Python Excel操作之將ISBN轉(zhuǎn)條形碼

更新時間：2025年06月22日 09:35:11 作者：晨曦之光Wing

這篇文章主要為大家詳細(xì)介紹了Python Excel操作中如何將ISBN轉(zhuǎn)條形碼功能,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

一、效果

原始文件：

輸出文件：

二、代碼

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 ImageWriter
 
logging.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 text
    ean = EAN13(barcode_str, writer=ImageWriter())
    saved_path = ean.save(output_path_without_ext)
 
    return saved_path
 
 
def 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.active
    logging.info(f"加載工作表: {ws.title}")
 
    img_height_in_points = 45  # 你設(shè)置的圖片高度，單位是磅，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}")
            continue
 
        img_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}")
            continue
 
        img_for_excel = ExcelImage(img_path)
        img_for_excel.width = 180
        img_for_excel.height = 60
 
        img_cell = f'{imgCol}{row}'
 
        # # 設(shè)置列寬
        ws.column_dimensions[imgCol].width = img_for_excel.width * 0.15
        # # 設(shè)置行高
        ws.row_dimensions[row].height = img_for_excel.height
 
        ws.add_image(img_for_excel, img_cell)
 
        # 調(diào)整當(dāng)前行高，避免圖片重疊
        current_height = ws.row_dimensions[row].height
        if 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} 并調(diào)整行高")
 
    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")