from openpyxl.drawing.image import Image as OpenpyxlImage
from openpyxl.utils import get_column_letter
import os
def insert_images_into_excel(worksheet, image_dir, target_column, start_row=2, image_size=(20, 20)):
"""
插入图片到Excel的工作表中
:param worksheet: Excel工作表对象
:param image_dir: 包含图片文件的目录路径
:param target_column: 目标列字母，例如 'A' 或 'Z'
:param start_row: 开始插入图片的行号，默认为 2
:param image_size: 图片的宽度和高度，单位为厘米（cm），默认为 (20, 20)
:return: None
"""
images_paths =[os.path.join(image_dir, img_path) for img_path in os.listdir(image_dir) if img_path.lower().endswith(('.png', '.jpg'))]
images_paths.sort(key=lambda x: os.path.basename(x))
for i, img_path inenumerate(images_paths, start=start_row):
img = OpenpyxlImage(img_path)
img.width, img.height = image_size
cell_position =f'{target_column}{i}'
worksheet.add_image(img, cell_position)
if __name__ =="__main__":
try:
workbook = load_workbook('qrcode.xlsx')
worksheet = workbook.active
dir_path ='./images'
last_column = get_column_letter(worksheet.max_column)
worksheet.cell(row=1, column=worksheet.max_column, value='二维码数据')
insert_images_into_excel(worksheet, dir_path, last_column)
workbook.save('modified_file.xlsx')
except Exception as e:
print(f"An error occurred: {e}")
