0

my goal is to export a pandas dataframe to an excel file keeping the format of the dataframe. Here is the code snipet to create and format the dataframe:

import pandas as pd
import os
import numpy as np

df = pd.DataFrame({'A': np.linspace(100000, 1000, 5), 'B': 'line'})
df = pd.concat([df, pd.DataFrame(np.random.randn(5, 4), columns=list('CDEF'))],axis=1)

path = os.path.join(os.path.expanduser("~"), "Desktop", "styled.xlsx")
        
writer = pd.ExcelWriter(path, engine='xlsxwriter')
df.to_excel(writer, sheet_name='sheet',header=True, index=False, startrow=0, startcol=0)

my_format = writer.book.add_format({'align': 'center',
                                               'num_format': '#,##0.00', 
                                               'bg_color': '#FFFFFF', 
                                               'fg_color': '#000000'})
for col in (0, 6):
    writer.sheets['sheet'].set_column(col, col, None, my_format)

writer.save()

This is the dataframe in Jupyter notebook (this is exactly what I wanted to see in the excel sheet): Dataframe with float formatted

But this is the Excel file that I got when I run the code above: Excel sheet output with unformatted floats

Can anybody help getting the desired format of the dataframe into the Excel file? I already tried doing this with openpyxl but it seems that it does not format the area where the dataframe is sitting, just the rest of the Excel sheet.

André Neiva Liboreiro
  • 1
  • 2

1 Answers1

1

I think you can use the ExcelWriter for this:

excel_writer = pd.ExcelWriter('/home/your_excel.xlsx', engine = 'xlsxwriter')
df.to_excel(excel_writer, index=False, sheet_name='mysheet')
book  = excel_writer.book
sheet = excel_writer.sheets['mysheet']
excel_format = book.add_format({'num_format': '0.000'})
worksheet.set_column('C:C', None, excel_format)  # Adds formatting to column C
excel_writer.save()

Also take a look here in the docs

basilisk
  • 1,156
  • 1
  • 14
  • 34