我正在寻找使用openpyxl在电子表格中插入行的最佳方法。
实际上,我有一个电子表格(Excel2007),它有一个标题行,后面跟着(最多)几千行数据。我希望插入行作为实际数据的第一行,所以在标题之后。我的理解是append函数适用于将内容添加到文件的末尾。
阅读openpyxl和xlrd (以及xlwt)的文档时,除了手动遍历内容并插入到新工作表(在插入所需行之后)之外,我找不到任何明确的方法。
鉴于我到目前为止使用Python的经验有限,我想知道这是否真的是最好的选择(最Python!),如果是的话,有人能提供一个明确的例子吗?具体来说,我可以使用openpyxl读写行吗?或者我必须访问单元格吗?另外,我可以(覆盖)写相同的文件(名称)吗?
发布于 2013-06-26 22:31:42
用我现在用来达到预期结果的代码来回答这个问题。请注意,我是在位置1处手动插入行,但这应该足够容易,以便根据特定需要进行调整。您还可以轻松地调整它以插入多行,并简单地填充从相关位置开始的其余数据。
此外,请注意,由于下游依赖关系,我们将手动指定“Sheet1”中的数据,并将数据复制到在工作簿开头插入的新工作表中,同时将原始工作表重命名为“Sheet1.5”。
编辑:我还添加了(稍后)对format_code的更改,以修复此处的默认复制操作删除所有formatting:new_cell.style.number_format.format_code = 'mm/dd/yyyy'
的问题。我找不到任何文档表明这是可设置的,这更多的是一种反复试验的情况!
最后,别忘了这个例子比原来的例子节省了时间。您可以在适用的情况下更改保存路径以避免这种情况。
import openpyxl
wb = openpyxl.load_workbook(file)
old_sheet = wb.get_sheet_by_name('Sheet1')
old_sheet.title = 'Sheet1.5'
max_row = old_sheet.get_highest_row()
max_col = old_sheet.get_highest_column()
wb.create_sheet(0, 'Sheet1')
new_sheet = wb.get_sheet_by_name('Sheet1')
# Do the header.
for col_num in range(0, max_col):
new_sheet.cell(row=0, column=col_num).value = old_sheet.cell(row=0, column=col_num).value
# The row to be inserted. We're manually populating each cell.
new_sheet.cell(row=1, column=0).value = 'DUMMY'
new_sheet.cell(row=1, column=1).value = 'DUMMY'
# Now do the rest of it. Note the row offset.
for row_num in range(1, max_row):
for col_num in range (0, max_col):
new_sheet.cell(row = (row_num + 1), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value
wb.save(file)
发布于 2015-06-15 15:20:20
根据此处的反馈,==已更新为全功能版本: groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw.==
正如其他人所指出的,openpyxl
不提供此功能,但我已经按如下方式扩展了Worksheet
类以实现插入行。希望这篇文章能对其他人有用。
def insert_rows(self, row_idx, cnt, above=False, copy_style=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 == Cell.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.col_idx))
c.row += cnt
new_cells[(c.row,c.col_idx)] = c
if old_coor in self.formula_attributes:
old_fas.add(old_coor)
fa = self.formula_attributes[old_coor].copy()
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...
for row in range(len(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
row_idx += 1
for row in range(row_idx,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
for col in range(1,self.max_column):
col = get_column_letter(col)
cell = self.cell('%s%d'%(col,row))
cell.value = None
source = self.cell('%s%d'%(col,row-1))
if copy_style:
cell.number_format = source.number_format
cell.font = source.font.copy()
cell.alignment = source.alignment.copy()
cell.border = source.border.copy()
cell.fill = source.fill.copy()
if fill_formulae and source.data_type == Cell.TYPE_FORMULA:
s_coor = source.coordinate
if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:
fa = self.formula_attributes[s_coor].copy()
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 - 1),
lambda m: m.group(1) + str(row),
source.value
)
cell.data_type = Cell.TYPE_FORMULA
# Check for Merged Cell Ranges that need to be expanded to contain new cells
for cr_idx, cr in enumerate(self.merged_cell_ranges):
self.merged_cell_ranges[cr_idx] = CELL_RE.sub(
replace,
cr
)
Worksheet.insert_rows = insert_rows
发布于 2018-08-07 17:39:56
从openpyxl1.5开始,你现在可以使用.insert_rows(idx,row_qty)
from openpyxl import load_workbook
wb = load_workbook('excel_template.xlsx')
ws = wb.active
ws.insert_rows(14, 10)
它不会像在Excel中手动拾取idx行的格式那样拾取idx行的格式。之后,您将应用正确的格式,即单元格颜色。
https://stackoverflow.com/questions/17299364
复制相似问题