Skip to content
TheChatArchiveThe Chat Archive
GPT 5.1· openaiHugging Face Frontier Reasoning Traces

Create a Python script to automate Excel report generation.

  1. User

    Create a Python script to automate Excel report generation.

  2. Assistant

    Creating a Python script

    The user wants a Python script to automate Excel report generation, but I need to clarify specifics because they haven't specified the report type, inputs, or environment. I'll assume a generic template: reading data from a CSV or database, generating pivot tables, charts, and formatted sheets, saving to XLSX. I can provide a sample script and explain dependencies. Users generally expect a flexible script with functions/arguments, maybe using pandas and openpyxl or xlsxwriter. I'll gather requirements and assume inputs like "monthly_sales.csv" and an output like "Sales_Report.xlsx".Designing a report generation script

    To create a practical reporting script, I'll use pandas for summarizing data rather than relying on complex pivot tables with formulas. For better Excel formatting and charting, xlsxwriter is my go-to. I can craft a script called generate_report.py that reads configurations, pulls raw data, computes KPIs, and writes to an Excel file with multiple sheets. I’ll also add formatting options and charts. To facilitate automation, I can implement a command-line interface with argparse, allowing users to specify input and output files while keeping explanations minimal through inline comments.Outlining installation and script details

    I need to mention the installation requirements for the user: they can use pip install pandas openpyxl xlsxwriter. I shouldn't just throw in code with placeholders since they asked for a specific script—it's important for them to adapt it based on their needs. I could generalize it as a script that reads any CSV and creates a simple summary. However, to be more helpful, I'll provide a concrete example while ensuring the code is correct and run-able.

    So, I’ll include an introduction, the dependencies, and a script based on an example CSV with columns like Date, Region, Salesperson, Product, Units, and Revenue—while letting them know they can rename the columns if necessary.Outlining script tasks and implementation

    For the script, I’ll be handling several tasks: First, I need to parse command-line arguments and read the CSV into a DataFrame. I’ll ensure the Date column is in datetime format and add a Month column. Then, I'll calculate key performance indicators like total revenue and units, along with averages. After grouping by Region and Product to summarize revenue and units, I'll output everything to an Excel file with multiple sheets.

    For the charts, I plan to use XlsxWriter for visualizing revenue by Region and Product on a 'Charts' sheet. I can use something like writer = pd.ExcelWriter(output_path, engine='xlsxwriter') and then write the DataFrame accordingly while applying the right formatting. I’ll be careful about handling column lists with pandas correctly and ensure I create summary sheets with the relevant KPIs.Developing the charting and reporting script

    I'll start adding a chart to visualize revenue by region. Using the workbook.add_chart({'type': 'column'}), I have to set the series with the region_summary data range. This includes calculating the maximum row for indexing correctly. Once the chart is configured, I need to ensure it’s placed in the Charts sheet and that the sheet names match.

    I also want to freeze panes and adjust column widths. I’ll structure the script step by step, writing brief comments and leveraging new features in pandas. The script will read CSV data, ensuring correct columns with parse_dates. I'll include a docstring, use the pd.ExcelWriter context manager for Excel writing, and apply currency formatting for the revenue columns. I'll name the script generate_excel_report.py to keep everything organized. Let's get coding! Below is a complete, ready‑to‑run Python script that:

    • Reads a CSV with transactional data
    • Builds summary tables (overall KPIs, by Region, by Product)
    • Writes a formatted Excel report with multiple sheets and charts

    You can adapt field names and logic as needed.


    1. Install dependencies

    pip install pandas xlsxwriter
    

    2. Example data structure

    The script assumes your input CSV has at least these columns:

    • Date – date of transaction (e.g., 2025-11-01)
    • Region – region name
    • Salesperson – salesperson name
    • Product – product name
    • Units – number of units sold
    • Revenue – total revenue for the row

    Example header row:

    Date,Region,Salesperson,Product,Units,Revenue
    2025-11-01,North,Alice,Widget A,10,2500
    

    3. The script: generate_excel_report.py

    #!/usr/bin/env python3
    """
    Automate Excel report generation from a CSV dataset.
    
    Usage:
        python generate_excel_report.py \
            --input sales_data.csv \
            --output Sales_Report.xlsx \
            --title "Monthly Sales Report"
    """
    
    import argparse
    from pathlib import Path
    import pandas as pd
    
    
    def parse_args():
        parser = argparse.ArgumentParser(description="Generate Excel report from CSV data.")
        parser.add_argument(
            "--input",
            "-i",
            required=True,
            help="Path to input CSV file (raw data).",
        )
        parser.add_argument(
            "--output",
            "-o",
            required=True,
            help="Path to output Excel file (.xlsx).",
        )
        parser.add_argument(
            "--title",
            "-t",
            default="Automated Sales Report",
            help="Report title to show in the Summary sheet.",
        )
        return parser.parse_args()
    
    
    def load_data(csv_path: str) -> pd.DataFrame:
        """Load source data from CSV and normalize columns."""
        csv_path = Path(csv_path)
        if not csv_path.exists():
            raise FileNotFoundError(f"Input file not found: {csv_path}")
    
        # Parse Date column as datetime if present
        df = pd.read_csv(csv_path)
    
        expected_cols = {"Date", "Region", "Salesperson", "Product", "Units", "Revenue"}
        missing = expected_cols - set(df.columns)
        if missing:
            raise ValueError(
                f"CSV is missing required columns: {', '.join(sorted(missing))}\n"
                f"Found columns: {', '.join(df.columns)}"
            )
    
        df["Date"] = pd.to_datetime(df["Date"], errors="coerce")
        if df["Date"].isna().any():
            raise ValueError("Some values in 'Date' column could not be parsed as dates.")
    
        # Ensure numeric
        df["Units"] = pd.to_numeric(df["Units"], errors="coerce")
        df["Revenue"] = pd.to_numeric(df["Revenue"], errors="coerce")
    
        # Optional: add Year-Month for grouping
        df["Month"] = df["Date"].dt.to_period("M").astype(str)
    
        return df
    
    
    def build_summaries(df: pd.DataFrame):
        """Build various summary tables."""
        # Overall KPIs
        total_revenue = df["Revenue"].sum()
        total_units = df["Units"].sum()
        avg_revenue_per_order = df["Revenue"].mean()
    
        kpi_df = pd.DataFrame(
            {
                "Metric": [
                    "Total Revenue",
                    "Total Units Sold",
                    "Average Revenue per Transaction",
                    "Number of Transactions",
                ],
                "Value": [
                    total_revenue,
                    total_units,
                    avg_revenue_per_order,
                    len(df),
                ],
            }
        )
    
        # By Region
        by_region = (
            df.groupby("Region", as_index=False)
            .agg(
                Units=("Units", "sum"),
                Revenue=("Revenue", "sum"),
                Transactions=("Revenue", "count"),
            )
            .sort_values("Revenue", ascending=False)
        )
    
        # By Product
        by_product = (
            df.groupby("Product", as_index=False)
            .agg(
                Units=("Units", "sum"),
                Revenue=("Revenue", "sum"),
                Transactions=("Revenue", "count"),
            )
            .sort_values("Revenue", ascending=False)
        )
    
        # By Month (optional)
        by_month = (
            df.groupby("Month", as_index=False)
            .agg(
                Units=("Units", "sum"),
                Revenue=("Revenue", "sum"),
                Transactions=("Revenue", "count"),
            )
            .sort_values("Month")
        )
    
        return kpi_df, by_region, by_product, by_month
    
    
    def write_excel_report(
        df: pd.DataFrame,
        kpi_df: pd.DataFrame,
        by_region: pd.DataFrame,
        by_product: pd.DataFrame,
        by_month: pd.DataFrame,
        output_path: str,
        report_title: str,
    ):
        """Write Excel report with formatting and charts using XlsxWriter."""
        output_path = Path(output_path)
    
        with pd.ExcelWriter(output_path, engine="xlsxwriter") as writer:
            # Write raw data
            df.to_excel(writer, sheet_name="Raw Data", index=False)
    
            # Write summaries
            kpi_df.to_excel(writer, sheet_name="Summary", index=False, startrow=3)
            by_region.to_excel(writer, sheet_name="By Region", index=False)
            by_product.to_excel(writer, sheet_name="By Product", index=False)
            by_month.to_excel(writer, sheet_name="By Month", index=False)
    
            workbook = writer.book
            # Access sheets
            raw_sheet = writer.sheets["Raw Data"]
            summary_sheet = writer.sheets["Summary"]
            region_sheet = writer.sheets["By Region"]
            product_sheet = writer.sheets["By Product"]
            month_sheet = writer.sheets["By Month"]
    
            # Formats
            title_fmt = workbook.add_format(
                {"bold": True, "font_size": 16, "align": "left"}
            )
            header_fmt = workbook.add_format(
                {"bold": True, "bg_color": "#D9E1F2", "border": 1}
            )
            kpi_header_fmt = workbook.add_format(
                {"bold": True, "bg_color": "#FCE4D6", "border": 1}
            )
            currency_fmt = workbook.add_format(
                {"num_format": "#,##0.00", "border": 1}
            )
            int_fmt = workbook.add_format(
                {"num_format": "#,##0", "border": 1}
            )
            text_fmt = workbook.add_format({"border": 1})
    
            # Set column widths and header format — Raw Data
            for col_idx, col_name in enumerate(df.columns):
                width = max(12, min(40, int(df[col_name].astype(str).str.len().max() + 2)))
                raw_sheet.set_column(col_idx, col_idx, width)
            raw_sheet.freeze_panes(1, 0)
            # Header row format
            raw_sheet.set_row(0, None, header_fmt)
    
            # Summary sheet layout
            summary_sheet.write("A1", report_title, title_fmt)
            summary_sheet.write("A3", "Key Metrics", kpi_header_fmt)
            # Format KPI table
            kpi_rows = len(kpi_df) + 1  # +1 for header
            summary_sheet.set_row(4, None, header_fmt)  # KPI header row (0-based index)
            # Apply some column formats
            summary_sheet.set_column("A:A", 35, text_fmt)
            summary_sheet.set_column("B:B", 20, currency_fmt)
    
            # By Region formatting
            region_sheet.freeze_panes(1, 0)
            region_sheet.set_row(0, None, header_fmt)
            region_sheet.set_column("A:A", 20, text_fmt)
            region_sheet.set_column("B:B", 14, int_fmt)
            region_sheet.set_column("C:C", 16, currency_fmt)
            region_sheet.set_column("D:D", 16, int_fmt)
    
            # By Product formatting
            product_sheet.freeze_panes(1, 0)
            product_sheet.set_row(0, None, header_fmt)
            product_sheet.set_column("A:A", 25, text_fmt)
            product_sheet.set_column("B:B", 14, int_fmt)
            product_sheet.set_column("C:C", 16, currency_fmt)
            product_sheet.set_column("D:D", 16, int_fmt)
    
            # By Month formatting
            month_sheet.freeze_panes(1, 0)
            month_sheet.set_row(0, None, header_fmt)
            month_sheet.set_column("A:A", 12, text_fmt)
            month_sheet.set_column("B:B", 14, int_fmt)
            month_sheet.set_column("C:C", 16, currency_fmt)
            month_sheet.set_column("D:D", 16, int_fmt)
    
            # Create a "Charts" sheet
            chart_sheet = workbook.add_worksheet("Charts")
    
            # ----- Chart: Revenue by Region -----
            region_rows = len(by_region) + 1  # +1 for header
            chart_region = workbook.add_chart({"type": "column"})
    
            chart_region.add_series(
                {
                    "name": "Revenue by Region",
                    "categories": ["By Region", 1, 0, region_rows - 1, 0],  # Region names
                    "values": ["By Region", 1, 2, region_rows - 1, 2],  # Revenue column
                }
            )
            chart_region.set_title({"name": "Revenue by Region"})
            chart_region.set_x_axis({"name": "Region"})
            chart_region.set_y_axis({"name": "Revenue", "major_gridlines": {"visible": False}})
            chart_region.set_style(10)
    
            chart_sheet.insert_chart("B2", chart_region, {"x_scale": 1.3, "y_scale": 1.3})
    
            # ----- Chart: Top Products by Revenue -----
            product_rows = len(by_product) + 1
            chart_product = workbook.add_chart({"type": "column"})
    
            chart_product.add_series(
                {
                    "name": "Revenue by Product",
                    "categories": ["By Product", 1, 0, product_rows - 1, 0],
                    "values": ["By Product", 1, 2, product_rows - 1, 2],
                }
            )
            chart_product.set_title({"name": "Revenue by Product"})
            chart_product.set_x_axis({"name": "Product", "label_position": "low"})
            chart_product.set_y_axis({"name": "Revenue", "major_gridlines": {"visible": False}})
            chart_product.set_style(11)
    
            chart_sheet.insert_chart("B20", chart_product, {"x_scale": 1.3, "y_scale": 1.3})
    
            # Optional: Monthly trend chart
            month_rows = len(by_month) + 1
            if month_rows > 1:
                chart_month = workbook.add_chart({"type": "line"})
                chart_month.add_series(
                    {
                        "name": "Revenue by Month",
                        "categories": ["By Month", 1, 0, month_rows - 1, 0],
                        "values": ["By Month", 1, 2, month_rows - 1, 2],
                    }
                )
                chart_month.set_title({"name": "Revenue Trend by Month"})
                chart_month.set_x_axis({"name": "Month"})
                chart_month.set_y_axis(
                    {"name": "Revenue", "major_gridlines": {"visible": True}}
                )
                chart_month.set_style(2)
    
                chart_sheet.insert_chart("J2", chart_month, {"x_scale": 1.3, "y_scale": 1.3})
    
            # Optional: Autofilter on raw data
            last_row = len(df) + 1
            last_col = len(df.columns) - 1
            raw_sheet.autofilter(0, 0, last_row, last_col)
    
    
    def main():
        args = parse_args()
        df = load_data(args.input)
        kpi_df, by_region, by_product, by_month = build_summaries(df)
        write_excel_report(
            df,
            kpi_df,
            by_region,
            by_product,
            by_month,
            args.output,
            args.title,
        )
        print(f"Report created: {args.output}")
    
    
    if __name__ == "__main__":
        main()
    

    4. How to run

    python generate_excel_report.py \
        --input sales_data.csv \
        --output Sales_Report.xlsx \
        --title "November 2025 Sales Report"
    

    If you tell me more about your actual data structure and what kind of report you need (KPIs, charts, pivots), I can tailor this script exactly to your use case.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.