一、效果
原始文件：
輸出文件：
二、代碼
import os
import logging
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as ExcelImage
from barcode import EAN13
from barcode.writer import ImageWriterlogging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')def delete_files(directory):file_list = os.listdir(directory)for file in file_list:file_path = os.path.join(directory, file)if os.path.isfile(file_path):os.remove(file_path)def generate_barcode_image_with_text(barcode_str, output_path_without_ext):# Generate barcode image without textean = EAN13(barcode_str, writer=ImageWriter())saved_path = ean.save(output_path_without_ext)return saved_pathdef insert_barcodes_to_excel(excel_file, ISBNCol, imgCol):logging.info("開始處理")if not os.path.exists('barcode_temp'):os.makedirs('barcode_temp')wb = load_workbook(excel_file)ws = wb.activelogging.info(f"加載工作表: {ws.title}")img_height_in_points = 45 # 你設置的圖片高度，單位是磅，1磅約等于1.33像素for row in range(2, ws.max_row + 1):barcode_cell = ws.cell(row=row, column=ISBNCol)barcode_str = str(barcode_cell.value).strip()if not barcode_str or len(barcode_str) != 13 or not barcode_str.isdigit():logging.warning(f"第{row}行條碼格式不正確，跳過: {barcode_str}")continueimg_path_no_ext = os.path.join('barcode_temp', f'barcode_{barcode_str}')try:img_path = generate_barcode_image_with_text(barcode_str, img_path_no_ext)except Exception as e:logging.error(f"生成條碼失敗，行{row}，條碼{barcode_str}，錯誤: {e}")continueimg_for_excel = ExcelImage(img_path)img_for_excel.width = 180img_for_excel.height = 60img_cell = f'{imgCol}{row}'# # 設置列寬ws.column_dimensions[imgCol].width = img_for_excel.width * 0.15# # 設置行高ws.row_dimensions[row].height = img_for_excel.heightws.add_image(img_for_excel, img_cell)# 調整當前行高，避免圖片重疊current_height = ws.row_dimensions[row].heightif current_height is None or current_height < img_height_in_points:ws.row_dimensions[row].height = img_height_in_points# logging.info(f"插入條碼圖片到 {img_cell} 并調整行高")new_file = os.path.splitext(excel_file)[0] + '_with_barcodes.xlsx'try:wb.save(new_file)logging.info(f"保存新文件: {new_file}")except PermissionError:logging.error(f"保存文件失敗，可能文件被打開: {new_file}")if os.path.exists('barcode_temp'):logging.info("刪除臨時文件")delete_files('barcode_temp')logging.info("完成處理！")if __name__ == "__main__":excel_path = 'D:\\temp\\圖書清單.xlsx'insert_barcodes_to_excel(excel_path, 1, "B")
三、說明
1、insert_barcodes_to_excel參數1：原始Excel表格文件絕對路徑。
2、insert_barcodes_to_excel參數2：ISBN所在列，數字格式。例如：ISBN在A列則輸入1，在B列則輸入2。
3、insert_barcodes_to_excel參數3：生成的條形碼需要放在第幾列，大寫字母格式。例如：需要放在第二列則輸入B，第三列則輸入C。
