Building a Multi Sheet Excel Report From SQL Queries

A report for people is rarely one table. It has a summary on the first sheet, the detail behind it, and perhaps one sheet for each region so each manager finds their own numbers. Building that from SQL is straightforward once you decide how the sheets relate. This post builds a three-part workbook: a summary, a detail sheet and a sheet per region, linked by formulas and hyperlinks. It is a spoke of sending database results to Excel.

The queries

Each sheet comes from its own query, or from one query split in pandas. Here one detail query feeds everything:

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://reporter@localhost/app")
detail = pd.read_sql(text("""
    SELECT region, order_no, customer, amount_eur AS amount, placed_at::date AS placed
    FROM orders
    WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01'
    ORDER BY region, placed_at, order_no
"""), engine)
detail["order_no"] = detail["order_no"].astype(str)
detail["amount"] = detail["amount"].astype(float)

Fetch once and split in memory, unless the pieces are different queries. One query run guarantees that every sheet comes from the same snapshot, which prevents sheets that disagree because data changed between two queries. If the pieces must come from different queries, run them inside one transaction with a repeatable-read snapshot, or accept and document the small gap.

Write the sheets

regions = sorted(detail["region"].unique())

with pd.ExcelWriter("march_report.xlsx", engine="xlsxwriter", date_format="yyyy-mm-dd") as writer:
    book = writer.book
    money = book.add_format({"num_format": "#,##0.00"})

    detail.to_excel(writer, sheet_name="Detail", index=False)
    d = writer.sheets["Detail"]
    d.freeze_panes(1, 0)
    d.autofilter(0, 0, len(detail), len(detail.columns) - 1)
    d.set_column("A:A", 12)
    d.set_column("B:C", 16)
    d.set_column("D:D", 12, money)
    d.set_column("E:E", 12)

    for region in regions:
        part = detail[detail["region"] == region].drop(columns="region")
        name = f"Region {region}"[:31]
        part.to_excel(writer, sheet_name=name, index=False)
        ws = writer.sheets[name]
        ws.freeze_panes(1, 0)
        ws.set_column("A:B", 16)
        ws.set_column("C:C", 12, money)
        ws.set_column("D:D", 12)

The Detail sheet holds every order with filters. Each region sheet is a slice without the region column, since the sheet name says which region it is. The sheet name is built from the region value and cut to 31 characters, which is Excel's limit. Names must also be unique, ignoring case, and cannot contain :, \, /, ?, *, [ or ], or start or end with an apostrophe. A region called North/East would make the writer raise an error, so sanitize:

import re

def sheet_name(text, used):
    base = re.sub(r"[:\\/?*\[\]]", "-", text).strip("'")[:31] or "Sheet"
    name, n = base, 2
    while name.lower() in used:
        suffix = f" ({n})"
        name = base[:31 - len(suffix)] + suffix
        n += 1
    used.add(name.lower())
    return name

The function replaces forbidden characters, trims to 31 and appends a counter if the name is taken. Keep a used set across all sheets. The sheet order in the file is the order you write them in; to put the summary first, create it first (even before its formulas can be filled) or reorder with book.worksheets_objs.

The summary sheet with formulas

A summary that reads from the detail sheet through formulas keeps the numbers traceable: a reader clicks a total and sees where it comes from. Write the summary after the detail so you know the ranges:

    summary = book.add_worksheet("Summary")
    writer.sheets["Summary"] = summary
    bold = book.add_format({"bold": True})
    summary.write_row(0, 0, ["Region", "Orders", "Amount"], bold)

    last = len(detail) + 1
    for i, region in enumerate(regions, start=1):
        summary.write(i, 0, region)
        summary.write_formula(i, 1, f'=COUNTIF(Detail!$A$2:$A${last}, A{i + 1})')
        summary.write_formula(i, 2, f'=SUMIF(Detail!$A$2:$A${last}, A{i + 1}, Detail!$D$2:$D${last})', money)

    total_row = len(regions) + 1
    summary.write(total_row, 0, "Total", bold)
    summary.write_formula(total_row, 1, f"=SUM(B2:B{total_row})", bold)
    summary.write_formula(total_row, 2, f"=SUM(C2:C{total_row})", money)
    summary.set_column("A:A", 14)
    summary.set_column("B:B", 10)
    summary.set_column("C:C", 14)

The detail sheet's first column is the region, which is column A, and the amount is column D after the region, order number and customer. That is why the formulas reference Detail!$A$2:$A$... for the region and Detail!$D$2:$D$... for the amount. When the detail columns change, these references must change with them; keep the column letters in constants near the top of the script and build the formulas from them.

Two cautions. Formulas written by a library have no cached value, so a program that reads the file without recalculating sees empty totals until Excel opens and saves it. XlsxWriter lets you pass the computed value as a fourth argument to write_formula, so the file is correct for both kinds of reader:

summary.write_formula(i, 2, f'=SUMIF(Detail!$A$2:$A${last}, A{i + 1}, Detail!$D$2:$D${last})', money,
                      detail.loc[detail["region"] == region, "amount"].sum())

The value is only a cache and Excel recalculates on open. And be sure the formula and the cached value agree, or two readers will see two numbers (what happens to formulas when code reads an Excel file). The second caution: functions introduced after Excel 2007 need special handling in some libraries, so stick to the classic ones (SUM, SUMIF, COUNTIF) in generated files unless you know your library supports the newer functions (adding formulas and number formats from Python).

An index sheet with links

With ten or twenty sheets, an index helps. Internal hyperlinks use the internal: prefix and a sheet reference, and a sheet name that contains spaces must be quoted with apostrophes:

    index = book.add_worksheet("Index")
    link = book.add_format({"font_color": "blue", "underline": 1})
    index.write(0, 0, "Sheet", bold)
    index.write(0, 1, "Contents", bold)
    entries = [("Summary", "Totals by region"), ("Detail", "Every order in the period")]
    entries += [(f"Region {r}"[:31], f"Orders for {r}") for r in regions]
    for i, (name, what) in enumerate(entries, start=1):
        index.write_url(i, 0, f"internal:'{name}'!A1", link, string=name)
        index.write(i, 1, what)
    index.set_column("A:A", 20)
    index.set_column("B:B", 36)

The target 'Region North'!A1 works with spaces because of the quotes. If a name contains an apostrophe, double it inside the quotes. The sheet named Index appears in the order it was created, so create it first if it should be the first tab, or move it with index.set_first_sheet() and activate() for the active tab. Setting summary.activate() makes the workbook open on the summary, which is usually the right landing page.

A note on readers who need the data

Splitting data across sheets by region is a presentation choice that makes the data harder to use. A reader who wants to pivot across all regions has to combine the sheets by hand. Keep one sheet with all rows and the region as a column (the Detail sheet here), and treat the region sheets as convenient views. Name it plainly, and say so on the index. For programs that read the file, document that the Detail sheet is the data, and the others are derived.

The same reasoning applies to a sheet per month. If a later import must combine twelve sheets, it has to check that each has the same header (loading multiple Excel sheets into separate tables).

Checks before you send

Open the file and test it. Click each total and confirm that its formula points where you intended. Change one amount on the detail sheet and watch the summary follow, which proves the formulas are live. Check that the number of rows on the detail sheet equals the query's count and that the sum of the region sheets equals the detail total. Read the file back with openpyxl and assert sheet names and counts, so a refactor cannot silently drop a sheet. Finally, make sure no sheet name was truncated into a duplicate: Region North America and Region North Asia both cut to the first 31 characters might collide, which the sheet_name helper above resolves.

When a multi sheet workbook is the wrong report design

The sheets are partitions of one table. Readers and programs prefer one table with a column. Provide it, and treat sheets as an extra.

There are dozens or hundreds of sheets. A workbook with 200 tabs is slow to open and impossible to navigate. Use separate files per region, or a filterable single table.

The numbers should drive decisions in a tool. A BI dashboard with filters serves that need better than a static workbook.

The workbook will be re-imported. Keep it flat and uniform, and avoid summary sheets that mix totals with data.

The formula and link calls are documented in the XlsxWriter pages on write_formula and write_url, and the sheet name rules in Microsoft's Excel specifications and limits. To check a small extract as JSON, use the CSV to JSON converter.