0

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?

Khalid
  • 65
  • 1
  • 9
  • I would recommend this answer: https://stackoverflow.com/questions/71527992/pandas-dataframe-to-specific-sheet-in-a-excel-file-without-losing-formatting/73116312#73116312 – Fraser Hay Jul 26 '22 at 14:53

1 Answers1

2

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')
Khalid
  • 65
  • 1
  • 9