Python使用openpyxl-3.0.3复制Excel的一行内容并且插入到下一行，包含公式，下拉框，示例代码如下：
# coding=utf-8
# openpyxl-3.0.3
from openpyxl.reader.excel import load_workbook
from openpyxl.cell.cell import TYPE_FORMULA
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.cell_range import CellRange
import re
import copy
def insert_rows(self, row_idx, cnt, above=False, copy_style=True, copy_merged_columns=True, fill_formulae=True):
"""Inserts new (empty) rows into worksheet at specified row index.
:param row_idx: Row index specifying where to insert new rows.
:param cnt: Number of rows to insert.
:param above: Set True to insert rows above specified row index.
:param copy_style: Set True if new rows should copy style of immediately above row.
:param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows.
Usage:
* insert_rows(2, 10, above=True, copy_style=False)
"""
CELL_RE = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)")
row_idx = row_idx - 1 if above else row_idx
def replace(m):
row = m.group('row')
prefix = "$" if row.find("$") != -1 else ""
row = int(row.replace("$", ""))
row += cnt if row > row_idx else 0
return m.group('col') + prefix + str(row)
# First, we shift all cells down cnt rows...
old_cells = set()
old_fas = set()
new_cells = dict()
new_fas = dict()
for c in self._cells.values():
old_coor = c.coordinate
# Shift all references to anything below row_idx
if c.data_type == TYPE_FORMULA:
c.value = CELL_RE.sub(
replace,
c.value
)
# Here, we need to properly update the formula references to reflect new row indices
if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]:
self.formula_attributes[old_coor]['ref'] = CELL_RE.sub(
replace,
self.formula_attributes[old_coor]['ref']
)
# Do the magic to set up our actual shift
if c.row > row_idx:
old_coor = c.coordinate
old_cells.add((c.row, c.column))
c.row += cnt
new_cells[(c.row, c.column)] = c
if old_coor in self.formula_attributes:
old_fas.add(old_coor)
fa = copy.copy(self.formula_attributes[old_coor])
new_fas[c.coordinate] = fa
for coor in old_cells:
del self._cells[coor]
self._cells.update(new_cells)
for fa in old_fas:
del self.formula_attributes[fa]
self.formula_attributes.update(new_fas)
# Next, we need to shift all the Row Dimensions below our new rows down by cnt...
# CHANGED: for row in range(len(self.row_dimensions) - 1 + cnt, row_idx + cnt, -1):
for row in range(list(self.row_dimensions)[-1] + cnt, row_idx + cnt, -1):
new_rd = copy.copy(self.row_dimensions[row - cnt])
new_rd.index = row
self.row_dimensions[row] = new_rd
del self.row_dimensions[row - cnt]
# Now, create our new rows, with all the pretty cells
# CHANGED: row_idx += 1
new_row_idx = row_idx + 1
for row in range(new_row_idx, new_row_idx + cnt):
# Create a Row Dimension for our new row
new_rd = copy.copy(self.row_dimensions[row-1])
new_rd.index = row
self.row_dimensions[row] = new_rd
# CHANGED: for col in range(1,self.max_column):
for col in range(self.max_column):
col = col + 1
cell = self.cell(row=row, column=col)
source = self.cell(row=row_idx, column=col)
if copy_style:
cell.number_format = copy.copy(source.number_format)
cell.font = copy.copy(source.font)
cell.alignment = copy.copy(source.alignment)
cell.border = copy.copy(source.border)
cell.fill = copy.copy(source.fill)
if fill_formulae and TYPE_FORMULA == source.data_type :
s_coor = source.coordinate
if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:
fa = copy.copy(self.formula_attributes[s_coor])
self.formula_attributes[cell.coordinate] = fa
#print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
cell.value = re.sub(
"(\$?[A-Z]{1,3}\$?)%d" % (row_idx),
lambda m: m.group(1) + str(row),
source.value
)
cell.data_type = TYPE_FORMULA
# Check for Merged Cell Ranges that need to be expanded to contain new cells
for cr in self.merged_cells.ranges:
min_col, min_row, max_col, max_row = cr.bounds
if min_row <= row_idx and max_row > row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
elif min_row > row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
cr.shrink(top = cnt)
# Merge columns of the new rows in the same way row above does
if copy_merged_columns:
bounds = []
for cr in self.merged_cells.ranges:
if cr.max_row == cr.min_row == row_idx:
bounds.append((cr.min_col, cr.max_col))
for (min_col, max_col) in bounds:
for row in range(new_row_idx, new_row_idx + cnt):
newCellRange = get_column_letter(min_col) + str(row) + ":" + get_column_letter(max_col) + str(row)
self.merge_cells(newCellRange)
# update dataValidation
validations = self.data_validations.dataValidation
for val in validations:
for cr in val.cells:
min_col, min_row, max_col, max_row = cr.bounds
if min_row <= row_idx and max_row >= row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
elif min_row > row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
cr.shrink(top = cnt)
# update conditional_formatting tow steps
# first get all conditional_formatting need to update
cond_fmts = self.conditional_formatting
upd_cfs = []
for cf in cond_fmts:
for cr in cf.cells:
min_col, min_row, max_col, max_row = cr.bounds
if min_row <= row_idx and max_row >= row_idx:
upd_cfs.append(cf)
break
elif min_row > row_idx:
upd_cfs.append(cf)
break
# second update conditional_formatting
for cf in upd_cfs:
rules = cond_fmts[cf]
del cond_fmts[cf.cells]
for cr in cf.cells:
min_col, min_row, max_col, max_row = cr.bounds
if min_row <= row_idx and max_row >= row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
elif min_row > row_idx:
if max_row + cnt >= CellRange.max_row.max:
cr.expand(down = CellRange.max_row.max - max_row)
else:
cr.expand(down = cnt)
cr.shrink(top = cnt)
for r in rules:
cond_fmts[cf] = r
Worksheet.insert_rows = insert_rows
if __name__ == "__main__":
# 注意，keep_vba=True打开的Excel文档，保存之后，MS Office 无法打开
wb = load_workbook(filename='example.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True)
ws = wb.active
ws.insert_rows(6, 4, above=True, copy_style=True)
wb.save('new_document.xlsx')
wb.close()
参考链接
- l Sample code for inserting rows in an openpyxl worksheet. This code is not supported
- Insert new row(blank row) at a specific row number.
- 插上翅膀，让Excel飞起来——xlwings（一）
- python对excel的公式支持情况概括
- Python开发 之 Python3读写Excel文件（较全）
- openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files
- python读取excel,获得下拉选中所有选项
- Python - openpyxl 读写操作Excel
- Python操作Excel插入删除行
- 使用Python中的openpyxl将行插入Excel电子表格
- xlwings Python for Excel
- Python-Excel 模块哪家强？
- snippets openpyx Sample code for inserting rows in an openpyxl worksheet. This code is not supported
- "We found a problem with some content in copy.xlsx" Openpyxl 3.0.3, open and save with no changes to file
- Cell borders lost on save
- 放弃openpyxl，拥抱xlwings（python操作xls）
- Openpyxl and Hidden/Unhidden Excel Worksheets
- 利用OpenXml SDK改变Excel样式(如单元格填充色)