如何用openpyxl修改Excel中XML表格且避免文件损坏？

使用openpyxl填充Dynamics NAV的XLSX模板表格后，用Excel打开文件时弹出提示：

我们发现<Excel文件名>中的部分内容存在问题。是否尝试尽可能恢复文件？若信任此工作簿来源，请点击‘是’

点击确认后Excel会“修复”文件，数据虽可见，但/xl/tables/table1.xml文件消失，导致Navision无法接收该文件。

当前使用的Python代码如下：

import openpyxl

wb = openpyxl.load_workbook("data_source.xlsx", data_only=True)
sheet1 = wb.active

wb2 = openpyxl.load_workbook('template.xlsx')
sheet2 = wb2.active

filas = sheet1.max_row

for fila in range(3,filas): 
    sheet2["A"+ str(fila)] = sheet1["A"+ str(fila)].value
    sheet2["B"+ str(fila)] = sheet1["B"+ str(fila)].value
    sheet2["C"+ str(fila)] = "FRA"
    sheet2["D"+ str(fila)] = "NAC"

wb2.save('tax1.xlsx')
wb2.close()

尝试从零创建表格时，仅当表格从第1行开始（ref="A1:E5"）时能正常工作；但模板中的表格从第3行开始，创建ref="A3:D6"的表格时会收到警告：

UserWarning: File may not be readable: column headings must be strings.

打开Excel时仍会出现相同的修复提示，且表格文件丢失。

需求：修改/填充表格时不损坏XLSX文件，或找到从A3开始创建无错误表格的方案。

1. 保留模板原有表格结构，直接填充数据 #

不要手动重建表格，而是加载模板后直接操作表格的数据区域，确保不破坏原有表格的XML结构：

• 首先获取模板中已有的表格对象（假设模板中表格名为Table1）
• 确定表格的数据起始行（跳过表头行），然后批量填充数据

示例代码：

import openpyxl

# 加载数据源
wb_source = openpyxl.load_workbook("data_source.xlsx", data_only=True)
ws_source = wb_source.active

# 加载模板，禁止使用data_only=True，否则会丢失表格结构
wb_template = openpyxl.load_workbook('template.xlsx')
ws_template = wb_template.active

# 获取模板中的目标表格
table = None
for tbl in ws_template.tables.values():
    if tbl.name == "Table1":
        table = tbl
        break

if not table:
    raise ValueError("模板中未找到目标表格")

# 解析表格的引用范围，拆分得到表头和数据区域的位置
ref_parts = table.ref.split(':')
start_cell = openpyxl.utils.cell.coordinate_to_tuple(ref_parts[0])
end_cell = openpyxl.utils.cell.coordinate_to_tuple(ref_parts[1])
data_start_row = start_cell[0] + 1  # 跳过表头行，数据从下一行开始

# 遍历数据源，填充到表格数据区域
source_max_row = ws_source.max_row
for row_idx in range(3, source_max_row):
    target_row = data_start_row + (row_idx - 3)
    # 如果数据超出原有表格范围，扩展表格引用
    if target_row > end_cell[0]:
        new_ref = f"{ref_parts[0]}:{openpyxl.utils.cell.get_column_letter(end_cell[1])}{target_row}"
        table.ref = new_ref
    
    ws_template[f"A{target_row}"] = ws_source[f"A{row_idx}"].value
    ws_template[f"B{target_row}"] = ws_source[f"B{row_idx}"].value
    ws_template[f"C{target_row}"] = "FRA"
    ws_template[f"D{target_row}"] = "NAC"

wb_template.save('tax1.xlsx')
wb_template.close()
wb_source.close()

2. 正确创建从第3行开始的表格（无警告） #

如果必须从零创建表格，需确保表格的表头行（第3行）的单元格值都是字符串类型，且表格范围正确：

• 先填充第3行的表头内容（必须是字符串，不能是数字或空值）
• 再创建表格对象，指定ref为包含表头和数据的范围

示例代码：

import openpyxl
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = openpyxl.Workbook()
ws = wb.active

# 先设置表头（第3行），必须为非空字符串
ws["A3"] = "列1"
ws["B3"] = "列2"
ws["C3"] = "列3"
ws["D3"] = "列4"

# 填充数据行（第4-6行）
for row in range(4, 7):
    ws[f"A{row}"] = f"数据A{row}"
    ws[f"B{row}"] = f"数据B{row}"
    ws[f"C{row}"] = "FRA"
    ws[f"D{row}"] = "NAC"

# 创建表格，ref包含表头（A3）到最后一行数据（D6）
table = Table(displayName="Table1", ref="A3:D6")

# 设置表格样式（可选，匹配Excel默认样式）
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
table.tableStyleInfo = style

# 将表格添加到工作表
ws.add_table(table)

wb.save('tax1.xlsx')
wb.close()

3. 关键注意事项 #

• 加载模板时禁止使用data_only=True：该参数会丢弃公式和表格结构信息，仅保留单元格值，导致保存时无法正确还原表格XML
• 表格表头行必须为非空字符串：这是Excel表格的规范要求，空值或非字符串表头会触发文件损坏警告
• 扩展表格需更新ref属性：如果填充的数据超出了模板表格原有范围，必须手动更新表格的ref，确保覆盖所有数据行
• 避免修改模板原有格式：尽量保持模板的单元格格式、样式与表格定义一致，减少Excel修复的触发概率

内容的提问来源于stack exchange，提问作者mabusdogma