How to modify xml tables inside excel with openpyxl?

252 Views Asked by At

I'm trying fo fill a table (inside an xlsx template for Dynamics NAV) with openpyxl, but when I open the file with Excel it promts an alert: “We found a problem with some content in <Excel_filename>. Do you want us to try recovering the file as much as we can? If you trust the source of this workbook, then click Yes”

Then Excel 'repairs' the file and I can still see the data but the table /xl/tables/table1.xml is gone, and Navision can't accept the file.

This is my code in 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()

When I create a table from zero with the code they show in the openpyxl official site: https://openpyxl.readthedocs.io/en/latest/worksheet_tables.html#creating-a-table it works fine only if the table starts from row one (ref="A1:E5").

...but this template has a table that starts from row 3!

So when I try to make the table I need (ref="A3:D6") I get this: 'UserWarning: File may not be readable: column headings must be strings.' and as expected, I get the same alert and the same result when I open it with Excel.

Is there a way to modify/fill a table without corrupting the xlsx file?

or, like a workaround Is there a way to create a table from A3 with no errors? Thanks in advance

0

There are 0 best solutions below