Add Bookmarks and a Cover Page to an Excel PDF Export

A workbook with a summary sheet and a sheet per region converts to a perfectly good PDF — but readers of a 30-page board pack expect a cover with the title and date, a contents list, and bookmarks in the side panel that jump to each section. LibreOffice already writes one bookmark per sheet, which is a good start. With pypdf and reportlab you can put a cover page in front, turn the flat list of sheet bookmarks into a nested tree, number the pages so the report starts at page 1 after the cover, and make the PDF open with the bookmarks panel showing. This page builds that pipeline and finishes with a test that every bookmark lands on the right page. It belongs to Exporting Excel Reports to PDF.

Prerequisites

pip install pypdf reportlab pandas openpyxl

board_pack.xlsx has a Summary sheet and four region sheets of different lengths, so some regions span more than one PDF page. LibreOffice (soffice) converts it; reportlab draws the cover page and pypdf assembles the final file.

Step 1 — Convert and read the sheet bookmarks

Convert the workbook as usual, then look at the outline LibreOffice wrote:

import subprocess
from pypdf import PdfReader

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "board_pack.xlsx"], check=True, capture_output=True)
report = PdfReader("board_pack.pdf")

sections = [(item.title, report.get_destination_page_number(item)) for item in report.outline]
print(len(report.pages), "pages")
for title, page in sections:
    print(f"  {title:8} starts on page {page + 1}")

LibreOffice adds a bookmark for each sheet, pointing at the first page that sheet prints on, and it sets the PDF to open with the bookmarks panel visible. Longer sheets take more pages, so the start pages are uneven — exactly the information a contents list needs. Reading the outline is more reliable than guessing page counts from row counts, because page breaks depend on fonts, margins and print scaling. If you set print areas or fit-to-page options, as in Fit an Excel Sheet to One Page in PDF, the outline reflects them automatically.

Step 2 — Draw a cover page with a contents list

reportlab draws a PDF page directly. The cover holds a title, the date and a contents list whose page numbers come from Step 1. Since the cover is added in front and the report's own numbering will start after it, the numbers printed are the report page numbers:

from datetime import date
from reportlab.lib.colors import HexColor
from reportlab.lib.pagesizes import letter
from reportlab.pdfgen import canvas

def draw_cover(path, title, subtitle, contents):
    c = canvas.Canvas(path, pagesize=letter)
    width, height = letter
    c.setFillColor(HexColor("#4338CA"))
    c.rect(0, height - 220, width, 220, stroke=0, fill=1)
    c.setFillColor(HexColor("#FFFFFF"))
    c.setFont("Helvetica-Bold", 28)
    c.drawString(60, height - 120, title)
    c.setFont("Helvetica", 14)
    c.drawString(60, height - 150, subtitle)
    c.setFillColor(HexColor("#172033"))
    c.setFont("Helvetica-Bold", 14)
    c.drawString(60, height - 290, "Contents")
    c.setFont("Helvetica", 12)
    y = height - 320
    for name, page in contents:
        c.drawString(72, y, name)
        c.drawRightString(width - 72, y, str(page))
        c.setDash(1, 3)
        c.line(72 + c.stringWidth(name, "Helvetica", 12) + 6, y + 3, width - 84 - c.stringWidth(str(page), "Helvetica", 12), y + 3)
        c.setDash()
        y -= 22
    c.showPage()
    c.save()

contents = [(title, page + 1) for title, page in sections]
draw_cover("cover.pdf", "Quarterly Board Pack", f"Prepared {date(2026, 10, 2):%d %B %Y}", contents)
print("cover with", len(contents), "contents lines")

The cover uses the same page size as the report (US Letter here, which is what this LibreOffice installation printed; use A4 from reportlab.lib.pagesizes if your sheets print on A4). Dotted leaders between each name and its page number are drawn with a dashed line. A logo can be added with c.drawImage(). Keep the cover simple and data-driven: the title, the reporting period and the preparation date are passed in as arguments, so a monthly job produces the right cover without anyone editing a template, and the contents list can never disagree with the bookmarks because both come from the same outline.

Step 3 — Assemble with a nested bookmark tree

Joining the cover and the report shifts every page by one, so the sheet bookmarks must be rebuilt rather than copied. Building them afresh is also the chance to group them — here the region sheets go under a "Regions" parent:

from pypdf import PdfWriter

writer = PdfWriter()
writer.append("cover.pdf")
writer.append("board_pack.pdf", import_outline=False)
offset = 1                                            # pages before the report

writer.add_outline_item("Cover", 0)
regions_parent = None
for title, page in sections:
    if title == "Summary":
        writer.add_outline_item("Summary", page + offset)
    else:
        if regions_parent is None:
            regions_parent = writer.add_outline_item("Regions", page + offset)
        writer.add_outline_item(title, page + offset, parent=regions_parent)

writer.set_page_label(0, 0, style="/r")               # cover shows as "i"
writer.set_page_label(1, len(writer.pages) - 1, style="/D", start=1)
writer.page_mode = "/UseOutlines"
writer.add_metadata({"/Title": "Quarterly Board Pack", "/Author": "Finance"})
writer.write("board_pack_final.pdf")
print(len(writer.pages), "pages written")

import_outline=False stops pypdf copying the old bookmarks, which would point at the wrong pages after the shift. add_outline_item(title, page, parent=…) returns the new item, so it can be the parent of later ones; nesting can go as deep as the report needs. Page labels change what the viewer's page box shows: the cover reads "i" and the first report page reads "1", which matches the numbers printed on the cover. page_mode makes the bookmarks panel open with the file, and the metadata title appears in the viewer's title bar instead of the file name.

Step 4 — Test the finished PDF

A short check confirms the structure — that each bookmark lands on the page whose text belongs to its sheet, and that the labels and page mode are set:

final = PdfReader("board_pack_final.pdf")

def walk(items, depth=0):
    for item in items:
        if isinstance(item, list):
            yield from walk(item, depth + 1)
        else:
            yield depth, item.title, final.get_destination_page_number(item)

for depth, title, page in walk(final.outline):
    label = final.page_labels[page]
    lines = final.pages[page].extract_text().split("\n")
    print(f"{'  ' * depth}{title:8} → page {label:>2}  first row: {lines[1][:22] if len(lines) > 1 else lines[0]}")
print("opens with:", final.page_mode)

Each region bookmark lands on a page whose first data row belongs to that region — order N-001 for North, S-001 for South and so on — and the labels run from "i" on the cover to "1" on the summary. The checks are cheap enough to run on every generated pack, and they catch the usual mistakes: an off-by-one after adding a cover, or bookmarks copied from the original file.

Variations

• One PDF per sheet, then join. If sheets come from separate conversions — see Convert Only Selected Sheets of a Workbook to PDF — append each file with writer.append(path, outline_item=name), which adds a bookmark for it at the right page as it goes.
• A cover sheet in the workbook. Instead of reportlab, put a styled cover sheet first in the workbook. The contents numbers then have to be filled in after a first conversion, so the reportlab route is simpler.
• Page numbers in the footer. Footer numbers come from the sheet's page setup; see Add Headers, Footers and Page Numbers to an Excel PDF. They count from the sheet, not the pack, so keep them consistent with the labels.

Joining several exported files without a cover is covered in Merge Excel PDF Exports into One File. If the pack must be locked, encrypt it as the very last step, after bookmarks and labels are in place.

Common pitfalls

• Copying the old bookmarks after adding a cover. They point one page early; pass import_outline=False and rebuild.
• Guessing start pages from row counts. Page breaks depend on layout; read LibreOffice's outline.
• Cover numbered page 1. Set page labels so the report starts at 1.
• A cover in a different page size. Match the report's paper size.
• Encrypting before adding bookmarks. Lock the file last.

Conclusion

LibreOffice's PDF export already writes a bookmark per sheet, which gives you each sheet's start page. Draw a cover with reportlab using those numbers, join it in front with pypdf while skipping the old outline, rebuild a nested bookmark tree with add_outline_item, set page labels so the cover is "i" and the report starts at 1, and open the file with the bookmarks panel. A test that walks the outline and checks each target page keeps the pack correct as sheets grow.

Frequently asked questions

Does LibreOffice add bookmarks when converting Excel to PDF? Yes. In our test each sheet became a bookmark pointing at its first page, and the PDF was set to open with the bookmarks panel. pypdf reads them from the reader's outline.

How do I add bookmarks to a PDF with Python? Use pypdf's PdfWriter.add_outline_item with a title and page index. It returns the item, which can be passed as parent to nest further bookmarks underneath it.

How do I add a cover page to a PDF with Python? Draw the cover with reportlab, then create a pypdf PdfWriter, append the cover first and the report after it, and rebuild the bookmarks because every page has moved by one.

How do I stop the cover page counting as page 1? Set page labels with set_page_label: roman numerals for the cover and decimal numbers starting at 1 for the first report page.

Related

• Up one level: Exporting Excel Reports to PDF — turning workbooks into PDFs.
• Convert Excel to PDF on Linux with LibreOffice — the conversion step.
• Password-Protect a PDF Exported from Excel — locking the finished pack.
• Building Multi-Sheet Excel Dashboards — the workbook behind a board pack.