Excel file output : formatting

[simonaubertbd]

Hello,

As of now, the excel output looks like this

I would like to add some formatting on it (as an option):
-auto filter on header
-freezing header
-color, font, background color, bold/italic... for header and for data (separately)

Best regards,

Simon

[tgourdel]

Thanks Simon, good suggestions, I don't know how to do that yet, but will look into it! Thank you :)

[tgourdel]

Example below:

import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

# Sample DataFrame
df = pd.DataFrame({
    'Name': ['John', 'Jane', 'Doe'],
    'Age': [28, 24, 22],
    'City': ['New York', 'Los Angeles', 'Chicago']
})

# Write DataFrame to an Excel file with openpyxl
with pd.ExcelWriter('styled_output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # Auto filter on header
    worksheet.auto_filter.ref = worksheet.dimensions

    # Freeze header
    worksheet.freeze_panes = worksheet['A2']

    # Header formatting
    header_font = Font(bold=True, color='FFFFFF')
    header_fill = PatternFill("solid", fgColor="4F81BD")
    for cell in worksheet[1]:
        cell.font = header_font
        cell.fill = header_fill

    # Data formatting (example: alternating row color)
    data_fill_odd = PatternFill("solid", fgColor="E7E6E6")
    for idx, row in enumerate(worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, min_col=1, max_col=worksheet.max_column), start=2):
        fill = data_fill_odd if idx % 2 == 0 else None
        for cell in row:
            if fill:
                cell.fill = fill

# Open the styled Excel file
print("Excel file with styling created as 'styled_output.xlsx'")

Auto Filter: worksheet.auto_filter.ref = worksheet.dimensions applies a filter to the entire header row.
Freeze Header: worksheet.freeze_panes = worksheet['A2'] freezes the first row.
Header Formatting: Uses Font and PatternFill to set font color, bold, and background color for the header.
Data Formatting: Alternates row colors for better readability.