Openpyxl created excel file with table causes file that requires recovery error

user_6278684 0
Viewed 3240

I have been testing adding a table to a worksheet using openpyxl, but I get the error below when I try to open it. The file opens, but the formatting isn't correct. After hitting recover, excel reports that there was an issue with the table xml. Is there a workaround/fix for this?

enter image description here

enter image description here The code I'm using:

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

xl_file_name = "new_test.xlsx"
wb = Workbook()
ws = wb.worksheets[0]
ws.title = "Table_Sheet"
headers = ["header1","header2","header3"]
for col in range(1,len(headers)+1):
    for row in range(1,5):
        if row == 1:
            ws.cell(row,col).value = headers[col-1]
        else:
            ws.cell(row,col).value = str(row)

tbl = Table(displayName="Tbl1",ref="A1:C4")
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tbl.tableStyleInfo = style
ws.add_table(tbl)
wb.save("new_test.xlsx")
excel-tables openpyxl python-3.x excel
3 Answers

Your name for the table is causing the problem. Run the same code with displayName="Tbl" or displayName="Tbl_1" instead, and you'll see it works fine. I'm not 100% sure, but I think the cause of the issue is that the name you give conflicts with the formatting for a possible cell reference of TBL1.

user_112756810

For me the following worked:

  • Change the Workbook as you wish (only Data no formatting)
  • Save the Workbook (If you would try to open it here it will display the error message)
  • Close the Workbook
  • Open the Workbook again (I think here Excel fixes the issue automatically)
  • Insert necessary formatting commands
  • Save the workbook
  • Close the Workbook

Or, as code:

import openpyxl

workbook = openpyxl.load_workbook(Source_Path)

##your code appending and deleting values - which  I think sometimes causes the errors

workbook.save(Destination_Path)
workbook.close

#Now open it again
workbook = openpyxl.load_workbook(Destination_Path)

#Your Code to format

workbook.save(Destination_Path)
workbook.close

Now you should be able to open the Excel file without an error.

user_169300420

I've had the same error message.

I was creating tables with numbers at the start of the name, so I changed that code to add t_ at the beginning, so

table_name = "112MHZ_data"
became
table_name = "t_112MHZ_data"

And that solved it for me.

user_6066610
Related