生成1列条形码，将条形码图片插入excel

 import os

from pystrich.code128 import Code128Encoder

import polars as pl

from PIL import Image

import io

import openpyxl

from openpyxl.utils import get_column_letter

def generate_barcode(value, output_dir):

    """生成条形码图片并返回图片对象"""

    if not os.path.exists(output_dir):

        os.makedirs(output_dir)

   

    filename = f"{output_dir}/{value}.png"

   

    encoder = Code128Encoder(str(value))

    encoder.save(filename)

   

    img = Image.open(filename)

    img = img.resize((200, 75))

   

    img_byte_arr = io.BytesIO()

    img.save(img_byte_arr, format='PNG')

    img_byte_arr = img_byte_arr.getvalue()

   

    os.remove(filename)

   

    return img_byte_arr

def create_excel_with_barcodes(df, barcode_values, output_dir,output_file):

    """创建包含条形码的Excel文件"""

    # 创建工作簿

    workbook = openpyxl.Workbook()

    worksheet = workbook.active

    # 写入表头

    for col_idx, header in enumerate(df.columns, start=1):

        worksheet.cell(row=1, column=col_idx, value=header)

    # 写入数据

    for row_idx, row in enumerate(df.iter_rows(), start=2):

        for col_idx, value in enumerate(row, start=1):

            worksheet.cell(row=row_idx, column=col_idx, value=value)

    # 添加条形码图片

    for idx, value in enumerate(barcode_values, start=2):

        barcode_data = generate_barcode(value,output_dir)

        img = Image.open(io.BytesIO(barcode_data))

        img_byte_arr = io.BytesIO()

        img.save(img_byte_arr, format='PNG')

       

        img_excel = openpyxl.drawing.image.Image(img_byte_arr)

        img_excel.anchor = f'{get_column_letter(len(df.columns) + 1)}{idx}'

        worksheet.add_image(img_excel)

    # 调整列宽

    for col in worksheet.columns:

        max_length = 0

        column = col[0].column_letter

        for cell in col:

            if cell.value:

                max_length = max(max_length, len(str(cell.value)))

        worksheet.column_dimensions[column].width = max_length + 2

    # 为条形码列设置宽度

    worksheet.column_dimensions[get_column_letter(len(df.columns) + 1)].width = 30

    # 设置统一的行高

    for row in range(2, len(df) + 2):

        worksheet.row_dimensions[row].height = 80

    # 保存文件

    workbook.save(output_file)

# 读取Excel文件

input_file = '/mnt/c/Users/xuefliang/Downloads/新冠疫苗退货3692.xlsx'

output_file = '/mnt/c/Users/xuefliang/Downloads/output_with_barcodes.xlsx'

output_dir='/mnt/c/Users/xuefliang/Downloads/'

# 使用polars读取Excel文件

df = pl.read_excel(input_file)

# 获取需要生成条形码的值

barcode_values = df['dzjgm'].to_list()

# 创建带有条形码的Excel文件

create_excel_with_barcodes(df, barcode_values, output_dir,output_file)