Linux环境下如何保留Excel XML表格结构实现数据填充？

需求说明 #

• 两个Excel文件：
  • file1：数据文件，需提取全部数据
  • file2：带XML表格表头的模板（A3为表头，首行固定）
• 目标：将file1的数据复制到file2的A5位置，保留file2原有的XML表格结构

尝试1：Openpyxl实现（失败） #

代码 #

import openpyxl

wb = openpyxl.load_workbook('file1.xlsx')
sheet1 = wb.active
filas = sheet1.max_row

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


for fila in range(5,filas+5): 
    sheet2["A"+ str(fila)] = sheet1["A"+ str(fila-4)].value
    sheet2["B"+ str(fila)] = "AFP"
    sheet2["C"+ str(fila)] = "FRA"
    sheet2["D"+ str(fila)] = "NAC"
    sheet2["E"+ str(fila)] = "NAC"
    sheet2["F"+ str(fila)] = "CBR"
    sheet2["G"+ str(fila)] = "WHT2.5"
    sheet2["H"+ str(fila)] = "2.5"
    
    wb2.save('file3.xlsx')
    wb2.close()

问题 #

生成的file3.xlsx文件损坏，打开时弹出警告：

We found a problem with some content in file3.xlsx. Do you want us to try to recover as much as we can?

Excel修复后会删除/xl/tables/table1.xml，导致原XML表格结构丢失。

尝试2：VBA实现（可行但有环境限制） #

代码 #

Sub Replace()
'
' Macro to fill template

    Workbooks.Open (ThisWorkbook.Path & "file1.xlsx")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Open (ThisWorkbook.Path & "file2.xlsx")
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
    ThisWorkbook.Path & "file3.xlsx"
End Sub

局限 #

该方案能正常运行且保留文件结构，但需部署在Linux服务器及无服务器微服务环境中，无法安装Wine、Windows或Office，因此无法使用。

提问 #

是否可以通过IronPython或Python.NET实现该需求，同时保留file2原有的XML表格结构？

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