Create a Python Tool That Generates Professional Excel Dashboards

import openpyxl
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def create_sales_dashboard(workbook_path: str) -> None:
    """Generate a professional sales dashboard in an Excel workbook."""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Dashboard"

    # Sample data
    headers = ["Month", "Sales", "Expenses", "Profit"]
    data = [
        ["Jan", 12000, 8000, 4000],
        ["Feb", 15000, 9000, 6000],
        ["Mar", 18000, 9500, 8500],
        ["Apr", 22000, 10000, 12000],
        ["May", 25000, 11000, 14000],
        ["Jun", 30000, 12000, 18000],
    ]

    # Write headers with style
    header_font = Font(bold=True, color="FFFFFF", size=12)
    header_fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')
        cell.border = thin_border

    # Write data
    for row_idx, row_data in enumerate(data, 2):
        for col_idx, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.border = thin_border
            if col_idx > 1:
                cell.number_format = '#,##0'

    # Adjust column widths
    for col in range(1, len(headers) + 1):
        ws.column_dimensions[get_column_letter(col)].width = 15

    # Create bar chart
    chart = BarChart()
    chart.type = "col"
    chart.title = "Monthly Performance Summary"
    chart.y_axis.title = "Amount ($)"
    chart.x_axis.title = "Month"
    chart.style = 10

    data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=len(data) + 1)
    cats_ref = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats_ref)
    chart.shape = 4

    ws.add_chart(chart, "F1")

    wb.save(workbook_path)

if __name__ == "__main__":
    create_sales_dashboard("sales_dashboard.xlsx")
    print("Dashboard created: sales_dashboard.xlsx")