I have an .xslx file with specific formatting and objects that I am using for reports that I plan on producing on a large scale using Python. I originally was openpyxl to load a copy of the template (openpyxl.load_workbook()), write a Pandas dataframe to the file (openpyxl.dataframe_to_rows()), then save the file for future distribution. I found out that openpyxl.load_workbook does not load the formatting or objects so they are removed from the new file. So then tried xlrd to open the file (xlrd.open_workbook()) which loaded the formatting and objects properly. However openpyxl will no longer write to the file creating empty copies of the template file. Is there another package I can use that will handle the reading/writing by itself or a package I can use instead of openpyxl? Xlsxwriter didn't work either. See code sample below.
from xlrd import open_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
import shutil
shutil.copy2('template.xlsx', 'new_report.xlsx')
book = open_workbook('new_report.xlsx')
writer = pd.ExcelWriter(book, engine='openpyxl')
ws = book.sheet_by_name('Sheet1')
for r in dataframe_to_rows(result, index=False, header=False):
ws.cell(colx=1, rowx=1)
ws.append(r)
book.save('new_report.xlsx')
I'm also getting the errors: "AttributeError: 'Book' object has no attribute 'save'" and "AttributeError: 'Sheet' object has no attribute 'append'" from the code if anyone has suggestions for those problems.
I ended up using formulas to recreate any formatting I had in the existing Excel file after pasting the new data. I'm still missing the objects (Ex. shapes) but my reports will live without them until I can find another work around.