Python dataframe to formatted Excel template-CodePudding

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?

CodePudding user response：

Call to_excel function. Like this:

df.to_excel("file_name.xlxs")

CodePudding user response：

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