Create a Python script to automate Excel report generation.
- User
Create a Python script to automate Excel report generation.
- 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.pythat 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 detailsI 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 scriptI'll start adding a chart to visualize revenue by region. Using the
workbook.add_chart({'type': 'column'}), I have to set the series with theregion_summarydata 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 thepd.ExcelWritercontext manager for Excel writing, and apply currency formatting for the revenue columns. I'll name the scriptgenerate_excel_report.pyto 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 nameSalesperson– salesperson nameProduct– product nameUnits– number of units soldRevenue– 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.