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.activeurl_matrix = {}for i, img in enumerate(sheet._images):# 获取图片位置锚点anchor = img.anchorrow_idx = anchor._from.row  # 0-basedcol_idx = anchor._from.col  # 0-basedtop_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)# 按行号分组，每行是一个dictif 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.activews.column_dimensions['C'].width = 100i = 2for index, row in data.iterrows():try:response = requests.get(row["merge_image_url2"])img = response.contentimage_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:continueimg = Image.open(BytesIO(img))img.save(image_path)image_data = image.Image(image_path)image_data.width, image_data.height = 100, 100ws.add_image(image_data, "C" + str(i))ws.row_dimensions[i].height = 100image_path = image_pathi = i + 1wb.save(url_path)