TLDR: I can't write a dataframe to an excel sheet that has preexisting merged cells after reading it as a workbook with openpyxl.
I need to write to a preexisting excel sheet to fill up a form. The form is pre-formatted, so I used openpyxl's open_workbook to open it as a workbook. The form filling is then done with a pandas dataframes. However, any merged cells in the excel sheet prevents me from writing to it.
I've googled about this but could not find any cases where people have tried to write to sheets that contain merged cells. I've found out that xlsxwriter can merge cells when writing to files but I'm not sure how to detect where the previously merged cells were, nor how to integrate that with openpyxl.
This was the thread that I used to retain formatting when writing to excel sheets: How to write to an existing excel file without overwriting data (using pandas)?
Just for demonstration purposes in this thread, an empty excel sheet with a single pair of merged cells is enough. The code below reads the file as a workbook, then reads it as a dataframe, and attempts to write it back in.
import pandas as pd
import openpyxl
df = pd.read_excel('example.xlsx')
wb = openpyxl.load_workbook('example.xlsx')
writer = pd.ExcelWriter('example2.xlsx', engine='openpyxl')
writer.book = wb
writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)
df.to_excel(writer)
It throws this error:
Traceback (most recent call last):
File "/Users/nathan/Desktop/test.py", line 18, in <module>
df.to_excel(writer)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/generic.py", line 2127, in to_excel
engine=engine)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/formats/excel.py", line 662, in write
freeze_panes=freeze_panes)
File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/excel.py", line 1605, in write_cells
xcell.value, fmt = self._value_with_fmt(cell.val)
AttributeError: 'MergedCell' object attribute 'value' is read-only
