如何使用Python以编程方式向现有Excel表添加行 [英] How can I programmatically add rows to existing Excel table with Python

问题描述

尽管有各种Python Excel操作库和资源，但我找不到特定的解决方案.

Despite the various Python Excel-manipulation libraries and resources, I am unable to find a specific solution.

现在，我有一个存在表的模板Excel文件.我想编写一个Python程序来填充此表.现有的任何Excel库都可以做到这一点吗?模板Excel文件的工作表中有一个空表(第一个屏幕截图).我想以编程方式用其他地方的一些数据填充表格.例如

Right now, I have a template Excel file where a table exists. I would like to write a Python program in order to populate this table. Is this possible with any of the existing Excel libraries? The template Excel file has a sheet with an empty table (1st screenshot). I want to programmatically populate the table with some data from elsewhere. e.g.

data = [("TS0001", "1.0 Administration", "Root", "vdouk", "15/09/19", 8.0, "example 1"),
        ("TS0002", "1.0 Administration", "Root", "vdouk", "16/09/19", 3.0, "example 2"),
        ("TS0003", "4.0 Simulations", "Root", "vdouk", "16/09/19", 5.0, "example 3")]

因此，我最终希望看起来像第二张屏幕截图.总条目和小计是从适当的excel单元格功能(已定义)中自动计算得出的.最后，请注意该表是从工作簿的其他部分引用的，这意味着它是一个命名的数据源(在Excel名称管理器中可见)，因此定义新表将在其他地方引起问题.有人可以指导我找到最合适的解决方案吗?

Thus, I want finally to look like the 2nd screenshot. The total entries and subtotals are computed automatically from appropriate excel cell functions (already defined). Finally, note that this table is referenced from other parts of the workbook, meaning that it is a named data source (seen in the Excel name manager), so defining a new table will cause problems elsewhere. Can someone direct me to the most appropriate solution?

1. 打开现有的excel文件.
2. 请勿更改任何格式.
3. 请勿删除任何现有数据.
4. 使用新行更新excel文件:

1. Open an existing excel file.
2. Do not change any formatting.
3. Do not delete any existing data.
4. Update the excel file with new rows:

1. 将所有新行插入标题行的正下方.
2. 确保这会将新行作为现有表的一部分.

保存并关闭excel文件.

更新

现在，我正在使用openpyxl.我要做的解决方法是编辑模板.xlsx并将空行添加到表中.然后，我使用openpyxl写入与数据元组一样多的行.该表总数似乎可以正常工作，但是其他一些excel宏似乎正在中断.

Update

Right now, I am using openpyxl. The workaround I do is to edit the template .xlsx and add empty rows to the table. Then I write as many rows as my data tuples are by using openpyxl. The table totals seem to work, however some other excel macros seem to be breaking.

推荐答案

也许您可以使用 xltpl 为此-一个从xls/x模板生成xls/x文件的python模块.

Maybe you can use xltpl for this - A python module to generate xls/x files from a xls/x template.

使用您的excel文件作为模板来生成新文件.

Use your excel file as the template to generate a new file.

这篇关于如何使用Python以编程方式向现有Excel表添加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！