Python dataframe to formatted Excel template

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?

Solution

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')