I am writing multiple dfs to an excel and I am trying to change certain index types to percentage, pound and others.
One of the df look like this ( dummy df ) :
2019 2020 2021 2022
A 40 40 51 58
B 5 40 54 97
C 0.3 0.5 0.5 0.8
D 2000 40 200 300
E 0.02 1 0.25 0.19
So I would like to change the index E to be type percentage when writing to excel, same thing would happen if I would select the whole row in excel and click % button to change the type.
I tried:
with pd.ExcelWriter(f'{name}.xlsx', engine="openpyxl", mode="a") as writer:
for cat in cats: # creating multiple dfs
df_temp = df_main[df_main['Category'] == cat]
df_temp.to_excel(writer, sheet_name = cat)
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets[cat]
# Add some cell formats.
format1 = workbook.add_format({'num_format': '#,##0.00'})
format2 = workbook.add_format({'num_format': '0%'})
worksheet.set_row('10', 18, format2)
The result I am looking for is :
2019 2020 2021 2022
A 40 40 51 58
B 5 40 54 97
C 0.3 0.5 0.5 0.8
D 2000 40 200 300
E 2% 100% 25% 19%
But I get an error:
AttributeError: ‘Workbook’ object has no attribute ‘add_format’
I don’t how I should continue with this problem and if there is an easier way to change the format types when writing dfs to excel. I am following this guide. And is there a way to give a name of an index which row type needs changing rather than noting the row number?
>Solution :
You have your ExcelWriter engine set to
openpyxl. The tutorial you’re following calls for xlsxwriter.
writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')