Pandas df write to Excel template with prepared styles

Question

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?

I would recommend the following: stackoverflow.com/questions/71527992/… — Fraser Hay
– Fraser Hay, Commented Jul 26, 2022 at 14:55

chucklukowski · Accepted Answer · 2019-08-01 18:19:35Z

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

answered Aug 1, 2019 at 18:19

chucklukowski

2,0342 gold badges14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Olga Over a year ago

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?

chucklukowski Over a year ago

No, I don't. Forgot to mention my answer was an alternative to your actual question.

Collectives™ on Stack Overflow

Pandas df write to Excel template with prepared styles

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related