Search code examples
pythonpandasdataframeexport-to-excel

Pandas to_excel formatting float values

I'm exporting a dataframe to an excel file using to_excel from pandas but I need that all the number values (float64) to be formatted (without specifing each columns because that will be variable) as a string (BRL currency) when exported.

import pandas as pd
df = pd.DataFrame({'Numero': {0: '044121', 1: '044121'}, 'Serie': {0: '0', 1: '0'}, 'Finalidade': {0: '1', 1: '1'}, 
                  'CNPJ_CPF_Emit': {0: '01039778000177', 1: '01039778000177'}, 
                  'Rz_Emit': {0: 'VINHEDOS INDUSTRIA E COMERCIO DE PAPEIS LTDA.', 1: 'VINHEDOS INDUSTRIA E COMERCIO DE PAPEIS LTDA.'}, 
                  'PIS_Valor': {0: 1620.98, 1: 1210.42}, 'COFINS_Valor': {0: 7466.32, 1: 5575.28}, 'Valor_IPI': {0: 3628.22, 1: 2709.28}, 
                  'Valor_ICMS': {0: 13396.5, 1: 10003.5}, 
                  'Valor_Total_Nota': {0: 201337.5, 1: 201337.5}, 'Valor_Frete': {0: '', 1: ''}})

df.to_excel(my_path, index=False, freeze_panes=(1,0))

resulting in:

enter image description here

and the desired excel output would be:

enter image description here

Is it possible? If so, how is the better way to do it?

Can be done using the float_format parameter when writing the excel?

Thanks!

Solution

  • It's possible to customize the format if you install xlsxwriter (with another code, it can work with openpyxl too).

    # pip install xlsxwriter
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:

    # write the dataframe before modify the sheet
    df.to_excel(writer, sheet_name='Sheet1')
    wb = writer.book  # get workbook
    ws = writer.sheets['Sheet1']  # and worksheet

    # add a new format then apply it on right columns
    currency_fmt = wb.add_format({'num_format': 'R$ #,###.##'})
    ws.set_column('F:J', None, currency_fmt)

    Output:

    enter image description here