Python Doc & Data Automation

Write Multiple Sheets to One Excel File

A monthly pack needs one workbook with a summary sheet, one sheet per region and a data-quality sheet. The first attempt writes each frame with to_excel in a loop and ends up with a workbook containing only the last frame — every call overwrote the file. Fixing that with ExcelWriter produces new problems: InvalidWorksheetName: Excel worksheet name 'EMEA / North Africa – detail' must be <= 31 chars, two regions whose names differ only after 31 characters collide, appending to an existing template silently replaces its sheets, and a 900,000-row detail sheet pushes memory past the container limit.

Root Cause

DataFrame.to_excel(path) creates a new workbook each time it is given a path, so a loop leaves only the final frame. pd.ExcelWriter keeps one workbook open across several to_excel calls, which is the correct tool, but its behaviour depends on two arguments people rarely set: mode ("w" creates a new file, "a" appends to an existing one) and if_sheet_exists (only valid in append mode; "error", "replace", "overlay" or "new"). Excel itself imposes rules the writer enforces: sheet names must be 1 to 31 characters, cannot contain : \ / ? * [ ], and must be unique — a workbook with Region: EMEA / North Africa (detail) fails on three counts at once. Finally, both engines build the whole workbook in memory before saving, so a multi-sheet export of millions of rows needs either xlsxwriter's constant-memory mode or a different output format.

Minimal Diagnostic

Check the sheet names you are about to write against Excel's rules and against each other, before spending minutes computing the data.

# stdlib only
import re

INVALID = re.compile(r"[:\\/?*\[\]]")

def sheet_name_report(names: list[str]) -> None:
    seen: dict[str, str] = {}
    for name in names:
        issues = []
        if not name.strip():
            issues.append("empty")
        if len(name) > 31:
            issues.append(f"{len(name)} chars (max 31)")
        if INVALID.search(name):
            issues.append(f"invalid characters: {''.join(sorted(set(INVALID.findall(name))))}")
        if name.startswith("'") or name.endswith("'"):
            issues.append("leading/trailing apostrophe")
        key = name[:31].strip().casefold()
        if key in seen:
            issues.append(f"collides with {seen[key]!r} after truncation")
        seen.setdefault(key, name)
        print(f"{name!r:<46} {'; '.join(issues) or 'ok'}")

if __name__ == "__main__":
    sheet_name_report([
        "Summary",
        "Region: EMEA / North Africa (detail)",
        "Region: EMEA / North Africa (summary)",
        "Data quality",
    ])

'Summary'                                      ok
'Region: EMEA / North Africa (detail)'         36 chars (max 31); invalid characters: /:
'Region: EMEA / North Africa (summary)'        36 chars (max 31); invalid characters: /:; collides with 'Region: EMEA / North Africa (detail)' after truncation
'Data quality'                                 ok

Two names are invalid and would collide even after truncation — the kind of failure that appears only when a new region is added.

Excel sheet name rules Names may be at most 31 characters, so a 36 character region name is truncated. The characters colon, backslash, forward slash, question mark, asterisk and square brackets are not allowed and are replaced with a dash. Names cannot start or end with an apostrophe. Names must be unique case-insensitively, so a truncation collision gets a numeric suffix. Empty names are replaced with a placeholder. Rule Breaks on Sanitised to Max 31 characters 36-char region name truncate to 31 No : \ / ? * [ ] Region: EMEA / NA Region - EMEA - NA No leading apostrophe 'Q1 Q1 Unique two truncated names suffix _2

Fix: One Writer, Sanitised Names, Per-Sheet Formatting

Open one ExcelWriter, sanitise names deterministically, and format each sheet as it is written. Changed lines carry comments.

# pip install "pandas>=2.2" xlsxwriter
import re
from pathlib import Path
import pandas as pd

INVALID = re.compile(r"[:\\/?*\[\]]")

def safe_sheet_name(name: str, used: set[str]) -> str:
    clean = INVALID.sub("-", str(name)).strip().strip("'") or "Sheet"      # changed: legal characters
    clean = re.sub(r"\s+", " ", clean)[:31]                                 # changed: length limit
    candidate, n = clean, 2
    while candidate.casefold() in used:                                     # changed: uniqueness
        suffix = f"_{n}"
        candidate = clean[:31 - len(suffix)] + suffix
        n += 1
    used.add(candidate.casefold())
    return candidate

def write_workbook(sheets: dict[str, pd.DataFrame], dest: Path) -> dict[str, str]:
    dest.parent.mkdir(parents=True, exist_ok=True)
    used: set[str] = set()
    mapping: dict[str, str] = {}
    with pd.ExcelWriter(dest, engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:  # changed: one writer
        book = writer.book
        header = book.add_format({"bold": True, "bg_color": "#EFF6FF", "bottom": 1})
        money = book.add_format({"num_format": "#,##0.00"})
        for raw_name, frame in sheets.items():
            name = safe_sheet_name(raw_name, used)
            mapping[raw_name] = name
            frame.to_excel(writer, sheet_name=name, index=False, startrow=1, header=False)  # changed: custom header
            ws = writer.sheets[name]
            for col, title in enumerate(frame.columns):
                ws.write(0, col, str(title), header)
                width = max(len(str(title)), *(frame[title].astype(str).str.len().head(200) or [0])) + 2
                fmt = money if pd.api.types.is_numeric_dtype(frame[title]) else None
                ws.set_column(col, col, min(width, 48), fmt)               # changed: readable widths
            ws.freeze_panes(1, 0)
            if len(frame):
                ws.autofilter(0, 0, len(frame), len(frame.columns) - 1)
    return mapping

if __name__ == "__main__":
    data = {
        "Summary": pd.DataFrame({"region": ["EMEA", "APAC"], "revenue": [128400.0, 91200.0]}),
        "Region: EMEA / North Africa (detail)": pd.DataFrame({"order": ["SO-1"], "revenue": [1240.5]}),
        "Region: EMEA / North Africa (summary)": pd.DataFrame({"month": ["2026-09"], "revenue": [128400.0]}),
    }
    print(write_workbook(data, Path("out/monthly-pack.xlsx")))

Returning the mapping from requested name to actual sheet name matters when other code — a summary sheet with links, or a downstream reader — needs to find sheets by name. Writing the header row separately with header=False, startrow=1 is what allows a formatted header; pandas' own header cannot carry a format in xlsxwriter.

Building a multi-sheet workbook One ExcelWriter is opened for the destination. For each frame the sheet name is sanitised for length, characters and uniqueness, and the mapping is recorded. The frame is written without its header, then the header row is written with a format, column widths are set, panes frozen and an autofilter added. After all sheets, an index sheet is inserted first with links to each sheet. The writer's context manager saves the workbook once. Open one writer pd.ExcelWriter(dest, engine='xlsxwriter') Sanitise names length, characters, uniqueness; record the mapping Write and format data, header row, widths, freeze, autofilter Index sheet links to every sheet, written first in the workbook Save once the context manager writes the file on exit

Variant Fix 1: Appending to an Existing Workbook

To add sheets to a workbook that already exists — a template with instructions and formatting — open the writer in append mode with openpyxl and decide what happens to existing sheets:

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd

def append_sheets(template: Path, dest: Path, sheets: dict[str, pd.DataFrame]) -> None:
    dest.parent.mkdir(parents=True, exist_ok=True)
    dest.write_bytes(template.read_bytes())                      # work on a copy, keep the template intact
    with pd.ExcelWriter(dest, engine="openpyxl", mode="a",       # append to the existing workbook
                        if_sheet_exists="replace") as writer:    # 'error' | 'replace' | 'overlay' | 'new'
        for name, frame in sheets.items():
            frame.to_excel(writer, sheet_name=name[:31], index=False)

if_sheet_exists="overlay" writes into an existing sheet without clearing it, which is how you fill a formatted template region while keeping its headers and conditional formatting — pair it with startrow/startcol to place the block. "replace" deletes the sheet and creates a new one, discarding its formatting. Append mode requires the openpyxl engine, and pandas raises ValueError: Append mode is not supported with xlsxwriter otherwise. Preserving rules and validations in templates is covered in Conditional Formatting and Data Validation in Excel.

Packs with fifteen sheets need a way in. Build an index as the first sheet, with internal links and row counts:

# pip install "pandas>=2.2" xlsxwriter
import pandas as pd

def add_index(writer: pd.ExcelWriter, mapping: dict[str, str], sheets: dict[str, pd.DataFrame]) -> None:
    book = writer.book
    ws = book.add_worksheet("Index")
    book.worksheets_objs.insert(0, book.worksheets_objs.pop())         # move Index to the front
    title = book.add_format({"bold": True, "font_size": 14})
    header = book.add_format({"bold": True, "bottom": 1})
    link = book.add_format({"font_color": "blue", "underline": 1})
    ws.write(0, 0, "Monthly pack – contents", title)
    for col, text in enumerate(["Sheet", "Rows", "Columns"]):
        ws.write(2, col, text, header)
    for row, (raw, name) in enumerate(mapping.items(), start=3):
        frame = sheets[raw]
        ws.write_url(row, 0, f"internal:'{name}'!A1", link, string=raw)  # clickable link to the sheet
        ws.write_number(row, 1, len(frame))
        ws.write_number(row, 2, len(frame.columns))
    ws.set_column(0, 0, 46)
    ws.set_column(1, 2, 12)

Call it just before the writer closes, when every sheet exists. The internal:'Sheet name'!A1 form is what xlsxwriter needs for a link to another sheet; quoting the name handles sheets with spaces. Readers open the pack, see what is in it, and click through — a small touch that noticeably reduces "where is the EMEA detail?" emails.

Layout of the finished pack The workbook opens on the Index sheet, which lists every sheet with its row and column counts and links to it. The Summary sheet holds the aggregated figures. Region sheets follow, one per region, each with a frozen header row and an autofilter. The Data quality sheet at the end lists rows rejected during validation so reviewers can see what the summary excludes. monthly-pack.xlsx, sheet order 1 1 Index links and row counts 2 2 Summary aggregated figures 3 3 Region sheets one per region 4 4 Data quality rejected rows

Large Exports Without Running Out of Memory

Both engines hold the workbook in memory. For very large sheets, xlsxwriter's constant-memory mode writes each row to disk as it is added, at the cost of requiring rows in order and disallowing later edits to written cells:

# pip install "pandas>=2.2" xlsxwriter
from pathlib import Path
import pandas as pd

def write_large(dest: Path, chunks, sheet: str = "Detail") -> int:
    rows = 0
    with pd.ExcelWriter(dest, engine="xlsxwriter",
                        engine_kwargs={"options": {"constant_memory": True}}) as writer:
        for i, chunk in enumerate(chunks):                        # chunks from read_csv(chunksize=...)
            chunk.to_excel(writer, sheet_name=sheet, index=False,
                           header=(i == 0), startrow=rows + (1 if i else 0))
            rows += len(chunk)
    return rows

In constant-memory mode, column widths and formats must be set before the rows they affect are written, and autofilter or freeze_panes must be called early. Excel's own limit is 1,048,576 rows per sheet; beyond that, split across sheets or write Parquet or CSV instead, as in convert a large CSV to Parquet with Python.

Verification

Read the workbook back and assert the sheets, their order, row counts and that nothing was silently truncated or overwritten.

# pip install "pandas>=2.2" openpyxl
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

def verify_workbook(dest: Path, mapping: dict[str, str], sheets: dict[str, pd.DataFrame]) -> None:
    wb = load_workbook(dest, read_only=True)
    try:
        names = wb.sheetnames
        assert len(names) == len(set(n.casefold() for n in names)), f"duplicate sheet names: {names}"
        for raw, name in mapping.items():
            assert name in names, f"sheet {name!r} (from {raw!r}) missing"
            ws = wb[name]
            expected_rows = len(sheets[raw]) + 1                  # data plus header
            assert ws.max_row == expected_rows, f"{name}: {ws.max_row} rows, expected {expected_rows}"
            assert ws.max_column == len(sheets[raw].columns), f"{name}: column count differs"
    finally:
        wb.close()
    print(f"{dest.name}: {len(mapping)} sheet(s) verified, order {load_workbook(dest, read_only=True).sheetnames[:3]}")

Reading with read_only=True keeps the check fast on large packs. Asserting on the mapping rather than on raw names is what makes the test survive sanitisation — the point is that every requested frame landed somewhere findable, not that the name was unchanged.

FAQ

Why did my loop leave only one sheet? Each to_excel(path) call created a new file. Use one ExcelWriter for all sheets.

Can I control sheet order? Sheets appear in write order. With xlsxwriter you can reorder book.worksheets_objs; with openpyxl use wb.move_sheet(name, offset) after writing.

How do I write the same frame to two sheets? Call to_excel twice with different sheet_name values; the data is written independently.

Is xlsxwriter or openpyxl the better engine? xlsxwriter for new files with rich formatting and constant-memory mode; openpyxl for appending to or editing existing workbooks.

Part of Automating Excel Report Generation.