Excel is a powerful tool for creating daily reports, charts, and data analysis workflows. However, when reports rely on large datasets stored in a SQL database, manual extraction quickly becomes inefficient and error-prone.

In this post, I’ll show you how to automate Excel report generation using Python and SQL Server. We’ll extract data from a SQL Server database, process it with pandas, and automatically generate an Excel file with multiple sheets—saving time and creating a scalable reporting solution.

This approach is especially useful if you:

  • Generate daily or weekly Excel reports from SQL databases
  • Work with datasets that exceed Excel row limits
  • Want to eliminate manual copy-paste workflows

The Challenge: Large Excel Reports from SQL Databases

A few months ago, I received a client request for an Excel report containing data from a SQL database. The dataset was so large that it couldn’t fit into a single Excel sheet.

Manually splitting the data by month and copying it into multiple sheets would have been a nightmare. Instead of doing repetitive work, I decided to automate the entire process using Python.

The Automated Solution Using Python and SQL

The idea was simple:

  • Query the database once
  • Load the data into Python
  • Split the data by month
  • Export everything into a single Excel file with multiple sheets

How the Automation Works

The automation follows these steps:

  1. Connect to a SQL Server database.
  2. Execute a SQL query and load the results into a pandas DataFrame.
  3. Split the data by month.
  4. Export each month into a separate Excel sheet.

This approach is especially useful when datasets are too large to fit into a single worksheet.

Required Python Libraries

First, we need to import the necessary packages.

# pyodbc is used to connect to SQL Server databases
# pandas is used to manipulate and transform the data
# xlsxwriter is used to generate Excel files with multiple sheets
import pyodbc
import pandas as pd
from xlsxwriter import Workbook

Connecting to the SQL Server Database

Create the database connection. Replace SERVER_NAME, DATABASE, USERNAME, and PASSWORD with your own values.

conn = pyodbc.connect(
    'DRIVER={SQL SERVER};'
    'SERVER=YOUR_SERVER_NAME;'
    'DATABASE=YOUR_DATABASE;'
    'UID=USERNAME;'
    'PWD=PASSWORD;'
)

Querying the Data

Define the SQL query to extract the data needed for the report:

sql_query = '''
    SELECT
        transaction_date,
        account_number,
        transaction_type,
        amount
    FROM transactions
'''

# Execute the query and load the results into a pandas DataFrame
query_df = pd.read_sql(sql_query, conn)

# You might see a warning from pandas about SQLAlchemy.
# This happens because pandas prefers SQLAlchemy connections.
# For this example, it is safe to ignore.

Creating the Excel Report

Create an Excel writer object. This allows writing multiple DataFrames into different sheets.

writer = pd.ExcelWriter('path/to/your/file/ExcelReport.xlsx')

# Define the list of months.
# Each month will become a separate Excel sheet.
months = [
    'January', 'February', 'March', 'April', 'May', 'June',
    'July', 'August', 'September', 'October', 'November', 'December'
]

# Loop through each month, filter the data,
# and write it to its corresponding sheet.
for month in months:
    df = query_df[
        pd.to_datetime(query_df['transaction_date'])
        .dt.strftime('%B') == month
    ]
    df.to_excel(writer, sheet_name=month, index=False)

# Close the writer to generate the final Excel file
writer.close()

Final Thoughts

This automation can be extended by:

  • Adding error handling and logging
  • Externalizing configuration (paths, credentials)
  • Scheduling the script using Windows Task Scheduler or cron
  • Switching to SQLAlchemy for better database abstraction

Automating Excel reports like this can save hours of manual work and scale easily as your datasets grow.