python将图片写入excel

import json
import pandas as pd
from openpyxl import Workbook
from openpyxl.drawing.image import Image
excel_col_map={
        1:"A",
        2:"B",
        3:"C",
        4:"D",
        5:"E",
        6:"F",
        7:"G"
        }
def jsonfile_img2excel(json_file,excel_file,img_field):
    '''
    将json文件中的图片地址，写入到excel表格中
    '''
    data = pd.read_json(json_file, lines=True)
    columns=list(data.columns) # 获取表头 ['name', 'img_path', 'age']
    wb = Workbook()
    ws = wb.active
    # img_col_index = columns.index("img_path")+1
    img_col_index = columns.index(img_field)+1
    print(f"img_col_index={img_col_index}")# 图片在第几列 -》 2
    column_letter= excel_col_map.get(img_col_index) # 第几列 2-》B
    ws.append(columns)  # excel加入表头

    for _, row in data.iterrows():
        print(list(row)) # ['deng', 'D:\\code\\yingdao_2\\excel\\1.jpg', 23]
        ws.append(list(row))
        img = Image(row[img_field])
        img.width = 40
        img.height = 40
        # column_letter = 'C'
        ws.column_dimensions[column_letter].width = img.width
        print(f"max_row={ws.max_row},max_column={ws.max_column}")
        cell = ws.cell(row=ws.max_row, column=img_col_index)
        cell.value = None
        ws.add_image(img, f'{column_letter}{ws.max_row}')
        ws.row_dimensions[ws.max_row].height = 40
    wb.save(excel_file)


def img2excel(excel_path,headers,data_list,img_field,img_width=40,img_height=40):
    '''
    excel_path : 保存的excel文件路径
    headers : 表头
    data_list : 二维数据列表
    img_field: 表头中图片路径的字段名
    img_width: 图片宽
    img_height: 图片高
    '''
    wb = Workbook()
    ws = wb.active
    # 1.添加表头
    ws.append(headers) # 获取表头 ['name', 'img_path', 'age']
    # 2. 图片路径字段名 img_field='img_path' ,图片存哪列
    img_col_index = headers.index(img_field) + 1
    # 3. 获取图片所在列的名称 ，比如第2列--》 B列
    img_column_letter = excel_col_map.get(img_col_index)  # 第几列 2-》B
    # 4.将数据写入excel
    for data in data_list:
        # data 表示一行数据 => ['deng', 'D:\\code\\yingdao_2\\excel\\1.jpg', 23]
        ws.append(data)
        # 获取图片
        img = Image(data.get(img_field))
        # 设置图片大小
        img.width = img_width
        img.height = img_height
        # 图片所在列的宽度
        ws.column_dimensions[img_column_letter].width = img_width
        # 图片所在行的高度
        ws.row_dimensions[ws.max_row].height = img_height
        print(f"max_row={ws.max_row},max_column={ws.max_column}")
        # 定位到图片需要存放的单元格
        cell = ws.cell(row=ws.max_row, column=img_col_index)
        cell.value = None
        ws.add_image(img, f'{img_column_letter}{ws.max_row}')
    wb.save(excel_path)

if __name__ == '__main__':
    dic={"name":"deng","img_path":r"D:\code\yingdao_2\excel\1.jpg","age":23}
    f = open("1.json",'w',encoding="utf-8")
    f.write(json.dumps(dic,ensure_ascii=False)+"\n")
    f.write(json.dumps(dic,ensure_ascii=False)+"\n")
    f.write(json.dumps(dic,ensure_ascii=False)+"\n")
    f.flush()
    f.close()
    json_file="1.json"
    excel_file="1.xlsx"
    img_field="img_path"
    jsonfile_img2excel(json_file,excel_file,img_field)