Skip to content

How to apply cell sytling?

Style: Add "style"-ing to individual dataframe exports

In writer.to_excel(df, ..., style={}) you can define specific styling kwargs for that specific table.
The style dictionary syntax is:

dictionary key:
reference the column, row, or cell		 dictionary value:
provide formatting specs
Options:
  • df column name
    • single coumn 'emplyees' or
    • range 'inception:last_contact'
  • df row number
    • single row '1' or
    • range '1:5'
  • excel column
    • single cell 'A1' or
    • range 'A1:C3'
  • excel column
    • single column 'A' or
    • range 'A:C'
 Options:
  • preset name from the selected "theme" (see presets of 'elegant_blue')
    • single preset 'bg_light_blue' or
    • list of presets ['bg_light_blue', 'num_fmt_pct']
  • custom stying kwargs as dictionary as per openpyxl's class names; examples:
    • font__name: 'Arial'
    • font__size: 10
    • font__bold: True
    • fill: 'FFEEB7'
    • alignment__horizontal: 'center'
    • alignment__vertical: 'center'
    • numberformat: '#,##0'
    • ...

Examples: (showcasing the many different styling options)

style = {'emplyees': ['bg_light_blue', 'num_fmt_pct'], 'F:G': 'num_fmt_pct'}

style = {'C3:D10': {'font__size': 20, 'numberformat': '#,##0', 'font__italic'=True}, 'employees:customers': {'numberformat': '#,##0'}}

MY_CUSTOM_WARNING_STYLE = {'font__bold': True, 'text__color': 'ff0000', 'font__size': 20}
MY_CUSTOM_DATE_STYLE = {'numberformat': 'yyyy-mm-dd'}

style = {
  '1': MY_CUSTOM_WARNING_STYLE, 
  '2:5': {'font__size': 20},
  'B3:G10': ['bg_light_blue', 'num_fmt_pct'], 
  'A1': {**MY_CUSTOM_WARNING_STYLE, **MY_CUSTOM_DATE_STYLE}
}

Theme: Set "theme" for entire excel file

In ExcelWriter(..., theme='elegant_blue') you can define the base theme that will be applied to your entire Excel file.
You can pass either:

  • a theme name like 'elegant_blue',
  • or your personal .yml-theme-file path (syntax example here)