python操作excel/单元格url图片下载/插入/定义图片大小/定义单元格大小

openpyxl/urllib

openpyxl == 3.0.7
urllib3 == 1.26.4

定义变量

import openpyxl
from openpyxl.drawing.image import Image
import os
from urllib.request import urlretrieve
from loguru import logger

excel_file_path = 'your/excel file/path'
sheetname = 'Sheet1'
urlcol = 'A' # url在A列
piccol = 'B' # 图片保存到B列
rows = 1 # 从第一行开始
image_path = './picture' # 图片保存位置

打开excel工作簿

wb = openpyxl.load_workbook(excel_file_path)  # 打开excel工作簿
ws = wb[sheetname]  # 获取活跃工作表
# 变量均以定义

开始遍历插入

for index, e in enumerate(ws[urlcol], start=rows):  # 取出第A列内容，从第1个算起
    img_url = e.value
    logger.info(f'{img_url}-{index}/{len(ws[urlcol])}')
    # 判断url列的规则，可自定义
    if img_url and ('.jpg' in img_url or '.png' in img_url): 
    	# 定义图片文件路径及文件名
        img_save_path = os.path.join(image_path,f'{index}.jpg')
        try:  # 因获取A列的第一行是标题，这里防止报错结束程序
        	# 下载图片到指定路径img_save_path 
            urlretrieve(img_url,img_save_path)
            img = Image(img_save_path) # 获取图片
            img.width, img.height = (120, 120)  # 设置图片大小
            # 调整表格列宽和行高
            ws.column_dimensions[piccol].width = 15  
            ws.row_dimensions[index].height = 90
            ws.add_image(img, anchor=piccol + str(index))  # 插入对应单元格
        except Exception as e:  
            logger.warning(e)
wb.save(excel_file_path)  # 保存