Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Merge Several Excel PDF Exports into One File

Combine the PDFs exported from several Excel workbooks into one document with pypdf — a bookmark per report, page labels such as "North 2", a title and an open bookmarks panel — and know what merging loses: accessibility tags and continuous page numbers. When those matter, combine the workbooks first and convert once.
PDF export

A monthly pack is often several separate reports — one workbook per region, a summary, an appendix — that someone wants as a single PDF to read or forward. Converting each workbook and merging the PDFs is quick with pypdf, and the result can be made pleasant to navigate with bookmarks and page labels. It also quietly loses two things people notice later: the accessibility tags and continuous page numbering. This guide, part of Exporting Excel Reports to PDF, merges exports properly, measures what changes, and shows when to combine the workbooks before converting instead.

Four regional exports, one navigable PDF Four PDFs exported from regional workbooks are merged in order into a single 14-page document. Each region gets a bookmark pointing at its first page and page labels that restart per region, and the document gets a title and opens with the bookmarks panel showing. regional_pack.pdf 14 pages, 4 bookmarks North pages 1–2 South pages 3–5 East pages 6–9 West pages 10–14 PdfWriter.append(path, outline_item=region) for each file, in order

Prerequisites

Bash
pip install pypdf pandas openpyxl

The exports come from LibreOffice, as in Convert Excel to PDF on Linux with LibreOffice. Four regional workbooks of different lengths stand in for the real reports:

Python
import pandas as pd

regions = ["North", "South", "East", "West"]
for i, region in enumerate(regions):
    rows = 60 + i * 45
    pd.DataFrame({
        "store": [f"{region} {k:03d}" for k in range(rows)],
        "sales": range(rows),
    }).to_excel(f"{region}.xlsx", index=False)

Step 1 — Convert all the workbooks in one call

LibreOffice accepts several input files in one command. Starting it once instead of once per file saves its start-up time on every file — 0.5 seconds against 1.7 for these four, and the gap grows with the number of reports:

Python
import subprocess

subprocess.run(
    ["soffice", "--headless", "--convert-to", "pdf", "--outdir", "pdf"]
    + [f"{region}.xlsx" for region in regions],
    check=True, timeout=300,
)

The four PDFs came out at 2, 3, 4 and 5 pages. Page setup — orientation, fitting wide sheets, repeated headers — has to be right in each workbook before this step; Fit an Excel Sheet to One Page Wide When Exporting to PDF covers it.

Step 2 — Merge with a bookmark per report

PdfWriter.append copies every page of a file onto the end of the output. Its outline_item argument adds a bookmark pointing at the first appended page, so the reader can jump straight to a region:

Python
from pypdf import PdfReader, PdfWriter

writer = PdfWriter()
for region in regions:
    writer.append(f"pdf/{region}.pdf", outline_item=region)
writer.write("regional_pack.pdf")

pack = PdfReader("regional_pack.pdf")
print(len(pack.pages))                                # 14

LibreOffice already writes a bookmark per sheet into each export, and append imports those by default, nesting them under the new bookmark. With one sheet per workbook that produces a pointless "Sheet1" under every region; pass import_outline=False to leave them out, or keep them when each workbook has several meaningful sheets.

Step 3 — Page labels, a title and an open bookmarks panel

A 14-page merged file shows "page 7 of 14" in the viewer, which says nothing about where the reader is. PDF page labels replace those numbers with anything you like — here the region and the page within it. A document title and a page mode that opens the bookmarks panel finish the job:

Python
writer = PdfWriter()
for region in regions:
    start = len(writer.pages)
    writer.append(f"pdf/{region}.pdf", outline_item=region, import_outline=False)
    writer.set_page_label(start, len(writer.pages) - 1, style="/D", prefix=f"{region} ", start=1)

writer.add_metadata({"/Title": "Regional sales, September 2026"})
writer.page_mode = "/UseOutlines"                    # open with bookmarks showing
writer.write("regional_pack.pdf")

pack = PdfReader("regional_pack.pdf")
print(pack.page_labels[:5], pack.page_labels[-1])
# ['North 1', 'North 2', 'South 1', 'South 2', 'South 3'] West 5

Viewers show the title in the window bar instead of the file name, and "South 2" in the page box. Page labels change what the viewer displays, not what is printed on the page — footers from the original exports still say whatever they said.

Same 14 pages, three different files The four LibreOffice exports total 337 KB and are tagged for accessibility. Merged with pypdf they become a 42 KB file without tags, because the structure tree is not copied. Combining the four sheets into one workbook and converting once gives a 311 KB tagged file with page numbers running from 1 to 14. 4 exports, tagged 337 KB pypdf merge, untagged 42 KB one workbook, converted once 311 KB the merged file is small because it lost its structure tree

Step 4 — Know what the merge drops

The merged file was 42 KB; the four inputs totalled 337 KB. Nothing visible was lost — every page and its fonts are there — but pdfinfo tells the story: the exports are tagged, carrying a structure tree that screen readers use to read tables in order, and the merged file is not. append copies pages, not the document-level structure that describes them. For internal packs this rarely matters; for documents that must meet accessibility requirements, it rules the merge out.

The second loss is page numbering. A footer of "Page &P of &N" in each workbook printed "Page 1 of 3" on South's first page, and after merging it still does, on page 3 of 14. Page labels help on screen; they do not change the printed footer.

Deduplicating does not win the size back either. Each export embeds its own subset of the same font with different glyphs, so writer.compress_identical_objects() found nothing to remove in this test.

Step 5 — Or combine the workbooks and convert once

When the reports are generated by your own code, write them as sheets of one workbook and convert that. LibreOffice then produces one tagged PDF, a bookmark per sheet, and page numbers that run through the whole document:

Python
from openpyxl import load_workbook

with pd.ExcelWriter("regional_pack.xlsx") as writer:
    for region in regions:
        pd.read_excel(f"{region}.xlsx").to_excel(writer, sheet_name=region, index=False)

wb = load_workbook("regional_pack.xlsx")
for ws in wb.worksheets:
    ws.oddFooter.center.text = "Page &P of &N"
wb.save("regional_pack.xlsx")
print(wb.sheetnames)
# ['North', 'South', 'East', 'West']

Converted, this gave a 14-page, tagged PDF whose footers read "Page 3 of 14" on the third page and "Page 14 of 14" on the last, with bookmarks named after the sheets. The catch is formatting: copying data through pandas loses styles, and openpyxl cannot copy a sheet from one workbook into another. This route suits reports you build from data; for finished workbooks from different sources, merging PDFs is the practical option. Headers and footers are covered in Add Headers, Footers and Page Numbers to an Excel PDF.

Merge the PDFs, or convert one workbook? Merging PDFs with pypdf works for any exports, including formatted workbooks from different sources, and adds bookmarks and page labels, but drops accessibility tags and keeps each export's own page numbers. Converting one combined workbook keeps tags and continuous page numbers, but the workbook has to be built from data because sheets cannot be copied between workbooks with their formatting. Merge PDFs with pypdf any exports, any source bookmarks and page labels − untagged result − page numbers restart per file One workbook, one conversion tagged PDF Page 3 of 14 in the footer sheet bookmarks for free − sheets built from data, not copied

Step 6 — Check the result

Verify the merged document before it is sent: the page count must equal the sum of the parts, and every bookmark must point where it should:

Python
parts = [len(PdfReader(f"pdf/{region}.pdf").pages) for region in regions]
pack = PdfReader("regional_pack.pdf")
assert len(pack.pages) == sum(parts), (len(pack.pages), parts)

expected, page = {}, 0
for region, count in zip(regions, parts):
    expected[region] = page
    page += count
found = {item.title: pack.get_destination_page_number(item) for item in pack.outline}
assert found == expected, found

Assertions like these catch the usual mistakes — a missing export, a stale PDF from the previous month in the folder, files merged in directory order instead of the intended one.

Common pitfalls

  • Merging in directory order. glob returns files in arbitrary order. Build the list explicitly, in the order readers expect.
  • Stale exports. If a conversion fails, last month's PDF may still be in the output folder and gets merged. Empty the folder before converting, and check each PDF is newer than its workbook.
  • Encrypted inputs. A password-protected PDF must be decrypted with PdfReader(...).decrypt(password) before its pages can be appended.
  • Very large packs. append holds the output in memory until write. For hundreds of reports, merge in batches.
  • Bookmark titles from file names. Names such as rpt_north_final2 end up in the reader's bookmarks panel. Map each file to a proper title — "North region" — when building the list.
  • Writing over the pack that is being read. Writing regional_pack.pdf while a colleague has it open fails on Windows and can leave a truncated file on a share. Write to a temporary name in the same folder and rename it when complete.

Conclusion

Convert all the workbooks in one LibreOffice call, merge the PDFs with PdfWriter.append and a bookmark per report, and add page labels, a title and an open bookmarks panel so the pack is easy to navigate. Merging drops the accessibility tags and keeps each export's own page numbers; when those matter and the reports are built from data, write them as sheets of one workbook and convert once.

Frequently asked questions

How do I merge PDF files in Python? Use pypdf's PdfWriter. Call writer.append(path, outline_item="Name") for each file in order, then writer.write("merged.pdf"). The outline_item argument adds a bookmark pointing at the first page of that file.

Why is the merged PDF much smaller than the files I merged? LibreOffice exports tagged PDFs that include a structure tree for screen readers. pypdf's append does not carry that tree over, so the merged file is untagged. In our test four exports totalling 337 KB merged into 42 KB.

How do I get page numbers that continue across the merged reports? Footers printed by each export restart at 1. Either put all the sheets into one workbook and convert it once, which numbered pages 1 to 14 continuously in our test, or use page labels so the PDF viewer shows section-based numbers such as "South 2".

Can I keep each workbook's sheet bookmarks when merging? Yes. pypdf imports the source file's outline by default and nests it under the bookmark you pass as outline_item. Pass import_outline=False to leave them out.

More on pdf export

More on pdf export →