Manipulate dataframe output in Excel sheets

Question

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.

Thanks @Blimitless. Will check when back in office. 😎

Peter Lucas
– Peter Lucas

2021-05-12 08:19:08 +00:00
Commented May 12, 2021 at 8:19 — Peter Lucas
– Peter Lucas, Commented May 12, 2021 at 8:19

BLimitless · Accepted Answer · 2021-05-14 19:05:30Z

2

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!

edited May 14, 2021 at 19:05

answered May 11, 2021 at 5:41

BLimitless

2,7855 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Manipulate dataframe output in Excel sheets

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related