如何基于电子表格数据批量生成PDF，解决仅生成最后一条记录对应PDF的问题

AI 小北

2026-10-19

服务收据自动开具问题说明

我正在开发一款服务收据自动开具应用，通过查阅网上资料已使用canvas库实现PDF生成功能。代码运行正常，可正常读取电子表格并将数据存入列表，但在PDF开具环节存在问题：程序仅生成电子表格中最后一位客户对应的PDF，无法为表格中所有客户生成对应PDF。

原有代码

from reportlab.lib.pagesizes import letter, A4
from datetime import date, datetime, timezone, timedelta
import openpyxl


# Opens the spreadsheet and obtains the status of the last payment.

wb = openpyxl.load_workbook('./lib/cobranca3.xlsx')
sheet = wb['Sheet1']

lastCol = sheet.max_column

# Checks the payment status of each customer.

unpaidMembers = {}
codigos = []
clients = []
cnpjs = []
emails = []
vencimentos = []
valores = []
acrescimos = []
antecipacoes = []
descontos = []
months = []

for r in range(2, sheet.max_row + 1):
    for c in range(13, lastCol + 1):
        payment = sheet.cell(row=r, column=c).value
        if payment != 'ok' or '':
            codigo = sheet.cell(row=r, column=1).value
            cliente = sheet.cell(row=r, column=2).value
            cnpj = sheet.cell(row=r, column=3).value
            email = sheet.cell(row=r, column=4).value
            venc = sheet.cell(row=r, column=6).value
            valor = sheet.cell(row=r, column=7).value
            acresc = sheet.cell(row=r, column=8).value
            antec = sheet.cell(row=r, column=9).value
            desc = sheet.cell(row=r, column=10).value
            month = sheet.cell(row=1, column=c).value
            codigos.append(codigo)
            clients.append(cliente)
            cnpjs.append(cnpj)
            emails.append(email)
            vencimentos.append(venc)
            valores.append(valor)
            months.append(month)
            unpaidMembers[cliente] = email
            print('Line:', r, 'Column:', c, 'Código:', codigo, 'Client:', cliente, 'CNPJ:', cnpj, 'Email:', email, 'Vencimento:', venc, 'Valor:', (valor + acresc) - antec - desc, 'Month:', month)
            #print('dictionary created successfully')


for r in range(1, sheet.max_row + 1):

    # Creating Canvas
    #c = canvas.Canvas("invoice.pdf", pagesize=A4, bottomup=0)
    c = canvas.Canvas(str(codigo) + '_' + 'PS.pdf', pagesize=A4, bottomup=0)

    data_atual = date.today()
    data_atual_formatada = '{}/{}/{}'.format(data_atual.day, data_atual.month, data_atual.year)

    # Setting the font for Name title of company
    c.setFont("Helvetica-Bold", 20)
    # Inserting the name of the company
    c.drawCentredString(295, 50, "NOTA DE SERVIÇOS PRESTADOS")

    # linha de separação
    c.line(10, 75, 580, 75)  # começa em, tem altura de, comprimento, altura

    # Setting the font
    c.setFont("Helvetica-Bold", 12)
    c.drawString(10, 110, "Código: %s" % codigo)

    c.drawString(200, 110, "Cliente: %s" % cliente)

    c.drawString(10, 140, "Data Emissão: %s" % data_atual_formatada)

    c.drawString(200, 140, "Data vencimento: %s" % month)

    # linha de separação
    c.line(10, 165, 580, 165)  # começa em, tem altura de, comprimento, altura

    c.drawString(10, 190, "Pagamento referente à:")

    # Setting the font
    c.setFont("Helvetica-Bold", 15)

    c.drawString(35, 220, "Honorário: ")
    c.drawString(120, 220, "%s" % month)

    # Setting the font
    c.setFont("Helvetica-Bold", 12)

    c.drawString(35, 250, "Valor: ")
    c.drawString(500, 250, "R$ %s" % valor)

    c.drawString(35, 280, "Acréscimo: ")
    c.drawString(500, 280, "R$ %s" % acresc)

    c.drawString(35, 310, "Antecipação: ")
    c.drawString(500, 310, "R$ %s" % antec)

    c.drawString(35, 340, "Desconto: ")
    c.drawString(500, 340, "R$ %s" % desc)

    # Setting the font
    c.setFont("Helvetica-Bold", 15)

    total = (valor + acresc) - antec - desc
    #print(total)

    c.drawString(35, 400, "TOTAL: ")
    c.drawString(500, 400, "R$ %s" % total)

    # linha de separação
    c.line(10, 425, 580, 425)  # começa em, tem altura de, comprimento, altura

    c.drawString(125, 475, "BOLETO EMITIDO PELO BANCO SANTANDER")
    c.drawString(80, 500, "Omnia Tecnologia Digital Eireli - CNPJ 10.751.327/0001-59")

    # End the Page and Start with new
    c.showPage()
    # Saving the PDF
    c.save()

问题修复

存在的问题

缺少canvas模块导入，原有代码未引入reportlab.pdfgen.canvas
缴费状态判断逻辑错误：if payment != 'ok' or ''等价于永远为真，空缴费状态也会被识别为未缴费
PDF生成循环逻辑完全脱节：已将所有未缴费客户的信息存入了对应列表，但生成PDF的循环没有遍历这些列表，而是直接使用了前面遍历表格结束后残留的最后一个客户的变量值，导致始终只生成最后一个客户的PDF
存储数据时漏了把acresc、antec、desc存入对应列表，生成PDF时无法取到之前的历史数据
文件名没有区分度，不同客户生成的PDF会互相覆盖

修复后代码

from reportlab.lib.pagesizes import letter, A4
from reportlab.pdfgen import canvas
from datetime import date, datetime, timezone, timedelta
import openpyxl


# 读取表格
wb = openpyxl.load_workbook('./lib/cobranca3.xlsx')
sheet = wb['Sheet1']
lastCol = sheet.max_column

# 初始化存储变量
unpaidMembers = {}
codigos = []
clients = []
cnpjs = []
emails = []
vencimentos = []
valores = []
acrescimos = []
antecipacoes = []
descontos = []
months = []

# 遍历提取未缴费客户信息
for r in range(2, sheet.max_row + 1):
    for c in range(13, lastCol + 1):
        payment = sheet.cell(row=r, column=c).value
        # 修复状态判断逻辑
        if payment not in ('ok', ''):
            codigo = sheet.cell(row=r, column=1).value
            cliente = sheet.cell(row=r, column=2).value
            cnpj = sheet.cell(row=r, column=3).value
            email = sheet.cell(row=r, column=4).value
            venc = sheet.cell(row=r, column=6).value
            valor = sheet.cell(row=r, column=7).value
            acresc = sheet.cell(row=r, column=8).value
            antec = sheet.cell(row=r, column=9).value
            desc = sheet.cell(row=r, column=10).value
            month = sheet.cell(row=1, column=c).value
            # 所有字段都存入对应列表
            codigos.append(codigo)
            clients.append(cliente)
            cnpjs.append(cnpj)
            emails.append(email)
            vencimentos.append(venc)
            valores.append(valor)
            acrescimos.append(acresc)
            antecipacoes.append(antec)
            descontos.append(desc)
            months.append(month)
            unpaidMembers[cliente] = email
            print('Line:', r, 'Column:', c, 'Código:', codigo, 'Client:', cliente, 'CNPJ:', cnpj, 'Email:', email, 'Vencimento:', venc, 'Valor:', (valor + acresc) - antec - desc, 'Month:', month)

# 遍历所有未缴费记录生成PDF，而不是遍历表格行
for i in range(len(codigos)):
    # 按索引取对应客户的信息
    codigo = codigos[i]
    cliente = clients[i]
    valor = valores[i]
    acresc = acrescimos[i]
    antec = antecipacoes[i]
    desc = descontos[i]
    month = months[i]

    # 文件名用客户编码+月份避免重复覆盖
    c = canvas.Canvas(f"{codigo}_{month}_PS.pdf", pagesize=A4, bottomup=0)
    data_atual = date.today()
    data_atual_formatada = f"{data_atual.day}/{data_atual.month}/{data_atual.year}"

    c.setFont("Helvetica-Bold", 20)
    c.drawCentredString(295, 50, "NOTA DE SERVIÇOS PRESTADOS")
    c.line(10, 75, 580, 75)

    c.setFont("Helvetica-Bold", 12)
    c.drawString(10, 110, f"Código: {codigo}")
    c.drawString(200, 110, f"Cliente: {cliente}")
    c.drawString(10, 140, f"Data Emissão: {data_atual_formatada}")
    c.drawString(200, 140, f"Data vencimento: {month}")

    c.line(10, 165, 580, 165)
    c.drawString(10, 190, "Pagamento referente à:")

    c.setFont("Helvetica-Bold", 15)
    c.drawString(35, 220, "Honorário: ")
    c.drawString(120, 220, f"{month}")

    c.setFont("Helvetica-Bold", 12)
    c.drawString(35, 250, "Valor: ")
    c.drawString(500, 250, f"R$ {valor}")
    c.drawString(35, 280, "Acréscimo: ")
    c.drawString(500, 280, f"R$ {acresc}")
    c.drawString(35, 310, "Antecipação: ")
    c.drawString(500, 310, f"R$ {antec}")
    c.drawString(35, 340, "Desconto: ")
    c.drawString(500, 340, f"R$ {desc}")

    c.setFont("Helvetica-Bold", 15)
    total = (valor + acresc) - antec - desc
    c.drawString(35, 400, "TOTAL: ")
    c.drawString(500, 400, f"R$ {total}")

    c.line(10, 425, 580, 425)
    c.drawString(125, 475, "BOLETO EMITIDO PELO BANCO SANTANDER")
    c.drawString(80, 500, "Omnia Tecnologia Digital Eireli - CNPJ 10.751.327/0001-59")

    c.showPage()
    c.save()

内容的提问来源于stack exchange，提问作者Guvv

如何基于电子表格数据批量生成PDF，解决仅生成最后一条记录对应PDF的问题

原有代码 #

问题修复 #

存在的问题 #

修复后代码 #

原有代码

问题修复

存在的问题

修复后代码