2

I have DataFrame df.

I have Excel file template_with_styles.xlsx in which there is a color table ready.

I want to write df to a template.

from openpyxl.utils.dataframe import dataframe_to_rows

wb = load_workbook('template_with_styles.xlsx')
ws = wb.active
for r in dataframe_to_rows(df, index=None, header=True):
    ws.append(r)
wb.save('my.xlsx')

As a result, I get: first comes my table with styles, and below is the data without styles.

How can I write DataFrame to a Ecxel using a prepared template?

Olga
  • 1,395
  • 2
  • 25
  • 34
  • I would recommend the following: 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:55

1 Answers1

0

You can use pandas to do this. My attempt to convert to your example, from the following article with better explanations

https://pbpython.com/advanced-excel-workbooks.html

writer = pd.ExcelWriter('template_with_styles.xlsx', engine='xlsxwriter')
df.to_excel(writer, 'Sheet1', index=False)
wb = writer.book
ws = writer.sheets['Sheet1']
ws.add_table('A1:B10', {'style': 'Table Style Medium 20'})
writer.save()
chucklukowski
  • 1,996
  • 2
  • 13
  • 13
  • 1
    In your example, styles are written in manual mode 'A1:B10', {'style': 'Table Style Medium 20'}. Is there a way to append dataframe to an already formatted Excel file? – Olga Aug 01 '19 at 19:02
  • No, I don't. Forgot to mention my answer was an alternative to your actual question. – chucklukowski Aug 01 '19 at 19:21