Openpyxl写入条形码图片致Excel损坏，求技术解决方案

我编写Python脚本，计划通过Openpyxl读取Excel模板，用python-barcode生成条形码图片后写入Excel，但运行时出错导致原Excel文件损坏，排查无果，搜索到的方案多不匹配场景。

原代码 #

def test_room():
    root = Tk()
    root.excel = filedialog.askopenfilename(title='Select Excel', filetypes=("Excel", "*.xlsx"),('All Files','*.*')))
    worksheet = openpyxl.load_workbook(root.excel, data_only=True)
    sheet = worksheet.sheetnames
    sticker_front = worksheet[sheet[0]]
    sticker_side = worksheet[sheet[1]]
    all_data = worksheet[sheet[2]]  
    col1_tolist = []
    col2_tolist = []

    for val in range(1, all_data.max_row+1):
        col1 = all_data.cell(row=val, column=1).value
        col1 = f'No.{col1}'
        col2 = all_data.cell(row=val, column=2).value
        col3 = all_data.cell(row=val, column=3).value
        col1_tolist.append(col1)
        col2_tolist.append(col2)

        def template_front1(): #top left
            if len(col1_tolist) % 4 == 1:
                sticker_front['A1'].value = col1_tolist[0]
                sticker_front['A2'].value = col2_tolist[0]
                with open(rf"D:\Workstuff\my-work-python-script\Print Barcode (dev)\result\{col3}.png".replace("/00","-00"), "wb+") as files:
                    Code128(col3, writer=ImageWriter()).write(files)
                    img1 = openpyxl.drawing.image.Image(files)
                    img1.height = 76
                    img1.width = 163
                    sticker_front.add_image(img1, 'A3')
                worksheet.save(root.excel)
        template_front1()

报错信息 #

Traceback (most recent call last):
  File "d:\Workstuff\my-work-python-script\Print Barcode (dev)\barcodeprint.py", line 148, in <module>
    main()
  File "d:\Workstuff\my-work-python-script\Print Barcode (dev)\barcodeprint.py", line 94, in main
    template_front1()
  File "d:\Workstuff\my-work-python-script\Print Barcode (dev)\barcodeprint.py", line 47, in template_front1
    worksheet.save(root.excel)
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\workbook\workbook.py", line 407, in save
    save_workbook(self, filename)
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 77, in write_data
    self._write_images()
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 116, in _write_images
    self._archive.writestr(img.path[1:], img._data())
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\drawing\image.py", line 48, in _data
    img = _import_image(self.ref)
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\openpyxl\drawing\image.py", line 16, in _import_image
    img = PILImage.open(img)
  File "C:\Users\Comseven\AppData\Local\Programs\Python\Python310\lib\site-packages\PIL\Image.py", line 3096, in open
    fp.seek(0)
ValueError: seek of closed file

解决方案 #

核心问题 #

你在with open代码块内创建openpyxl.drawing.image.Image对象，代码块结束后文件流会自动关闭，但openpyxl要到保存Excel时才会读取图片数据，此时文件流已关闭，导致报错。此外代码还存在语法错误、循环内定义函数、频繁保存等问题。

修复措施 #

1. 先保存条形码图片到本地，再用文件路径创建Image对象
2. 移除循环内的函数定义，避免逻辑混乱
3. 统一保存Excel文件，不要在循环内重复执行保存操作
4. 修复文件选择对话框的语法错误（多了一个闭合括号）

修正后的代码 #

def test_room():
    root = Tk()
    # 修复语法错误：移除多余的闭合括号
    root.excel = filedialog.askopenfilename(
        title='Select Excel', 
        filetypes=(("Excel", "*.xlsx"), ('All Files','*.*'))
    )
    worksheet = openpyxl.load_workbook(root.excel, data_only=True)
    sheet = worksheet.sheetnames
    sticker_front = worksheet[sheet[0]]
    all_data = worksheet[sheet[2]]  
    col1_tolist = []
    col2_tolist = []

    for val in range(1, all_data.max_row+1):
        col1 = all_data.cell(row=val, column=1).value
        col1 = f'No.{col1}'
        col2 = all_data.cell(row=val, column=2).value
        col3 = all_data.cell(row=val, column=3).value
        col1_tolist.append(col1)
        col2_tolist.append(col2)

        # 直接执行逻辑，不再嵌套函数
        if len(col1_tolist) % 4 == 1:
            sticker_front['A1'].value = col1_tolist[0]
            sticker_front['A2'].value = col2_tolist[0]
            # 生成图片路径
            img_path = rf"D:\Workstuff\my-work-python-script\Print Barcode (dev)\result\{col3}.png".replace("/00","-00")
            # 保存条形码图片到本地
            with open(img_path, "wb") as files:
                Code128(col3, writer=ImageWriter()).write(files)
            # 用文件路径创建Image对象，此时文件已保存，openpyxl可直接读取本地文件
            img1 = openpyxl.drawing.image.Image(img_path)
            img1.height = 76
            img1.width = 163
            sticker_front.add_image(img1, 'A3')
    
    # 所有操作完成后统一保存Excel
    worksheet.save(root.excel)
    root.destroy()  # 关闭Tk窗口

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