How Do I Generate an Excel Report Automatically Using Python?

The raw data is clean. The files have been merged. Now comes the final, tedious step: creating the weekly summary report. You open Excel and begin the ritual: creating pivot tables, calculating totals, formatting headers in the company's brand color, adjusting column widths, and adding a summary chart. It's a manual process that takes an hour every single week.

What if you could run a single script that does all of that—the data analysis, the formatting, the chart creation—and delivers a pixel-perfect, presentation-ready Excel report in seconds?

This is not just possible; it's one of the most powerful and practical applications of Python. Let's build a complete, end-to-end report generator.

The Goal: From Raw Data to a Polished Report

Our objective is to take a raw data file and automatically produce a professional report with:

A summary table (the equivalent of a pivot table).
Clean, professional formatting (bold headers, number formats).
A chart to visualize the summary data.
The raw data and the summary on separate sheets.

The Tools for the Job: `pandas` and `XlsxWriter`

We'll use two libraries working in harmony:

pandas: For the heavy lifting. We'll use it to read the raw data and perform the aggregation (.groupby()) to create our summary table.
XlsxWriter: This is a fantastic library for writing Excel files. While pandas can do a basic data dump, XlsxWriter gives us granular control to add charts, custom formatting, formulas, and much more.

First, you'll need to install the necessary libraries:

pip install pandas openpyxl xlsxwriter

The Step-by-Step Code Solution

Let's imagine we have a raw data file named sales_data.xlsx with columns: Region, Product, and Sales_Amount.

Step 1: Import Libraries and Load Data

Python
import pandas as pd

# Load the raw sales data into a pandas DataFrame
df = pd.read_excel("sales_data.xlsx")

Step 2: Aggregate the Data (Create the "Pivot Table")

We'll use the powerful .groupby() method to create a summary of total sales by region.

Python
# Group by 'Region' and calculate the sum of 'Sales_Amount'
summary_df = df.groupby('Region')['Sales_Amount'].sum().reset_index()

Step 3: Set Up the Excel Writer

This is the key to connecting pandas with XlsxWriter. We create an ExcelWriter object.

Python
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('automated_sales_report.xlsx', engine='xlsxwriter')

Step 4: Write DataFrames to Separate Sheets

Now we can write our raw data and our new summary data to different sheets within the same Excel file.

Python
# Write each DataFrame to a specific sheet.
df.to_excel(writer, sheet_name='Raw_Data', index=False)
summary_df.to_excel(writer, sheet_name='Summary', index=False)

Step 5: Add Formatting and a Chart (The Premium Touch)

This is where XlsxWriter shines. We access the workbook and worksheet objects to add professional touches.

Python
# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
summary_sheet = writer.sheets['Summary']

# Add some cell formats.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC', # A nice green
    'border': 1})

currency_format = workbook.add_format({'num_format': '$#,##0.00'})

# Apply the header format to the summary sheet.
for col_num, value in enumerate(summary_df.columns.values):
    summary_sheet.write(0, col_num, value, header_format)

# Set the column widths and format for the summary sheet.
summary_sheet.set_column('A:A', 15)
summary_sheet.set_column('B:B', 20, currency_format)

# --- Create a bar chart ---
chart = workbook.add_chart({'type': 'bar'})

# Configure the chart.
chart.add_series({
    'name':       'Total Sales by Region',
    'categories': '=Summary!$A$2:$A$5', # Regions
    'values':     '=Summary!$B$2:$B$5', # Sales values
})

chart.set_title({'name': 'Regional Sales Summary'})
chart.set_legend({'position': 'none'})

# Insert the chart into the worksheet.
summary_sheet.insert_chart('D2', chart)

Step 6: Save the Report

This is the final, crucial step. We close the ExcelWriter object, which saves the file to disk.

Python

# Close the Pandas Excel writer and output the Excel file.
writer.close()

print("Automated Excel report has been generated successfully!")

Frequently Asked Questions (FAQs)

1. Why use XlsxWriter instead of just pandas' to_excel()?

The standard to_excel() is great for quickly saving data. But to add charts, conditional formatting, formulas, and other advanced Excel features, you need to use a dedicated writing "engine" like XlsxWriter or openpyxl.

2. This seems complicated. Is there an easier way?

While there's a learning curve, this method offers almost unlimited customization. Think of it as writing a recipe for your perfect report. Once the recipe is written, you can create the report perfectly every time with zero effort.

3. Can I add Excel formulas with this method?

Yes! You can use the worksheet.write_formula() method to insert any valid Excel formula into a cell, which will be calculated when you open the file.

Conclusion: You've Built an Automation Engine

You have now graduated from simple data cleaning to building a complete, end-to-end reporting pipeline. By combining the data-crunching power of pandas with the sophisticated formatting capabilities of XlsxWriter, you can automate the creation of polished, professional reports.

This is a hugely valuable skill. A process that once took an hour of manual work can now be a script you run on a schedule, delivering perfect results every time.

Search This Blog

The AI Journal