excel 通过openpyxl表格下载和插入图片

 1。 从表格下载图片

def download_excel_images(excel_path, url_path, output_dir):    
    # 设置路径
    os.makedirs(output_dir, exist_ok=True)
    position_log = []
    res = []
    # 方法2：用openpyxl记录单元格位置（需额外安装 pip install openpyxl）
    wb = load_workbook(excel_path)
    sheet = wb.active
    url_matrix = {}
    for i, img in enumerate(sheet._images):
        # 获取图片位置锚点
        anchor = img.anchor
        row_idx = anchor._from.row  # 0-based
        col_idx = anchor._from.col  # 0-based
        top_left_cell = f"{chr(65+anchor._from.col)}{anchor._from.row+1}"

        img_name = f"{sheet.title}_img_{i}.png"
        with open(os.path.join(output_dir, img_name), "wb") as f:
            f.write(img.ref.getvalue())
        
        position_log.append(f"{img_name} | 位置: {sheet.title}!{top_left_cell}") 
        img_data = Image.open(img.ref)
        img_url = upload_img(img_data)
        # 按行号分组，每行是一个dict
        if row_idx not in url_matrix:
            url_matrix[row_idx] = {}
        url_matrix[row_idx][col_idx] = img_url

    # pdb.set_trace()
    for row in sorted(url_matrix.keys()):
        row_urls = [url_matrix[row].get(col, "") for col in range(3)]
        res.append(row_urls)

    df = pd.DataFrame(res, columns=["图片1_url", "图片2_url", "图片3_url"])
    df.to_excel(url_path, index=False)

2. 通过图片链接将图片插入表格

def insert_img2excel(excel_path, url_path, output_dir):
    data = pd.read_excel(excel_path)
    # 创建一个工作簿
    wb = Workbook()

    # 获取默认的活动工作表
    ws = wb.active
    
    ws.column_dimensions['C'].width = 100
    i = 2
    for index, row in data.iterrows():
        try:
            response = requests.get(row["merge_image_url2"])
            img = response.content
            image_path = os.path.join(output_dir, hashlib.md5(img).hexdigest()[:5] + ".png")
            # with open(image_path, 'wb') as f:
            #     f.write(img)
            # pdb.set_trace()
        except Exception as e:
            continue
        img = Image.open(BytesIO(img))
        img.save(image_path)
        image_data = image.Image(image_path)
        image_data.width, image_data.height = 100, 100
        ws.add_image(image_data, "C" + str(i))
        ws.row_dimensions[i].height = 100
        image_path = image_path

        i = i + 1
    wb.save(url_path)