Python 自动生成二维码并填充到 Excel 单元格

最近工作中遇到一个需求，需要为某个酒店的全部房间生成二维码，手机扫二维码就显示对应的房间信息。

我的解决办法：

导出数据

所有的房间号从数据导出生成 rooms.xlsx ，第一个列是房号，第二列是二维码。

读取房号

wb = load_workbook("rooms.xlsx")
sheet = wb.active
rowNum = sheet.max_row
colNum = sheet.max_column
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# Calculated number of cells width or height from cm into EMUs

for i in range(2, rowNum + 1):
    if i > 0:
        for j in range(1, colNum):
            roomName = sheet.cell(row=i, column=j).value
            imageName = generateQrCode(roomName)

生成二维码

def generateQrCode(roomName):
    if roomName:
        img = qrcode.make(roomName)
        img_name = "./qrcode/" + roomName + ".png"
        with open(img_name, 'wb') as f:
            img.save(f)
        return img_name

把二维码填充到单元格

img = Image(imageName)
newsize = (90, 90)
img.width, img.height = newsize
column = 1
coloffset = cellw(0.05)
row = i - 1
rowoffset = cellh(0.5)
h, w = img.height, img.width
size = XDRPositiveSize2D(p2e(h), p2e(w))
marker = AnchorMarker(
    col=column, colOff=coloffset, row=row, rowOff=rowoffset)
img.anchor = OneCellAnchor(_from=marker, ext=size)
sheet.add_image(img)

依赖 openpyxl 和 qrcode

完整代码可以看 Demo