Openpyxl Workbook.save Function Creates A Corrupt And Un-openable Excel (.xlsx) File

November 30, 2023 Post a Comment

I have tried using August William's solution to this issue, but that also didn't work. I am not switching workbook types, i.e. .xlsm to .xlsx, which appears to be a separate issue

Solution 1:

Like I said above , yesterday I had the same problem ..I found the solution in this link:

https://python-forum.io/Thread-Need-help-in-understanding-this-particular-Traceback-TypeError

In reference to this error : TypeError: got invalid input value of type , expected string or Element

In summary the solution was to install openpyxl to another version :

pip uninstall openpyxl
pip install openpyxl==3.0.1

Solution 2:

I can't find a good reference, but I recall having stumbled upon the same, and the solution was to use the older format (.xls, which is a completely different format) instead. Seems like a generic problem of openpyxl that wasn't resolved at the moment.

A working way to append to .xlsx (works for me):

from openpyxl import load_workbook
writer = pd.ExcelWriter(filename, engine='openpyxl')

try:
    # try to open an existing workbook
    writer.book = load_workbook(filename)

    # get the last row in the existing Excel sheet# if it was not specified explicitlyif startrow isNoneand sheet_name in writer.book.sheetnames:
        startrow = writer.book[sheet_name].max_row

    # truncate sheetif truncate_sheet and sheet_name in writer.book.sheetnames:
        # index of [sheet_name] sheet
        idx = writer.book.sheetnames.index(sheet_name)
        # remove [sheet_name]
        writer.book.remove(writer.book.worksheets[idx])
        # create an empty sheet [sheet_name] using old index
        writer.book.create_sheet(sheet_name, idx)

    # copy existing sheets
    writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
except FileNotFoundError:
    # file does not exist yet, we will create itpassif startrow isNone:
    startrow = 0# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)

# save the workbook
writer.save()

Solution 3:

same problem for me not able to reopen a file created by openpyxl version > 3: in 3.0.3: File "D:\MyProg.py", line 251, in chargerSynthese self.wbs = load_workbook(filename=self.nomfichierXLSX) File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 314, in load_workbook reader.read() File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 279, in read self.read_worksheets() File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 227, in read_worksheets ws_parser.bind_all() File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\worksheet_reader.py", line 426, in bind_all self.bind_cells() File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\worksheet_reader.py", line 337, in bind_cells for idx, row in self.parser.parse(): File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\worksheet_reader.py", line 153, in parse row = self.parse_row(element) File "D:\MyPython\python3.8.2-x64\lib\site-packages\openpyxl\worksheet_reader.py", line 264, in parse_row self.row_counter = int(attrs['r']) ValueError: invalid literal for int() with base 10: '2.0'

attrs={'r':'2.0'} don't know where it come from (but origin in a save worksheet by openpyxl 3.0.3) then when you do this int(attrs['r']) in _reader.py: crash!

solution back to 2.6.4 version!

Solution 4:

reply to myself!

openpyxl 3.0.3 works well but is less permissive than 2.6 versions, here is my test code you must put int in row= , not float

from openpyxl import __version__
from openpyxl import load_workbook
from openpyxl import Workbook


wbs = Workbook()
wbs.active.title = 'titi'
mycell=wbs['titi'].cell(row = 1.0, column = 1)
mycell.value=22
wbs.save('toto.xlsx')
print('openpyxl  __version__:',__version__)

wbi = load_workbook(filename='toto.xlsx')
for i inrange(0,30):
    wbi['titi'].append([i,'tata'])
wbi.save('toto.xlsx')


# result1:# openpyxl  __version__: 2.6.3# result2:# openpyxl  __version__: 3.0.3# Traceback (most recent call last):#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\py\essais\crashxlsx.py", line 13, in <module>#     wbi = load_workbook(filename='toto.xlsx')#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 314, in load_workbook#     reader.read()#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 279, in read#     self.read_worksheets()#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\reader\excel.py", line 227, in read_worksheets#     ws_parser.bind_all()#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\worksheet\_reader.py", line 426, in bind_all#     self.bind_cells()#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\worksheet\_reader.py", line 337, in bind_cells#     for idx, row in self.parser.parse():#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\worksheet\_reader.py", line 153, in parse#     row = self.parse_row(element)#   File "D:\Users\T0015039\Documents\Mes Outils Personnels\python3.8.2_pyscripter3.6.3-x64\python3.8.2-x64\lib\site-packages\openpyxl\worksheet\_reader.py", line 264, in parse_row#     self.row_counter = int(attrs['r'])# ValueError: invalid literal for int() with base 10: '1.0'

Python Channel