keep all formatting when exporting to excel

Question

I have dataframe df_brands and I want to format this df and export to excel-file, but I have a problem

I have df_brands:

and I format this df:

and after this I want save this formatted df in excel-file, but this is bad:

I want to keep all formatting when I export to excel-file. Also I want fix column column width

Maybe I can export in another way? as picture maybe? but I want to save table format

Answer 1

When you export a "styled dataframe" (which is a different object than a simple dataframe), there are some "styles" that you will lose.

To get the styles you want in the final excel file, there are 2 ways:

• With "pandas styled dataframe": it's fast but some styles will not be exported to Excel
• With a library dedicated to Excel ("openpyxl" for example): it's generally slower...specially when you need to apply styles cell by cell

1. Center the values in the Excel cells (i use pandas)

Give a df to this function you will get back a styled dfs where values are centered.

import pandas as pd

def df_centerer(df):
    dfs = df.style.set_table_styles([dict(selector='th',
                                     props=[('text-align', 'center')])])
    dfs.set_properties(**{'text-align': 'center'})
    return dfs

2. Adjust columns width (I use openpyxl)

NB: Your screenshots are from Jupyter. Who tell you that it's not Jupyter which is kindly adjusting the column widths for you ?

In the function below "ws" is a worksheet object. "num" indicate the columns widths i want to use

from openpyxl.styles import Alignment

COLS_WIDTH = {1: ["ABCDEFGH", [20]*8],
              2: ["ABCDEF", [15] * 2 + [40] * 3 + [15]],
              3: ["ABC", [30] * 4]}

def ws_formatter(ws , num):
    # Adjust columns widths
    for idx, col in enumerate(COLS_WIDTH[num][0]):
        ws.column_dimensions[col].width = COLS_WIDTH[num][1][idx]
    
    # Set Autofilter on the whole table
    ws.auto_filter.ref = ws.dimensions
    
    # Freeze panes
    ws.freeze_panes = "B2"