Corrupted Workbook when written with openpyxl in pandas

user_1151746 0
Viewed 952

I wrote below code to write to a workbook using pandas. Idea is to add a new sheet to the workbook if one with same name already exists, otherwise create a new workbook.

import pandas as pd
from pathlib import Path
from openpyxl import load_workbook

xls = pd.ExcelFile("test.xlsx")
df = pd.read_excel(xls, "Sheet1")

op_name = "op.xlsx"
my_file = Path(op_name)
if my_file.exists():
    book = load_workbook(my_file)
    writer = pd.ExcelWriter(op_name, engine='openpyxl', mode='a')
    writer.book = book
    df.to_excel(writer, sheet_name="r2")
else:
    writer = pd.ExcelWriter(op_name, engine='openpyxl')
    df.to_excel(writer, sheet_name="r1")
writer.save()
writer.close()

However I get the following error while opening the output workbook:

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

I have tried multiple variations of this code but nothing has made it work. Any suggestions on how to resolve this issue will be highly appreciated.

openpyxl pandas dataframe excel python
2 Answers

Hi What is the pandas version that you have used if pandas version is >= 1.2 this error occurred as mentioned in this bug report in GitHub. Lower your pandas version to 1.1.5 or if you are particularly using this newer version please edit the library python file as below.

in pandas/io/excel/_openpyxl.py add these following lines

self.handles.handle.seek(0)
self.handles.handle.truncate()

below this line

self.book = load_workbook(self.handles.handle)

This fix is for pandas >= 1.2 libraries only.

user_105834340

op_name is only the file name. my_file variable had the file path. ExcelWriter() takes file path parameter and not file name.

Try writer = pd.ExcelWriter(my_file, engine='openpyxl', mode='a')

user_170813050
Related