Search code examples
pythonexcelpandaspandas.excelwriter

Manipulate dataframe output in Excel sheets

Morning,

I have a dataframe which I am splitting into multiple sheets based on the Trading Book in a specific column. Once exported to individual sheets I'd like to autofit the data, hide gridlines and centre align the data and freeze panes on a particular cell ($B$2). The export has been done but the formatting in Excel is not working, I'm stuck on the autofit:

dfTemp = {  'Trading_Book': ['A', 'A', 'A',
                 'B', 'B', 'B',
                 'C', 'C', 'C',
                 ],
        'Client': ['AA', 'AA', 'AA',
                 'BB', 'BB', 'BB',
                 'CC', 'CC', 'CC',
                 ],
      }
dfTemp = pd.DataFrame.from_dict(dfTemp)
print(dfTemp)

# Export each Trading Books data to a separate sheet in Excel
excel_writer = pd.ExcelWriter('C:\EOM_ML_201901\outputs\Excel_Data.xlsx')
dfTemp.to_excel(excel_writer, sheet_name='Consolidated')
# Get Book List
Books = dfTemp['Trading_Book'].unique().tolist()
# For each book send to Excel sheet
for myname in Books:
    mydf = dfTemp.loc[dfTemp.Trading_Book == myname]
    mydf.to_excel(excel_writer, sheet_name=myname)
    # Autofil columns
    for column in mydf:
        column_length = max(mydf[column].astype(str).map(len).max(), len(column))
        col_idx = mydf.columns.get_loc(column)
        excel_writer.sheets[myname].set_column(col_idx, col_idx, column_length)
excel_writer.save()

Any assistance would be appreciated.

Solution

  • Freeze panes can be done directly from pandas when you write to the file:

    mydf.to_excel(excel_writer, sheet_name=myname, freeze_panes=(x,y))

    replace (x,y) with the row and column you want to freeze at, per documentation here.

    Unfortunately, that same documentation does not show a way to hide gridlines directly from pandas. You can, however, call xlsxwriter as the engine to use to write with: mydf.to_excel(excel_writer, sheet_name=myname, engine='xlsxwriter')

    Then you should be able to follow the accepted answer here to hide the gridlines.

    For aligning center, you can use these two commands from the xlsxwriter package, depending on if you want horizontal or vertical centering:

    cell_format.set_align('center')
cell_format.set_align('vcenter')

    Documentation here from xlsxwriter, use ctrl-f 'align' to skip to the parts you want.

    Good luck!