I'm converting the float columns to currency data type with the following:
df = pd.DataFrame({'col0': [71513.0, 200000.0, None],
'col1': [True, False, False],
'col2': [100.0, 200.0, 0.0]})
df[['col0', 'col2']] = df[['col0', 'col2']].astype(float).astype("Int32").applymap(\
lambda x: "${:,.0f}".format(x) if isinstance(x, int) else x)
I am outputting the table with the following:
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, index= False)
workbook = writer.book
ws = writer.sheets['Sheet1']
writer.close()
writer.save()
However, when I output the datable with the following, the currency is stored as text:
How would I format the excel sheet itself (instead of the pandas column) based on the column name so that the value is a number, but the formatting is currency?
This is how it worked for me
Removed the column formatting within df
df = pd.DataFrame({'col0': [71513.0, 200000.0, None],
'col1': [True, False, False],
'col2': [100.0, 200.0, 0.0]})
Removed index parameter from to_excel, Defined format for the columns, and assign it to columns 1, and 3
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer) # index= False)
workbook = writer.book
ws = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '$#,##0.00'})
ws.set_column(1, 1, 18, format1)
ws.set_column(3, 3, 18, format1)
writer.save()
writer.close()
reference to documentation: https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html