在Python中使用openpyxl将行插入到Excel电子表格中 [英] Insert row into Excel spreadsheet using openpyxl in Python

问题描述

我正在寻找使用openpyxl将一行插入电子表格的最佳方法。

有效地，我有一个电子表格（Excel 2007），其中有一个标题行，最后（最多）数千行数据。我正在将行插入实际数据的第一行，所以在标题之后。我的理解是，附加功能适合于向文件的结尾添加内容。

阅读openpyxl和xlrd的文档（和xlwt），我无法找到任何明确的方法来做到这一点，除了手动循环内容并插入到新的工作表（插入所需的行之后）。鉴于我迄今为止对Python的经验是有限的，我想了解这是否是最好的选择（最多的是pythonic！），如果可以的话有人提供了明确的例子。具体来说，我可以用openpyxl读写行，还是要访问单元格？另外我可以（过）写同一个文件（名称）吗？

解决方案

使用我正在使用的代码以达到预期的效果。请注意，我手动将行插入位置1，但是应该足够容易调整特定需求。您也可以轻松地调整这个来插入多行，并简单地填充从相关位置开始的其余数据。

另外请注意，由于下游依赖关系我们手动从Sheet1指定数据，并将数据复制到插入工作簿开头的新工作表，同时将原始工作表重命名为Sheet1.5。

编辑：我还添加了（以后）更改格式_code以修复这些默认复制操作在这里删除所有格式的问题： 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'）
 
＃做标题。 
 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 
 
＃要插入的行。我们手动填充每个单元格。 
 new_sheet.cell（row = 1，column = 0）.value ='DUMMY'
 new_sheet.cell（row = 1，column = 1）.value ='DUMMY'
 
＃现在做其余的。注意行偏移。在范围（1，max_row）中的row_num的
：范围（0，max_col）中col_num的
：
 new_sheet.cell（row =（row_num + 1），column = col_num）.value = old_sheet.cell（row = row_num，column = col_num）.value 
 
 wb.save（file）
  

I'm looking for the best approach for inserting a row into a spreadsheet using openpyxl.

Effectively, I have a spreadsheet (Excel 2007) which has a header row, followed by (at most) a few thousand rows of data. I'm looking to insert the row as the first row of actual data, so after the header. My understanding is that the append function is suitable for adding content to the end of the file.

Reading the documentation for both openpyxl and xlrd (and xlwt), I can't find any clear cut ways of doing this, beyond looping through the content manually and inserting into a new sheet (after inserting the required row).

Given my so far limited experience with Python, I'm trying to understand if this is indeed the best option to take (the most pythonic!), and if so could someone provide an explicit example. Specifically can I read and write rows with openpyxl or do I have to access cells? Additionally can I (over)write the same file(name)?

解决方案

Answering this with the code that I'm now using to achieve the desired result. Note that I am manually inserting the row at position 1, but that should be easy enough to adjust for specific needs. You could also easily tweak this to insert more than one row, and simply populate the rest of the data starting at the relevant position.

Also, note that due to downstream dependencies, we are manually specifying data from 'Sheet1', and the data is getting copied to a new sheet which is inserted at the beginning of the workbook, whilst renaming the original worksheet to 'Sheet1.5'.

EDIT: I've also added (later on) a change to the format_code to fix issues where the default copy operation here removes all formatting: new_cell.style.number_format.format_code = 'mm/dd/yyyy'. I couldn't find any documentation that this was settable, it was more of a case of trial and error!

Lastly, don't forget this example is saving over the original. You can change the save path where applicable to avoid this.

    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)

这篇关于在Python中使用openpyxl将行插入到Excel电子表格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！