将Excel中的图片链接替换为图片

使用前提：

• 第一行不能是合并单元格，openpyxl处理会出现问题
• openpyxl version > 3.0
• 如果图片地址不是以.jpg结尾，需要更改requests部分代码

from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import requests

wb =load_workbook("example.xlsx")
ws = wb["sheet1"]
file_path = "E:\Jupyter\向Excel中插入图片\img\{}.jpg"

location = []  # 存放坐标列表

for row in ws.rows:
    for cell in row:
        if cell.value and str(cell.value).startswith("http://"):
            location.append(cell.coordinate) # 返回坐标
            pic= requests.get(cell.value)
            cell.value = ""     # 删除原有链接数据
            with open(file_path.format(cell.coordinate),"wb") as f:
                f.write(pic.content)
                
for coordinate in location:   
    file_path = "E:\Jupyter\向Excel中插入图片\img\{}.jpg".format(coordinate)
       
    img = Image(file_path)
    newsize =(112.5,150) # 设置图片大小
    img.width, img.height = newsize
    
    column = "".join(re.findall(r'[A-Za-z]', coordinate)) # 列的位置
    row = int(re.sub("\D", "", coordinate) ) # 行的位置
    
    ws.column_dimensions[column].width = 13.5 # 设置列宽
    ws.row_dimensions[row].height = 114 # 设置行高
    
    ws.add_image(img, coordinate)
    
wb.save("result.xlsx")