I have a python dataframe with various data types for each column. Usually I export it to a regular csv file using pandas.DataFrame.to_csv then open it with Excel, copy its content and paste it in a well-formatted template (xltx) file.
I was wondering If I can write the dataframe directly to the template so the data can be automatically formatted without the extra steps.
What's the best way to do so?
I figured out I can use openpyxl to do exactly what I wanted to do by using dataframe_to_rows from openpyxl.utils.dataframe which can write the dataframe to the template using something like this:
# Load the file
wb = openpyxl.load_workbook('Template.xltx')
ws = wb.active
# Convert the dataframe into rows
rows = dataframe_to_rows(processed_data, index=False, header=False)
# Write the rows to the worksheet
for r_idx, row in enumerate(rows, 1):
for c_idx, value in enumerate(row, 1):
ws.cell(row=r_idx, column=c_idx, value=value)
# Save the worksheet as a (*.xlsx) file
wb.template = False
wb.save('File.xlsx')