如何读取 Excel 文件，创建二维码？

Question

我正在尝试使用此代码from openpyxl import load_workbookimport qrcodewb = load_workbook("D:\QR\qrcodes.xlsx")  ws = wb.['Sheet1']  column = ws['A']  # Columndata = [column[x].value for x in range(len(column))]print(data)qr = qrcode.QRCode(version = 1, error_correction = qrcode.constants.ERROR_CORRECT_H,box_size = 10, border = 4)ext=".png"for images in data:    qr.add_data(i)    qr.make(fit=True)    img=qr.make_image()    img.save("{}{}".format(i,ext))但是在每个循环之后，创建的图像也包含前一个图像的值，如何解决这个问题？

Answer 1

您正在循环外创建 QR 对象。

您最好在 for 循环中初始化对象<class 'qrcode.main.QRCode'>并

使用函数来创建 QR 图像，因为函数内的变量具有本地范围。

ext=".png"

def createQr(data):

    qr = qrcode.QRCode(version = 1, error_correction = qrcode.constants.ERROR_CORRECT_H,box_size = 10, border = 4)

    qr.add_data(data)

    qr.make(fit=True)

    img=qr.make_image()

    return img

for i in data:

    img = createQr(i)

    img.save("{}{}".format(i,ext))

同样正如@martineau所提到的，您必须将循环变量从图像更改为i

Answer 2

从 Excel 文件中，它从第二行开始读取“A”列中的数据并生成其 QR 码，并创建一个名为“qrcode_produced”的新 Excel 文件，该文件在 B 列中生成 QR 码。

# modules needed

import qrcode

from tkinter import filedialog

from tkinter import *

import openpyxl

from openpyxl import Workbook

from openpyxl.styles import Alignment

from openpyxl import load_workbook

#select the excel file to be read

# the texts must be in the "A" column starting with "2" row. In the B column, qrcodes will be seen.

print('select xlsx file:')

root = Tk()

root.filename =  filedialog.askopenfilename(initialdir = "/",title = "Select file",filetypes = (("xlsx files","*.xlsx"),("all files","*.*")))

print (root.filename)

# select the folder to save qrcodes as png format images and excel file with qrcodes

print('where to save excel file and qrcodes:')

root2 = Tk()

root2.withdraw()

folder_selected = filedialog.askdirectory()

# read the excel file

workbook = load_workbook(str(root.filename))

sheet = workbook.active

# settings for qrcode to be produced

qr = qrcode.QRCode(

    version=1,

    error_correction=qrcode.constants.ERROR_CORRECT_L,

    box_size=4,

    border=2,)

# excel file cell size settings that will be produced 

sheet.column_dimensions['B'].width = 25

for i in range(1,len(sheet['A'])+1):

    sheet.row_dimensions[i+1].height=150

# Title of B column

sheet["B1"]="Qr_Codes"

# production of qrcodes for each row in the A column except first row. Skips the empty rows.

for i in range(2,len(sheet['A'])+1):

  if sheet.cell(row=i, column=1).value is None:

    continue 

  else:

    qr.add_data(str(sheet.cell(row=i, column=1).value))

    qr.make(fit=True)

    img = qr.make_image()

    img.save(folder_selected + "/" + "row_"+str(i)+"_qrcode.png")

    img=openpyxl.drawing.image.Image(folder_selected + "/" + "row_"+str(i)+"_qrcode.png")

    img.anchor = "B" + str(i)

    sheet.add_image(img)

    sheet["B" + str(i)].alignment = Alignment(horizontal='center', vertical='center')

    sheet["A" + str(i)].alignment = Alignment(horizontal='center', vertical='center')

# saving the excel file

workbook.save(folder_selected+ "/qrcode_produced.xlsx")

Answer 3

首先请转换为csv。然后你应该添加枚举，这样你的文件名也会有一个索引号，而不是有多个 file.jpg。

import csv

import qrcode

with open('D:\QR\qrcodes.csv') as csvfile:

    fieldnames= ["Your_Column"]

    reader= csv.reader(csvfile)

    qr = qrcode.QRCode(

    version=1,

    error_correction=qrcode.constants.ERROR_CORRECT_L,

    box_size=10,

    border=4,

    )

    for i, row in enumerate(reader):

        labeldata = row[0]

        qr.add_data(labeldata)

        qr.make(fit=True)

        img = qr.make_image()

        img.save("test{}.jpg".format(i))