Create a PDF Report from Excel Data with ReportLab

Converting a workbook to PDF with LibreOffice or Excel reproduces the sheet as it is — gridlines, column widths and all. Sometimes the PDF should instead be a document: a title and a few sentences of commentary, a chart, then a well-set table that reads like a printed report rather than a screenshot of a spreadsheet. ReportLab builds exactly that from data, in pure Python, with no office software on the server. This page reads an Excel file of orders, summarises it with pandas, and lays out a report with ReportLab's high-level platypus layer: styled paragraphs, a native vector bar chart, a table with right-aligned formatted numbers, text that wraps inside its cell, zebra stripes, bold subtotal rows, a header row repeated on every page and a "page X of Y" footer. A final check reads the PDF back to confirm the layout. It belongs to Exporting Excel Reports to PDF.

Prerequisites

pip install reportlab pandas openpyxl pypdf

ReportLab is the PDF engine; pandas reads and summarises the workbook; pypdf is used only for the checks. The parent guide shows the minimal version — a sheet dumped into a table. This page builds the version you would send to a client.

Step 1 — Read and summarise the workbook

import pandas as pd

orders = pd.read_excel("orders_q3.xlsx", sheet_name="Orders")
by_region = orders.groupby("Region", as_index=False).agg(Orders=("Order", "count"), Revenue=("Amount", "sum"))
by_region = by_region.sort_values("Revenue", ascending=False)
total = orders["Amount"].sum()
top = by_region.iloc[0]
print(len(orders), "orders;", f"total {total:,.2f}; top region {top.Region} ({top.Revenue / total:.0%})")

All the numbers in the report come from here. Computing them in pandas, not in the layout code, keeps the two concerns apart: the summary can be unit-tested, and the layout code only formats what it is given. The commentary sentence in the next step is generated from the same figures, so the words and the numbers can never disagree.

Step 2 — Title, commentary and a vector chart

Platypus builds a document from a list of flowables — paragraphs, spacers, tables, drawings — which it flows onto pages. Start with a title, a sentence generated from the figures and a bar chart drawn with ReportLab's own graphics, so it stays sharp at any zoom and adds almost nothing to the file size:

from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import mm
from reportlab.graphics.shapes import Drawing
from reportlab.graphics.charts.barcharts import VerticalBarChart
from reportlab.platypus import Paragraph, Spacer

styles = getSampleStyleSheet()
small = ParagraphStyle("small", parent=styles["BodyText"], fontSize=8, leading=10)

def revenue_chart(df, width=160 * mm, height=55 * mm):
    d = Drawing(width, height)
    chart = VerticalBarChart()
    chart.x, chart.y, chart.width, chart.height = 30, 20, width - 50, height - 35
    chart.data = [[round(v / 1000, 1) for v in df["Revenue"]]]
    chart.categoryAxis.categoryNames = list(df["Region"])
    chart.valueAxis.valueMin = 0
    chart.valueAxis.labelTextFormat = "%d k"
    chart.bars[0].fillColor = colors.HexColor("#1f6feb")
    chart.barLabelFormat, chart.barLabels.nudge = "%.1f k", 7
    d.add(chart)
    return d

story = [
    Paragraph("Orders report — Q3 2026", styles["Title"]),
    Paragraph(f"{len(orders)} orders worth {total:,.2f} were placed in the quarter. "
              f"{top.Region} contributed the most, {top.Revenue:,.0f} or {top.Revenue / total:.0%} of revenue.", styles["BodyText"]),
    Spacer(1, 4 * mm),
    revenue_chart(by_region),
    Spacer(1, 6 * mm),
]
print(len(story), "flowables so far")

VerticalBarChart takes its data as a list of series and draws axes, labels and bars as vector shapes. For charts ReportLab cannot draw — a scatter with a fitted line, say — render a matplotlib figure to an in-memory PNG or SVG and insert it as an Image; Embed a matplotlib Chart in an Excel Report shows rendering a figure to an in-memory image, which works the same way for ReportLab.

Step 3 — A table that reads like a report

The detail table is where most of the work is. Long product names must wrap rather than overflow, numbers must be formatted and right-aligned, each region needs a subtotal, and the header must repeat on every page:

from reportlab.platypus import Table, TableStyle

def detail_table(df):
    header = ["Order", "Region", "Product", "Qty", "Amount"]
    data, subtotal_rows = [header], []
    for region, grp in df.sort_values(["Region", "Order"]).groupby("Region"):
        for r in grp.itertuples(index=False):
            data.append([r.Order, r.Region, Paragraph(r.Product, small), f"{r.Qty:,}", f"{r.Amount:,.2f}"])
        data.append(["", f"{region} subtotal", "", f"{grp['Qty'].sum():,}", f"{grp['Amount'].sum():,.2f}"])
        subtotal_rows.append(len(data) - 1)
    data.append(["", "Total", "", f"{df['Qty'].sum():,}", f"{df['Amount'].sum():,.2f}"])
    style = [
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#24292f")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("FONTSIZE", (0, 0), (-1, -1), 8),
        ("ALIGN", (3, 0), (-1, -1), "RIGHT"),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("LINEBELOW", (0, 0), (-1, 0), 0.8, colors.black),
        ("ROWBACKGROUNDS", (0, 1), (-1, -2), [colors.white, colors.HexColor("#f6f8fa")]),
        ("FONTNAME", (0, -1), (-1, -1), "Helvetica-Bold"),
        ("LINEABOVE", (0, -1), (-1, -1), 0.8, colors.black),
    ]
    for r in subtotal_rows:
        style += [("FONTNAME", (0, r), (-1, r), "Helvetica-Bold"), ("BACKGROUND", (0, r), (-1, r), colors.HexColor("#ddf4ff"))]
    widths = [22 * mm, 30 * mm, 70 * mm, 16 * mm, 26 * mm]
    return Table(data, colWidths=widths, repeatRows=1, style=TableStyle(style))

story.append(detail_table(orders))

Wrapping a cell's text in a Paragraph is what makes it wrap: a plain string in a table cell is drawn on one line, however long. Fixed colWidths that add up to the page's usable width (A4 is 210 mm; with 20 mm margins that leaves 170 mm, and these columns use 164 mm) stop the table from running off the page. Numbers are formatted as strings before they go into the table, which is where thousands separators and two decimals are decided; ALIGN RIGHT on the numeric columns lines their decimal points up. repeatRows=1 repeats the header row after every page break, and ROWBACKGROUNDS alternates the stripe colours, which the subtotal rows then override with their own background.

Step 4 — "Page X of Y" and building the document

ReportLab knows the current page number while drawing, but not the total until the document is finished. The usual solution is a canvas subclass that holds each page back until the end, then stamps "page X of Y" on all of them:

from reportlab.pdfgen import canvas
from reportlab.platypus import SimpleDocTemplate

class NumberedCanvas(canvas.Canvas):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self._pages = []

    def showPage(self):
        self._pages.append(dict(self.__dict__))
        self._startPage()

    def save(self):
        for state in self._pages:
            self.__dict__.update(state)
            self.setFont("Helvetica", 7)
            self.drawRightString(190 * mm, 10 * mm, f"Page {self._pageNumber} of {len(self._pages)}")
            self.drawString(20 * mm, 10 * mm, "Orders report — Q3 2026 — generated from orders_q3.xlsx")
            super().showPage()
        super().save()

doc = SimpleDocTemplate("orders_q3.pdf", pagesize=A4, leftMargin=20 * mm, rightMargin=20 * mm,
                        topMargin=18 * mm, bottomMargin=18 * mm, title="Orders report — Q3 2026", author="Sales operations")
doc.build(story, canvasmaker=NumberedCanvas)

showPage normally writes a page out immediately; the subclass saves the page's state instead, and save replays every page with the footer added now that the total is known. Passing title and author to the document template sets the PDF's metadata, which is what a PDF viewer shows as the window title and what a document library indexes. The same footer approach works for a confidentiality notice or a run timestamp.

Step 5 — Check the PDF

Read the PDF back and check the things a person would look for — page count, the header on every page, the footer numbering and the total:

import re
from pypdf import PdfReader

reader = PdfReader("orders_q3.pdf")
pages = [p.extract_text() for p in reader.pages]
print("pages:", len(pages), "| title:", reader.metadata.title)
print("header on every page:", all("Order\nRegion\nProduct\nQty\nAmount" in t for t in pages))
print("footers:", [re.search(r"Page \d+ of \d+", t).group() for t in pages])
print("total in PDF matches workbook:", f"{total:,.2f}" in pages[-1])

pypdf extracts each table cell on its own line, which is why the header check looks for the five column names separated by line breaks. The report runs to five pages; every page carries the repeated header row; the footers count through to the last page; and the grand total printed on the last page matches the figure computed from the workbook. Checks like these, run in the job before the PDF is sent, catch a broken layout — a table that lost its header, a total that disagrees with the source — before a reader does. Sending the result is covered in Send an Excel Report to Multiple Recipients in Python.

Common pitfalls

• Long text in plain table cells. Wrap it in a Paragraph or it will not wrap.
• No column widths. Set colWidths that fit the page's usable width.
• Header missing after page one. Pass repeatRows=1.
• Numbers left-aligned. Format as strings and align numeric columns right.
• "Page X" without "of Y". Use a canvas subclass that stamps footers on save.

Conclusion

To create a designed PDF report from Excel data, read and summarise the workbook with pandas, then build a platypus story: a title, commentary generated from the figures, a vector chart, and a table with Paragraph cells for wrapping text, right-aligned formatted numbers, zebra stripes, bold subtotal rows and repeatRows=1. Build it with a canvas subclass for "page X of Y" footers and check the result with pypdf before it is sent.

Frequently asked questions

How do I make text wrap in a ReportLab table cell? Put the text in a Paragraph object rather than a plain string; Paragraphs wrap to the column width, plain strings do not.

How do I repeat a table's header row on every page in ReportLab? Create the Table with repeatRows=1, or a higher number for multi-row headers, and add it to a platypus document.

How do I add page X of Y to a ReportLab PDF? Use a canvas subclass that stores each page in showPage and draws the footer for every stored page in save, when the total is known.

Can ReportLab draw charts? Yes. reportlab.graphics.charts includes bar, line and pie charts drawn as vector graphics; for other chart types insert a matplotlib image.

Related

• Up one level: Exporting Excel Reports to PDF — PDFs from Excel in Python.
• Generate One PDF per Excel Row — many small documents instead of one report.
• Convert an Excel File to PDF with Python — reproducing the sheet instead.
• Add Bookmarks and a Cover Page to an Excel PDF Export — navigation in longer PDFs.