6

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")
1QuickQuestion
  • 729
  • 2
  • 9
  • 25

4 Answers4

4

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.

jmb
  • 116
  • 3
1

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.

GarethD
  • 112
  • 1
  • 8
0

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.

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Konstantin Todte
  • 1
  • 2
0

In addition to jmb answer, you must also be careful not to have any duplicate names in your dataframe columns (names are considered duplicate even if case is different). For example the following code will cause an error on opening the file (tested with openpyxl==3.1.0)

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.worksheet.table import Table, TableStyleInfo

workbook = Workbook()
worksheet = workbook.active

# dataframe with identical column names 
df = pd.DataFrame({"myname": [0], "MyName": [1]})


for row in dataframe_to_rows(df, header=True, index=False):
    worksheet.append(row)


table_range = f"A1:{get_column_letter(df.shape[1])}{worksheet.max_row}"
table_name = "Tbl"
table = Table(displayName=table_name, ref=table_range)

table.tableStyleInfo = TableStyleInfo(name="TableStyleLight1")

worksheet.add_table(table)

workbook.save("problems.xlsx")

To solve the issue, you can rename your columns before using openpyxl so that there are no duplicates, for example with the following code which will add an increasing number at the end of identical columns

counter = {}
cols = []

for col in df.columns:
    if col.lower() in counter:
        cols.append(f"{col}{counter[col.lower()]}")
        counter[col.lower()] += 1
    else:
        counter[col.lower()] = 1
        cols.append(col)
df.columns = cols
Gabriel
  • 857
  • 10
  • 14