Bulk Create QR Codes in Excel with Python

This tutorial shows you how to insert QR codes into Microsoft Excel with Python. We’ll start by writing a few lines of Python code that you could run from a Jupyter notebook or as a traditional Python script before we’ll see how we can generate QR codes at the click of a button via the xlwings add-in. To conclude, we’ll build a user-defined function (UDF) in Excel to insert QR codes.

Inserting QR codes into Excel can be useful for many things, for example, if you are generating reports from a template and want to dynamically insert a QR code with a URL where your readers will find more detailed information. Note that even though this tutorial uses QR codes for URLs, you can use them to represent any sort of information such as an ID or a phone number.

Supported OS: Windows, macOS
Python Packages: xlwings, segno

Table of Contents

1. Prerequisites
2. Run a Python Script to insert QR codes in Microsoft Excel
3. Formatting the QR Code
4. Generating QR Codes at the Click of a Button
5. Writing a user-defined function (UDF) to generate a QR Code (Windows only)
6. Conclusion

Prerequisites

You need to have a Python installation with the following packages installed:

• xlwings (to manipulate Excel with Python)
• segno (to generate the QR codes)

If you are new to Python, I would recommend you to install the Anaconda Individual Edition as it comes with xlwings preinstalled, then open an Anaconda Prompt and install segno like this:

pip install segno

Run a Python Script to insert QR codes in Microsoft Excel

Start by creating a workbook with a list of URLs like so:

Now run the following Python code (as a script or from a Jupyter notebook):

import tempfile
import segno
import xlwings as xw


# Update this with the name of your workbook
book = xw.Book('qr.xlsx')
sheet = xw.sheets[0]

# Update this with the starting cell of your URLs
start_cell = sheet['A1']
urls = start_cell.options(expand='down', ndim=1).value

# Loop through each URL and generate the QR code
for ix, url in enumerate(urls):
    # Generate the QR code
    qr = segno.make(url)
    with tempfile.TemporaryDirectory() as td:
        # Save the QR code as a temporary svg file. If you are on macOS, use pdf
        # instead and if you don't have Microsoft 365, you may have to use png
        filepath = f'{td}/qr.svg'
        qr.save(filepath, scale=5, border=0, finder_dark='#15a43a')
        # Insert the QR code to the right of the URL
        destination_cell = start_cell.offset(row_offset=ix, column_offset=1)
        sheet.pictures.add(filepath,
                           left=destination_cell.left,
                           top=destination_cell.top)

After running the script, your spreadsheet will have the QR codes embedded like this:

Formatting the QR Code

You probably noticed that the QR code’s finder patterns (the three big squares) have the same green color as the xlwings logo. It’s a detail, but I find it makes the QR codes a lot less boring, and makes them integrate smoothly with your corporate design. You achieve this by providing the finder_dark argument in the qr.save method. By leaving it away, you will end up with a QR code all in black. Segno allows you to customize the appearance of your QR code with many settings, see the segno docs for more details. In the example, I am using additionally border and scale, feel free to change them to suit your needs.

The script uses svg as the file extension in the qr.save command. This pastes the picture as vector graphics into Excel, which will guarantee the best possible quality for printing. You can also resize the QR without any loss in quality. However, on Windows, svg is only supported by the Excel version that comes with the Microsoft 365 subscription. If your version of Excel doesn’t support svg, replace it with png instead. Note that when you run this on macOS, you need to use png or pdf instead of svg (pdf doesn’t support the finder_dark argument though).

Generating QR Codes at the Click of a Button

The previous code sample is simple enough and can be run from a Jupyter notebook or via Python script without the need to install the xlwings add-in. However, if you need to create QR codes regularly, you may want to have something easier to handle.

• Start by installing the xlwings add-in as described in the docs.

• On a Command Prompt or Anaconda Prompt (Windows) or in a Terminal (macOS), run the following command:

    xlwings quickstart qr
  

The quickstart command will create a folder qr with an Excel file called qr.xlsm and a Python script called qr.py in the current working directory. Open the Python file in an editor, e.g. in VS Code, and replace the sample code that’s in there with the following code (a slightly modified version from the above):

import tempfile
import segno
import xlwings as xw


def main():
    book = xw.Book.caller()
    sheet = xw.sheets[0]
    
    # Start at the selected cell
    start_cell = book.app.selection
    if start_cell.value is None:
        raise TypeError('Please select a cell with a value!')
    urls = start_cell.options(expand='down', ndim=1).value
    
    # Loop through each URL and generate the QR code
    for ix, url in enumerate(urls):
        # Generate the QR code
        qr = segno.make(url)
        with tempfile.TemporaryDirectory() as td:
            # Save the QR code as a temporary svg file. If you are on macOS, use pdf
            # instead and if you don't have Microsoft 365, you may have to use png
            filepath = f'{td}/qr.svg'
            qr.save(filepath, scale=5, border=0, finder_dark='#15a43a')
            # Insert the QR code to the right of the URL
            destination_cell = start_cell.offset(row_offset=ix, column_offset=1)
            sheet.pictures.add(filepath,
                               left=destination_cell.left,
                               top=destination_cell.top)

Now fill in a few URLs again in the first sheet of qr.xlsm and click the Run main button in the xlwings Excel add-in. Unlike the previous version that used a hard-coded start_cell, this version reads the list from wherever you place your cursor. Instead of clicking the Run main button in the xlwings add-in, you could also insert a Form button on the sheet and assign it to the SampleCall macro, see RunPython for more details on this.

Writing a user-defined function (UDF) to generate a QR Code (Windows only)

To generate QR codes via UDFs, add the following function to the qr.py file created in the previous step (note that UDFs are only supported on Windows):

import segno
import tempfile
import xlwings as xw


@xw.func
def qr(content, name, caller):
    # 'caller' is the xlwings range object from where the UDF is being called
    # and isn't exposed in Excel
    qr = segno.make(content)
    with tempfile.TemporaryDirectory() as td:
        filepath = f'{td}/qr.svg'
        qr.save(filepath, scale=5, border=0, finder_dark='#15a43a')
        caller.sheet.pictures.add(filepath,
                                  left=caller.offset(column_offset=1).left,
                                  top=caller.top,
                                  name=name, update=True)
        return f'<{name}>'

Now, click the Import UDFs button in the xlwings add-in, then set your spreadsheet up as shown on the following screenshot with a name in the first column, a URL in the second column, and the formula in the third column:

This will dynamically update the QR code whenever you change the URL in column B.

Conclusion

This tutorial presented different possibilities of how you can programmatically insert QR codes into your Excel spreadsheets. Except for the last option (UDFs), everything works on Windows and macOS.