Print Mailing Labels from an Excel Address List

Turn an Excel address list into a print-ready PDF of mailing labels with pandas and reportlab: describe a label sheet such as Avery L7160 or 5160 by its measurements, tidy addresses by dropping blank lines, shrink long lines to fit, start on a partly used sheet, print an alignment test, and check every address landed inside its label.

Report layouts & templates PDF export

Word's mail merge will print labels from a spreadsheet, but it is a manual, click-through job that nobody wants to repeat every month — and it falls over on the details: empty "Address 2" cells leave gaps, long company names spill past the label edge, and a half-used sheet of labels gets wasted. A short Python script reads the address list with pandas and draws the labels with reportlab, positioned to the millimetre for the label stock in the printer. This page describes a label sheet by its measurements, cleans each address into tidy lines, shrinks text that would overflow, starts printing part-way down a used sheet, and adds an alignment test and a placement check. It belongs to Generating Excel Reports from Templates.

Prerequisites

Bash

pip install pandas openpyxl reportlab pypdf

addresses.xlsx holds seven contacts with name, optional company, two address lines, city, postcode and country — with the usual gaps: no company for some, no second address line for most. The labels are sent from the United Kingdom, so the home country is left off UK addresses.

Step 1 — Describe the label sheet

Every label product is a grid defined by a handful of measurements printed on the box or the manufacturer's template: page size, label size, the number of columns and rows, the top and left margins, and the pitch — the distance from the start of one label to the start of the next, which includes any gap:

Python

from dataclasses import dataclass
from reportlab.lib.pagesizes import A4, letter
from reportlab.lib.units import inch, mm

@dataclass(frozen=True)
class LabelSheet:
    page: tuple
    cols: int
    rows: int
    width: float
    height: float
    left: float
    top: float
    h_pitch: float
    v_pitch: float

    def origin(self, index):
        """Bottom-left corner of label number `index` (0-based, row by row)."""
        col, row = index % self.cols, index // self.cols
        x = self.left + col * self.h_pitch
        y = self.page[1] - self.top - row * self.v_pitch - self.height
        return x, y

L7160 = LabelSheet(A4, cols=3, rows=7, width=63.5 * mm, height=38.1 * mm,
                   left=7.2 * mm, top=15.1 * mm, h_pitch=66.0 * mm, v_pitch=38.1 * mm)
AVERY_5160 = LabelSheet(letter, cols=3, rows=10, width=2.625 * inch, height=1 * inch,
                        left=0.1875 * inch, top=0.5 * inch, h_pitch=2.75 * inch, v_pitch=1 * inch)
print("L7160:", L7160.cols * L7160.rows, "labels per sheet; 5160:", AVERY_5160.cols * AVERY_5160.rows)

The two definitions cover the most common stock: Avery L7160 (21 labels on A4, used across Europe) and Avery 5160 (30 labels on US Letter). reportlab measures from the bottom-left corner of the page, while label templates measure from the top, so origin() converts. For any other product, copy its five or six numbers from the template; the rest of the code does not change.

Step 2 — Turn each row into address lines

A label should show only the lines that have content, in postal order. Building the lines in a function keeps that logic in one place:

Python

import pandas as pd

HOME = "United Kingdom"

def address_lines(row):
    lines = [row["Name"], row["Company"], row["Address 1"], row["Address 2"]]
    if row["Country"] == HOME:
        lines += [row["City"].upper(), row["Postcode"]]
    else:
        lines += [f'{row["Postcode"]} {row["City"]}'.strip(), row["Country"].upper()]
    return [str(v).strip() for v in lines if pd.notna(v) and str(v).strip()]

people = pd.read_excel("addresses.xlsx", dtype=str)
labels = [address_lines(r) for _, r in people.iterrows()]
for lines in labels[:3]:
    print(" / ".join(lines))

Reading with dtype=str keeps postcodes such as "69002" and "753 20" as text instead of numbers. Empty cells arrive as NaN and are dropped together with whitespace-only values, so a missing company or second address line leaves no gap. UK addresses end with the town in capitals and the postcode on its own line; foreign ones end with the postcode and city followed by the country in capitals, the usual convention for international post. Adapt the rule to your own country's postal guidance — this is the part most worth reviewing with whoever posts the letters. Columns with stray spaces or inconsistent capitals are easier to fix in pandas before this step, as in Strip Whitespace and Normalise Text Columns with pandas.

Step 3 — Draw the labels, shrinking long lines

Each label is drawn at its origin with a small inner padding. A line too long for the label gets a smaller font, down to a minimum, rather than running off the edge:

Python

from reportlab.pdfbase.pdfmetrics import stringWidth
from reportlab.pdfgen import canvas

FONT, SIZE, MIN_SIZE, PAD = "Helvetica", 10, 7, 4 * mm

def fit_size(text, max_width):
    size = SIZE
    while size > MIN_SIZE and stringWidth(text, FONT, size) > max_width:
        size -= 0.5
    return size

def draw_labels(path, sheet, labels, skip=0, outline=False):
    c = canvas.Canvas(path, pagesize=sheet.page)
    per_page = sheet.cols * sheet.rows
    for n, lines in enumerate(labels, start=skip):
        if n and n % per_page == 0:
            c.showPage()
        x, y = sheet.origin(n % per_page)
        if outline:
            c.setDash(2, 2)
            c.rect(x, y, sheet.width, sheet.height)
            c.setDash()
        line_h = SIZE * 1.2
        text_y = y + (sheet.height + len(lines) * line_h) / 2 - SIZE    # centre the block vertically
        for line in lines:
            c.setFont(FONT, fit_size(line, sheet.width - 2 * PAD))
            c.drawString(x + PAD, text_y, line)
            text_y -= line_h
    c.save()

draw_labels("labels.pdf", L7160, labels, skip=2)
print("labels.pdf written; first label drawn in position", 2)

skip=2 starts in the third position, leaving the first two labels of a partly used sheet untouched — the next run can use them. A new page starts every 21 labels. The block of lines is centred vertically in the label, which looks better than top-aligned text when addresses have different numbers of lines. Dev Patel's long company name, about 60 mm wide at 10 points against 55.5 mm of usable label width, is drawn at 9 points so that it fits; with a 7-point minimum, a line that still does not fit is better shortened in the source data than printed illegibly small.

Step 4 — Print an alignment test first

Printers shift and scale. Before using a sheet of labels, print the dashed outlines on plain paper, hold it against a label sheet up to the light, and adjust left and top if the boxes do not line up:

Python

draw_labels("alignment_test.pdf", L7160, [["Alignment test"]] * 21, outline=True)
print("21 outlined test labels")

Print at actual size — "Fit to page" or "Shrink oversized pages" in the print dialog scales the whole page by a few percent and moves every label off its mark, more so towards the bottom of the sheet. Once the outlines match, keep the corrected measurements in the LabelSheet definition for that printer.

Step 5 — Check every address landed inside its label

Text placement is easy to get subtly wrong, so verify it from the PDF: read where each line was drawn and confirm it falls inside a label rectangle:

Python

from pypdf import PdfReader

placed = []
def visitor(text, cm, tm, font_dict, font_size):
    if text.strip():
        placed.append((text.strip(), tm[4], tm[5]))

PdfReader("labels.pdf").pages[0].extract_text(visitor_text=visitor)

def inside_a_label(x, y, sheet):
    return any(ox + 2 <= x <= ox + sheet.width - 2 and oy <= y <= oy + sheet.height
               for ox, oy in (sheet.origin(i) for i in range(sheet.cols * sheet.rows)))

outside = [t for t, x, y in placed if not inside_a_label(x, y, L7160)]
widest = max(stringWidth(t, FONT, MIN_SIZE) for t, _, _ in placed)
print(len(placed), "lines drawn; outside a label:", outside)
print("every line fits the label width:", all(stringWidth(t, FONT, fit_size(t, L7160.width - 2 * PAD)) <= L7160.width - 2 * PAD
                                               for t, _, _ in placed), "| widest at minimum size:", round(widest / mm, 1), "mm")

pypdf's visitor_text callback reports each text run with its transformation matrix, whose last two values are the x and y position on the page. Every line started inside a label, and every line fits within the label's inner width at the size it was drawn. A check like this belongs in a test of the label job, so a change to the address rules or the sheet definition cannot quietly push text across a label edge.

Variations

Return labels or a sender line. Add a small first line in 6-point text with your return address above the recipient.
Labels sorted for posting. Sort the DataFrame by country and postcode before drawing so sacks of mail can be bundled.
Barcodes or QR codes. reportlab's barcode module (graphics.barcode) draws them inside the label next to the text.
One letter per contact instead of labels. Use the same address lines in a document template; see Generate Word Documents from Excel Rows.

Common pitfalls

Postcodes read as numbers. Read the file with dtype=str.
Gaps for empty columns. Drop blank and whitespace-only lines.
Printing with "Fit to page". Print at actual size or every label drifts.
Measuring from the top in reportlab. Its origin is the bottom-left; convert.
Wasting part-used sheets. Use a starting offset.

Conclusion

Printing mailing labels from Excel needs three pieces: a label-sheet definition taken from the product's measurements, a function that turns each row into clean address lines, and a reportlab loop that draws each label at its origin, shrinking long lines and starting part-way down a used sheet. Print an outlined alignment page at actual size before using real labels, and check from the PDF that every line landed inside its label.

Frequently asked questions

How do I print address labels from an Excel list with Python? Read the list with pandas, build the address lines for each row, and draw them with reportlab at positions computed from the label sheet's margins, label size and pitch. Print the resulting PDF at actual size.

What measurements do I need for a label sheet? Page size, label width and height, the number of columns and rows, the top and left margins, and the horizontal and vertical pitch. They are printed on the box or in the manufacturer's template.

How do I skip labels already used on a sheet? Start numbering at an offset, for example skip=2 to leave the first two positions empty, so printing begins on the first unused label.

Why are my labels slightly out of line? Usually the print dialog is scaling the page. Print at actual size, test with outlined labels on plain paper, and adjust the margins in the sheet definition if needed.

Up one level: Generating Excel Reports from Templates — documents built from spreadsheet data.
Generate Customer Account Statements from a Ledger — a document per customer from the same kind of list.
Add Bookmarks and a Cover Page to an Excel PDF Export — another reportlab-drawn page.
Send Each Recipient Their Own Excel Report — the email version of a mail merge.

More on report layouts & templates