Data Automation & Cleaning Beginner

Export Pandas DataFrame to Styled Executive Excel Report (.xlsx) with Auto-Width in Python

Convert raw Pandas DataFrames into client-ready Excel workbooks in Python using openpyxl with navy header styling, currency formatting, and auto-fit column widths.

#pandas#openpyxl

Problem Statement

Default `df.to_excel()` outputs cramped columns (`###`) with plain headers that look unprofessional when sent to clients or management.

Formula / Core Logic

Writes DataFrame via openpyxl, applies dark header PatternFill + bold white Font, and sets column_dimensions.width = max_char_len + 4.

Installation

pip install pandas openpyxl

auto-format-excel-financial-report-openpyxl-python.py

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

def export_styled_excel(df, filename='executive_report.xlsx'):
    wb = Workbook()
    ws = wb.active
    ws.title = 'Financial_Summary'
    
    header_fill = PatternFill(start_color='0F172A', end_color='0F172A', fill_type='solid')
    header_font = Font(color='FFFFFF', bold=True)
    
    for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), start=1):
        ws.append(row)
        if r_idx == 1:
            for cell in ws[1]:
                cell.fill = header_fill
                cell.font = header_font
                
    for col in ws.columns:
        max_len = max(len(str(cell.value or '')) for cell in col)
        ws.column_dimensions[col[0].column_letter].width = max(max_len + 4, 12)
    wb.save(filename)