工作上常用到给表格中插入图片, 特别是比较多的时候, 很麻烦, 于是尝试用python解决这个问题.

以下是我自己尝试出来经常在用的, 这里做个记录备份.

** 自动插入的图片的名称中需含有表格中某列的值, 作为关健字, 以便进行自动匹配

比如: 图片名为: AABBCC.jpg, 则exce表格某行中某单元格中的值为AAB则可以匹配

0、表格列号的转化

因为Excel表格的列是A,B,....Z,AA,AB,...., 不是很方便, 比如: A是第1列, Z是第26列, AA是27列等, 为了方便这个做成函数, 比如, 第4列, 刚输出 D

def gcl(number):  #get a excel header's letter
    # 此函数用于通过一个数字来获取 excel 表头列的字母代码
    # 比如：第1列返回 A，第27列返回AA。以此类推
    import string
    # a = b * c + d  =>  b = a / c
    # a 为被除数，b 为除数，c 为商，d 为余数

    letter = list(string.ascii_uppercase)  # 26个大写英文字母
    num = range(1, 27, 1)
    dic = dict(zip(num, letter))  #生成字典

    if int(number) > 26:
        b = int(number) // 26  #取商
        d = int(number) % 26  #取余
        col_letter = dic[b] + dic[d]
    else:
        col_letter = dic[number]

    return col_letter

1、第一次做的插入图片

第一次做的这个逻辑不好, 很慢, 用了一次就放弃了. 留个念.

'Awork/00-sanho pictrue library/00-文件插入图片专用新图片/' 因为我的图片都统一放在这里, 所以在函数中默认了这个位置, 实际使用是正常给 pic_file 赋值就可以了

def add_pic_to_excel0(f_file,file_book,sht_name,col_pic_name,col_pic_insert,row_start):
    # 这个是第一个版本的,可运行,但是执行的速度很慢2022-9-2
    # 向excle 添加图片
    # f_file 表格地址
    # file_book 工作薄名称
    # sht_name 工作表名称
    # col_pic_name 插入的图片名称列,数字或字母
    # col_pic_insert 图片插入列,数字或字母
    # row_start 插入图片的起始行
    import openpyxl

    f_file_book = f_file + file_book
    f_file_book_r  = f_file + '图片版' + file_book
    
    wb=openpyxl.load_workbook(f_file_book)
    ws=wb[sht_name]
    
    # 最大行
    row_max = ws.max_row +1
    
    col_pic_name_m = str(col_pic_name)
    col_pic_insert_m = str(col_pic_insert)
    
    # 以下对 col_pic_name 插入的图片名称列,数字或字母,最终使col_pic_name_r为字母
    # .isalpha()是内置的一个字符串方法，用于检查给定的字符串是否只包含字母。
    if col_pic_name_m.isalpha():
        col_pic_name_r = col_pic_name
    elif col_pic_name_m.isdigit():
        # 如果是数字,调用函数转为字母
        col_pic_name_r = gcl(col_pic_name)
    
    # 以下对 col_pic_insert 插入的图片名称列,数字或字母,最终使col_pic_name_r为字母
    if col_pic_insert_m.isalpha():
        col_pic_insert_r = col_pic_insert
    elif col_pic_insert_m.isdigit():
        # 如果是数字,调用函数转为字母
        col_pic_insert_r = gcl(col_pic_insert)
    # 设置列宽
    ws.column_dimensions[col_pic_insert_r].width = 15
        
    for i in range(row_start,row_max):
        # 设置行高
        ws.row_dimensions[i].height=80
        
        # 图片名称
        pic_name = ws[col_pic_name_r + str(i)].value
        
        # 调用函数获取图片路径
        a = os_file_dir()
        # 将图片路径转为字典
        dict_name = dict(zip(a[1],a[0]))

        i_count = 0
        for j in dict_name.keys():
            if str(pic_name) in j:
                pic_dir = dict_name[j]
                cell = col_pic_insert_r + str(i)
                
                # 获取图片地址
                img=openpyxl.drawing.image.Image(pic_dir)

                # 设置图片的宽度
                img.width  = 114
                img.height = 101

                # 在单元格中添加img图像
                ws.add_image(img,cell)
                
                # 图片插入后将工作薄并保存
                wb.save(f_file_book_r)  
                # 加一个输出，查看程序运行情况
                print(str(pic_name) + '插入成功')
                
                i_count += 1
        
        if i_count == 0:
            # 将插入的结果打印出来
            print(str(pic_name) + ' 奇怪了,居然真的没有插入成功')
            continue
            
    wb.close()

2、第2次做的插入图片

优化后的这个很快, 几秒钟搞定

def add_pic_to_excel(f_file,file_book,sht_name_li,col_pic_name,col_pic_insert,row_start,pic_file='Awork/00-sanho pictrue library/00-文件插入图片专用新图片/'):
    # 向excle 添加图片,这个快
    # f_file 表格地址
    # file_book 工作薄名称
    # sht_name 工作表名称
    # col_pic_name 插入的图片名称列,数字或字母
    # col_pic_insert 图片插入列,数字或字母
    # row_start 插入图片的起始行
    import openpyxl

    f_file_book = f_file + file_book
    f_file_book_r  = f_file + '图片版' + file_book
    
    # 初始化几个参数    
    col_pic_name_m = str(col_pic_name)
    col_pic_insert_m = str(col_pic_insert)
    
    # 以下对 col_pic_name 插入的图片名称列,数字或字母,最终使col_pic_name_r为字母
    if col_pic_name_m.isalpha():
        col_pic_name_r = col_pic_name
    elif col_pic_name_m.isdigit():
        # 如果是数字,调用函数转为字母
        col_pic_name_r = gcl(col_pic_name)
    
    # 以下对 col_pic_insert 插入的图片名称列,数字或字母,最终使col_pic_insert_r为字母
    if col_pic_insert_m.isalpha():
        col_pic_insert_r = col_pic_insert
    elif col_pic_insert_m.isdigit():
        # 如果是数字,调用函数转为字母
        col_pic_insert_r = gcl(col_pic_insert)
    
    wb=openpyxl.load_workbook(f_file_book)
    
    # 循环工作表列表
    for sht_name in sht_name_li:
        # 将图片路径转为字典
        dict_name = build_pic_dir_dict(f_file,file_book,sht_name,col_pic_name,col_pic_insert,row_start,pic_file)
        ws=wb[sht_name]
        print('_'*5 + '现在给 ' + sht_name + ' 插入图片' + '_'*5)
        
        # 最大行
        row_max = ws.max_row +1

        # 设置列宽
        ws.column_dimensions[col_pic_insert_r].width = 15

        for i in range(row_start,row_max):
            # 设置行高
            ws.row_dimensions[i].height=80

            # 图片名称
            pic_name = str(ws[col_pic_name_r + str(i)].value)

            if pic_name in dict_name.keys():

                pic_dir = dict_name[pic_name]
                cell = col_pic_insert_r + str(i)

                # 获取图片地址
                img=openpyxl.drawing.image.Image(pic_dir)

                # 设置图片的宽度
                img.width  = 114
                img.height = 101

                # 在单元格中添加img图像
                ws.add_image(img,cell)

                # 加一个输出，查看程序运行情况
                print(str(pic_name) + '插入成功')

            else:
                print(str(pic_name) + ' 奇怪了,居然真的没有插入成功')
            
    wb.save(f_file_book_r)       
    wb.close()

补充

优化的里面主是预先做了个图片名称与地址的字典函数, 插入图片时直接调用, 在这里补上

def build_pic_dir_dict(f_file,file_book,sht_name,col_pic_name,col_pic_insert,row_start,pic_file='Awork/00-sanho pictrue library/00-文件插入图片专用新图片/'):
    # 向excle 添加图片,首先先建立一个图片名关键字与对应图片路径的字典
    # f_file 表格地址
    # file_book 工作薄名称
    # sht_name 工作表名称
    # col_pic_name 插入的图片名称列,数字或字母,优选数字
    # col_pic_insert 图片插入列,数字或字母,优选数字
    # row_start 插入图片的起始行
    import pandas as pd

    f_file_book = f_file + file_book

    df = pd.read_excel(f_file_book,header=row_start-2,sheet_name=sht_name)
    
    # 将含图片名称列转为数列
    pic_name_li = df.iloc[:,col_pic_name-1].tolist()

    # 调用函数获取图片路径
    a = os_file_dir(pic_file)
    # 将图片路径转为字典
    dict_name = dict(zip(a[1],a[0]))
    
    # 建立插入图片的含图片名(key)及图片路径(value)的字典
    dict_new = {}
    
    # 建立图片名称列(key),实为图片名称含想插入图片关健字及路径的字典
    for pic_name in pic_name_li:
        # 判断如果字典有就跳过,避免有相同图片名的路径
        if str(pic_name) not in dict_new.keys():
            # 循环图片名(图片名为字典的keys
            for j in dict_name.keys():
                # 判断图片名是否包含想插入图片的关键字
                if str(pic_name) in j:
                    # 图片路径
                    pic_dir = dict_name[j]
                    # 图片名关键字
                    new_key = str(pic_name)
                    # 生成字典
                    dict_new[new_key] = pic_dir
        continue
    return dict_new

3、使用

import pandas as pd

f_file = r"/Users/add58/Downloads/"
file_book = "23年清仓品库存数.xlsx"
sht_name_li = ["Sheet1"]
col_pic_name = 1 # 要插入图片的图片名称所在的列,比如, A列
col_pic_insert = 3  # 图片插入在第几列, 3为插入在C列
row_start = 2 # 从第2行开始插入图片

add_pic_to_excel(
    f_file, file_book, sht_name_li, col_pic_name, col_pic_insert, row_start
)
print("finish")

插入成本则显示 插入成本, 如果图片名不对或没有这张图片, 显示 ' 奇怪了,居然真的没有插入成功'

2024-9-19 补充

有人问os_file_dir()这个函数, 之前忘记放上了

## 查找图片
def os_file_dir(
        file_path='Awork/00-sanho pictrue library/00-文件插入图片专用新图片/',
        FILE_TYPE=['.png', '.jpg', 'JPG', 'PNG']):
    # 函数作用读取文夹下面的文件的全路径 和 文件名
    # file_path 图片存放的位置, 我自己的图片位置就给了默认值, 可重新赋值
    # FILE_TYPE 图片的格式, 一定加.  一般常用图片的后缀名就这几种, 根据实际需求可重新赋值
    # 模块os中的walk()函数可以遍历文件夹下所有的文件
    # 该函数可以得到一个三元组tupple(dirpath, dirnames, filenames).
    # dirpath：string，代表目录的路径；
    # dirnames：list，包含了当前dirpath路径下所有的子目录名字（不包含目录路径）；
    # filenames：list，包含了当前dirpath路径下所有的非目录子文件的名字（不包含目录路径）。
    # dirnames和filenames均不包含路径信息，如需完整路径，可使用os.path.join(dirpath, dirnames)
    import os
    # 以下这个判断是我自己在用的, 别人一般用不到这个判断
    if 'Users' in file_path:
        FILE_PATH = file_path
    else:
        FILE_PATH = f_dir + file_path
    Full_filepath_name = []
    The_file_name = []
    for root, dirs, files in os.walk(FILE_PATH):
        for file in files:
            # 这句是判断文件夹下面是否有指定的 FILE_TYPE 格式的文件
            if os.path.splitext(file)[1] in FILE_TYPE:
                Full_filepath_name.append(os.path.join(root, file))
                The_file_name.append(file)
    return Full_filepath_name, The_file_name

4、全部代码

将以上所有函数和代码封装为类, 在本次封装中,对原代码进行了简化, 并对函数名和参数名进行了规范,使其更加方便的阅读.

其中类方法get_image_paths中的2个参数

1、file_path='/Users/add58/Library/Mobile Documents/com~apple~CloudDocs/Awork/00-sanho pictrue library/00-文件插入图片专用新图片',

2、file_types=['.png', '.jpg', 'JPG', 'PNG'])

需要根据实际情况进行修改

为方便查阅, 添加了详尽的注释.

import string
import openpyxl
import pandas as pd
import os

class ExcelImageInserter:
    """一个用于向Excel表格中插入图片的类"""

    def __init__(self, file_path, file_name):
        """
        初始化ExcelImageInserter类

        :param file_path: Excel文件的路径
        :param file_name: Excel文件的名称
        """
        self.file_path = file_path  # 保存文件路径
        self.file_name = file_name  # 保存文件名称
        self.full_file_path = os.path.join(file_path, file_name)  # 拼接完整的文件路径

    @staticmethod
    def get_excel_column_letter(column_number):
        """
        根据列号获取Excel列的字母代码

        :param column_number: 列号（从1开始）
        :return: 对应的Excel列字母
        """
        if column_number <= 0:  # 如果列号小于等于0，返回空字符串
            return ''
        letters = string.ascii_uppercase  # 获取所有大写字母
        column_letter = ''  # 初始化列字母
        while column_number > 0:  # 当列号大于0时循环
            column_number -= 1  # 列号减1
            column_letter = letters[column_number % 26] + column_letter  # 计算当前字母并添加到前面
            column_number //= 26  # 列号整除26，准备下一轮计算
        return column_letter  # 返回最终的列字母

    def add_images_to_excel(self, sheet_names, image_name_col, image_insert_col, start_row):
        """
        向指定的Excel工作表中插入图片

        :param sheet_names: 工作表名称列表
        :param image_name_col: 图片名称列（数字或字母）
        :param image_insert_col: 图片插入列（数字或字母）
        :param start_row: 插入图片的起始行
        """
        output_file_path = os.path.join(self.file_path, '图片版' + self.file_name)  # 拼接输出文件路径

        # 确保列名是字符串
        image_name_col_str = str(image_name_col)  # 将图片名称列转换为字符串
        image_insert_col_str = str(image_insert_col)  # 将图片插入列转换为字符串

        # 获取列字母
        image_name_col_letter = self.get_excel_column_letter(int(image_name_col_str)) if image_name_col_str.isdigit() else image_name_col_str  # 获取图片名称列的字母
        image_insert_col_letter = self.get_excel_column_letter(int(image_insert_col_str)) if image_insert_col_str.isdigit() else image_insert_col_str  # 获取图片插入列的字母

        workbook = openpyxl.load_workbook(self.full_file_path)  # 加载Excel工作簿

        for sheet_name in sheet_names:  # 遍历每个工作表名称
            image_dict = self.build_image_path_dict(sheet_name, image_name_col, start_row)  # 构建图片路径字典
            worksheet = workbook[sheet_name]  # 获取当前工作表
            print(f'_____现在给 {sheet_name} 插入图片_____')  # 打印当前插入图片的工作表名称

            max_row = worksheet.max_row + 1  # 获取当前工作表的最大行数
            worksheet.column_dimensions[image_insert_col_letter].width = 15  # 设置插入列的宽度

            for row in range(start_row, max_row):  # 遍历从起始行到最大行
                worksheet.row_dimensions[row].height = 80  # 设置当前行的高度
                image_name = str(worksheet[image_name_col_letter + str(row)].value)  # 获取当前行的图片名称

                if image_name in image_dict:  # 如果图片名称在字典中
                    image_path = image_dict[image_name]  # 获取对应的图片路径
                    cell = f'{image_insert_col_letter}{row}'  # 计算插入图片的单元格位置
                    img = openpyxl.drawing.image.Image(image_path)  # 创建图片对象
                    img.width, img.height = 114, 101  # 设置图片的宽度和高度
                    worksheet.add_image(img, cell)  # 在指定单元格中添加图片
                    print(f'{image_name} 插入成功')  # 打印插入成功的信息
                else:
                    print(f'{image_name} 奇怪了,居然真的没有插入成功')  # 打印未能插入的图片名称

        workbook.save(output_file_path)  # 保存修改后的工作簿
        workbook.close()  # 关闭工作簿

    def build_image_path_dict(self, sheet_name, image_name_col, start_row):
        """
        建立图片名称与路径的字典

        :param sheet_name: 工作表名称
        :param image_name_col: 图片名称列（数字）
        :param start_row: 数据起始行
        :return: 包含图片名称和路径的字典
        """
        df = pd.read_excel(self.full_file_path, header=start_row - 2, sheet_name=sheet_name)  # 读取指定工作表的数据
        image_name_list = df.iloc[:, image_name_col - 1].tolist()  # 获取图片名称列的数据
        image_paths = self.get_image_paths()  # 获取图片路径和文件名
        image_dict = dict(zip(image_paths[1], image_paths[0]))  # 创建图片名称与路径的字典

        image_name_dict = {}  # 初始化图片名称字典
        for image_name in image_name_list:  # 遍历每个图片名称
            image_name_str = str(image_name)  # 确保图片名称是字符串
            if image_name_str not in image_name_dict:  # 如果字典中没有该图片名称
                for key in image_dict:  # 遍历图片路径字典的键
                    if image_name_str in key:  # 如果图片名称在键中
                        image_name_dict[image_name_str] = image_dict[key]  # 将图片名称和路径添加到字典
        return image_name_dict  # 返回图片名称与路径的字典

    @staticmethod
    def get_image_paths(file_path='/Users/add58/Library/Mobile Documents/com~apple~CloudDocs/Awork/00-sanho pictrue library/00-文件插入图片专用新图片', file_types=['.png', '.jpg', 'JPG', 'PNG']):
        """
        读取指定文件夹下的所有图片路径和文件名

        :param file_path: 图片存放的文件夹路径
        :param file_types: 支持的图片文件类型
        :return: 包含图片路径和文件名的元组
        """
        full_file_paths = []  # 初始化完整文件路径列表
        file_names = []  # 初始化文件名列表
        for root, dirs, files in os.walk(file_path):  # 遍历指定文件夹
            for file in files:  # 遍历文件夹中的每个文件
                if os.path.splitext(file)[1] in file_types:  # 如果文件类型在支持的类型中
                    full_file_paths.append(os.path.join(root, file))  # 添加完整路径到列表
                    file_names.append(file)  # 添加文件名到列表
        return full_file_paths, file_names  # 返回完整路径和文件名的元组

# 示例调用
file_path = r"/Users/add58/Downloads/"  # 设置Excel文件的路径
file_name = "礼品展选品pprice.xlsx"  # 设置Excel文件的名称
sheet_names = ["Sheet1"]  # 设置要处理的工作表名称
image_name_col = 2  # 设置图片名称列
image_insert_col = 7  # 设置图片插入列
start_row = 2  # 设置插入图片的起始行

image_inserter = ExcelImageInserter(file_path, file_name)  # 创建ExcelImageInserter对象
image_inserter.add_images_to_excel(sheet_names, image_name_col, image_insert_col, start_row)  # 调用方法插入图片
print("finish")  # 打印完成信息

效果展示: