Generate QR Codes and Barcodes in Excel with Python

Asset registers, stock lists, event badges and equipment labels often need a code people can scan: a QR code that opens the asset's page, or a barcode a handheld scanner reads into the stock system. Excel has no built-in barcode generator, and barcode fonts are fiddly — they need the right font installed on every machine and check characters calculated by hand. Generating the codes as images in Python and placing one in each row avoids both problems. The part that is easy to skip, and should not be, is checking that the codes still scan after Excel scales them and a printer prints them. This page is part of Inserting Images and Logos into Excel.

Prerequisites

pip install openpyxl pandas qrcode pillow python-barcode zxing-cpp

qrcode makes QR codes, python-barcode makes linear barcodes such as Code 128, and Pillow renders both as images. zxing-cpp is only used for checking: it reads codes back from an image. assets.xlsx lists six assets with an ID, description and location.

Step 1 — Make a QR code in memory

Build the QR code with an explicit error-correction level and a small module size, and keep it in memory — there is no reason to write hundreds of PNG files to disk:

import io
import qrcode
import zxingcpp

def qr_png(text, box=4):
    q = qrcode.QRCode(error_correction=qrcode.constants.ERROR_CORRECT_M, box_size=box, border=2)
    q.add_data(text)
    q.make(fit=True)
    img = q.make_image(fill_color="black", back_color="white").convert("RGB")
    buf = io.BytesIO()
    img.save(buf, format="PNG")
    return buf, img

buf, img = qr_png("https://assets.example.com/AS-10442")
print(img.size, [r.text for r in zxingcpp.read_barcodes(img)])

box_size is the number of pixels per QR module and border the quiet zone in modules; the specification asks for four, but two is enough when the code sits in a white cell with space around it. Error-correction level M recovers from about 15% damage, a good default for printed labels. Decoding the image straight away with zxingcpp.read_barcodes confirms the content before anything else happens — a URL with a typo scans perfectly and goes nowhere, so read the text back, not just the fact that something decoded.

Step 2 — Make a Code 128 barcode

Linear barcodes are less forgiving than QR codes. The two settings that decide whether a scanner reads one are the width of the thinnest bar and the blank quiet zone at each end:

import barcode
from barcode.writer import ImageWriter
from PIL import Image

def code128_png(text, dpi=300, module_mm=0.254, quiet_mm=6.5):
    buf = io.BytesIO()
    barcode.get("code128", text, writer=ImageWriter()).write(
        buf, options={"module_width": module_mm, "module_height": 9.0, "quiet_zone": quiet_mm,
                      "font_size": 7, "text_distance": 3.0, "dpi": dpi})
    return buf, Image.open(io.BytesIO(buf.getvalue()))

for dpi, module in [(200, 0.2), (300, 0.254)]:
    _, bc = code128_png("AS-10442", dpi=dpi, module_mm=module)
    px = module / 25.4 * dpi
    print(f"{dpi} dpi, {module} mm bars = {px:.2f} px:", bc.size, [r.text for r in zxingcpp.read_barcodes(bc)])

The library's defaults — 0.2 mm bars — at 200 dpi make each bar 1.57 pixels wide, so bar edges fall between pixels, get smeared into grey, and the reader finds nothing at all. A module_width of 0.254 mm at 300 dpi is exactly three pixels per bar, every edge lands on a pixel boundary, and the same text decodes. In tests with a clean image, the reader tolerated a trimmed quiet zone — the blank margin at each end — but printed labels sit next to borders, text and other codes, which is exactly what the quiet zone protects against, so keep the default 6.5 mm and make the column wider instead. Code 128 is the usual choice for internal IDs because it encodes letters, digits and punctuation compactly; use EAN-13 only for retail product numbers.

Step 3 — Anchor one of each in every row

Load the asset list, add two columns, and place the images with openpyxl, each anchored to its row's cell:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as XLImage
from openpyxl.styles import Alignment

assets = pd.read_excel("assets.xlsx")
wb = load_workbook("assets.xlsx")
ws = wb.active
ws["D1"], ws["E1"] = "QR", "Barcode"
for r, asset_id in enumerate(assets["AssetID"], start=2):
    qbuf, _ = qr_png(f"https://assets.example.com/{asset_id}")
    q = XLImage(qbuf)
    q.width = q.height = 90
    ws.add_image(q, f"D{r}")
    bbuf, bimg = code128_png(asset_id)
    b = XLImage(bbuf)
    b.width, b.height = 260, round(260 * bimg.height / bimg.width)
    ws.add_image(b, f"E{r}")
    ws.row_dimensions[r].height = 72
    for c in ws[r][:3]:
        c.alignment = Alignment(vertical="center")
ws.column_dimensions["B"].width = 18
ws.column_dimensions["D"].width = 14
ws.column_dimensions["E"].width = 38
wb.save("assets_labels.xlsx")
print(len(ws._images), "images placed")

openpyxl's Image accepts a file-like object, so the BytesIO buffer goes straight in. width and height are the displayed size in pixels at 96 per inch; the barcode keeps its aspect ratio so the bars are scaled evenly. A row height of 72 points (96 pixels) gives the 90-pixel QR code a little space. Each image is anchored at its cell's top-left corner, so sorting or filtering the rows in Excel afterwards does not move the images with them — generate the file in its final order. Resize and Position Images in Excel with openpyxl covers anchoring in more detail.

Step 4 — Prove the codes scan after rendering

The codes decoded as standalone images, but what matters is the printed page: Excel scales the images to the displayed size, and printing resamples them again. Render the sheet to PDF, rasterise it at 300 dpi — printer resolution — and decode everything on the page:

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf assets_labels.xlsx > /dev/null 2>&1
pdftoppm -png -r 300 -f 1 -l 1 assets_labels.pdf page && ls page*

page = Image.open("page-1.png")
found = sorted({(r.format.name, r.text) for r in zxingcpp.read_barcodes(page)})
qr = {t.rsplit("/", 1)[-1] for f, t in found if f == "QRCode"}
bars = {t for f, t in found if f == "Code128"}
expected = set(assets["AssetID"])
print(f"QR codes read: {len(qr)}/{len(expected)}, barcodes read: {len(bars)}/{len(expected)}")
print("missing:", sorted(expected - qr), sorted(expected - bars))

Every QR code and every barcode on the rendered page decodes to the right ID. Running this check in the script that generates the labels turns "the barcodes looked fine" into a tested fact, and it catches the real failures — a column made narrower so the barcode is squeezed, a smaller image size that loses the bars' pixel alignment, a changed URL prefix.

Performance notes

Generating a QR code and a barcode takes a few milliseconds each, and openpyxl stores each image once in the workbook's media folder, so a sheet of a thousand assets is a few seconds' work and a few megabytes of file. The decode check is the slow step — reading a 300 dpi page takes a noticeable fraction of a second — so for long lists render and check a sample of pages, or check every page in a nightly run rather than on every generation. If the file grows too large to email, lower the QR box_size or the barcode dpi together with the display size, keeping the bars on whole pixels.

Common pitfalls

• Fractional bar widths. Bars 1.57 pixels wide did not decode at all; choose module widths that are a whole number of pixels at the output resolution.
• Trimming the quiet zone. The blank margins are part of the barcode; on a printed label they keep neighbouring lines and text from being read as bars.
• Squashing the image. Scale barcodes with their aspect ratio, never width alone.
• Sorting after inserting. Images are anchored to positions, not to rows' data; sort before generating.
• Trusting that it decodes. Check the decoded text, not just that something was found.

Conclusion

Generate each code in memory — qrcode with error correction M and a whole-pixel module size, python-barcode's Code 128 with whole-pixel bars and the default quiet zone — and anchor it in its row with openpyxl's Image, scaled with its aspect ratio into a row tall enough to hold it. Then render the sheet at printer resolution and decode every code, comparing the text with the expected IDs, so the labels are known to scan before anyone prints them.

Frequently asked questions

How do I add a QR code to an Excel cell with Python? Generate the QR code with the qrcode library, save it to a BytesIO buffer as PNG, create an openpyxl Image from the buffer, set its width and height, and add it with ws.add_image(img, "D2"). Make the row tall enough to hold it.

How do I generate a barcode in Excel with Python? Use python-barcode with the ImageWriter to create a Code 128 PNG in memory and insert it with openpyxl. Barcode fonts are an alternative, but they depend on the font being installed and on check characters being calculated correctly.

Why won't my barcode scan? The usual causes are bar widths that are not a whole number of pixels, bars squeezed by scaling the image unevenly, or a trimmed quiet zone. Choose module widths that match the output resolution, scale with the aspect ratio and keep the default quiet zone.

How can I test that the codes are readable? Render the workbook to PDF, rasterise it at 300 dpi with pdftoppm, and decode the image with zxing-cpp. Compare the decoded text with the expected IDs.

Related

• Up one level: Inserting Images and Logos into Excel — pictures in and out of workbooks.
• Insert an Image from Memory into Excel — the BytesIO technique in detail.
• Build a Product Catalog with Images in Excel — one picture per row at larger scale.
• Generate One PDF per Excel Row — turning rows into printable labels.