Generates bulk bin QR codes from an Excel file

make_bulk_qrs.py

################
# One-time setup
#
# 1. Install the latest version of Python 3 from the world wide web
# 2. open a terminal and run: pip install pandas qrcode openpyxl
#     if you get an error, try: pip3 install pandas qrcode openpyxl
#                           or: python3 -m pip install pandas qrcode openpyxl
#                           or: python -m pip install pandas qrcode openpyxl
#
# If all of these fail, and the commands `python3` `python` and `py` fail,
# Python probably wasn't installed correctly or wasn't added to the path.
#
# This second step installs a few libraries that aren't built into 
# Python that this script uses to read excel sheets and make QR codes

#################
# Making QR codes
#
# 1. Note the path to your input xlsx relative to make_bulk_qrs.py. 
#       If it's in the same directory, then this is just the filename.
# 2. Open a terminal in the directory that has make_bulk_qrs.py
# 3. run: python3 make_bulk_qrs.py "path/to/your/bulk/spreadsheet.xlsx"
#
# You can also change the `filename = "Bulk Book Simple.xlsx"`
# code below to specify the default path to your bulk sheet.
#
# If the file wasn't found, the script will try to find the first .xlsx
# file in the same directory where it was executed and use that.
#
# The input xlsx format is two columns: the first is a 
# description of the product, the second is its number
#
# Note: if `python3` fails in any of the above 
# commands, try `python` (without backticks) or `py`.

#################################################
# Run by double-clicking without the command line
#
# It's also possible to use a script to execute this with double clicking.
#
# Make a file in the same directory as the .py file called 
# make_bulk_qrs.command with the following contents (without the leading `#`s):
#
#   #! /usr/bin/env bash
#   SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" &> /dev/null && pwd )"
#   cd $SCRIPT_DIR
#   python3 make_bulk_qrs.py
#
# Then, you'll need to give it execute permissions one time:
# Open a terminal where the .command file is and run `chmod +x *.command`
# (without the backticks). Then you can double-click that .command file to make qrs.
# It will look for the first .xlsx file in the current directory.


import os
import pandas as pd
import qrcode
import re
import sys
from datetime import datetime

# use a default filename or check whether an
# argument was provided to the script, e.g.
# python3 make_bulk_qrs.py "some/path/to/your/bulk/spreadsheet.xlsx"
default_filename = "Bulk Book Simple.xlsx"
filename = default_filename if len(sys.argv) < 2 else sys.argv[1]

if not os.path.isfile(filename):
    filename = next(
        f for f in os.listdir(".") 
        if os.path.isfile(f) and f.endswith("xlsx")
    )

# read the excel sheet, drop rows with missing 
# data and make number strings into integers
df = pd.read_excel(filename, engine="openpyxl").dropna()
df.iloc[:, 1] = df.iloc[:, 1].astype(int)

# create an output folder name with the current timestamp
current_date = str(datetime.now()).split(".")[0].replace(" ", "__")
timestamp = re.sub(r"[: ]", "_", current_date)
output_path = f"bulk_qr_codes__{timestamp}"

# make the output directory if it doesn't exist
if not os.path.exists(output_path):
    os.makedirs(output_path)

# loop over the rows in the data frame
for _, (description, itemno) in df.iterrows():

    # make a QR code; feel free to change box_size and border values
    qr = qrcode.QRCode(
        version=1,
        error_correction=qrcode.constants.ERROR_CORRECT_L,
        box_size=5,
        border=1,
    )

    # format the QR content text
    qr.add_data(f"{itemno} {description}")

    # replace some bad characters with underscores and write the image to file 
    img = qr.make_image(fill_color="black", back_color="white")
    sanitized_description = re.sub(r"[^ \w.,;'%$&\]\[)(]", "_", description)
    path = os.path.join(output_path, f"{sanitized_description}_{itemno}.png")
    img.save(path, "PNG")