import os
import requests
import xlwings as xw
from io import BytesIO
from PIL import Image, ImageOps
from tkinter import filedialog, messagebox, ttk
import tkinter as tk
from openpyxl import load_workbook
from concurrent.futures import ThreadPoolExecutor, as_completed
# 下载并保存单张图片（带验证，安全保存）
def download_image(url, path):
try:
print(f"📥 正在下载图片：{url}")
response = requests.get(url, timeout=10)
img = Image.open(BytesIO(response.content))
img.verify() # 验证图片是否损坏
img = Image.open(BytesIO(response.content)) # 必须重新加载
with open(path, 'wb') as f:
img.save(f, format='PNG')
f.flush()
os.fsync(f.fileno())
print(f"✅ 保存成功：{path}")
return True
except Exception as e:
print(f"❌ 下载失败：{url}，原因：{e}")
return False
# 多线程批量下载图片
def download_images_multithreaded(url_map, folder):
downloaded = {}
with ThreadPoolExecutor(max_workers=5) as executor:
future_to_row = {
executor.submit(download_image, url, os.path.join(folder, f"img_{row}.png")): row
for row, url in url_map.items()
}
for future in as_completed(future_to_row):
row = future_to_row[future]
try:
success = future.result()
if success:
downloaded[row] = os.path.join(folder, f"img_{row}.png")
except Exception as e:
print(f"❌ 第 {row} 行下载任务异常: {e}")
return downloaded
# 获取最大有内容的行数
def get_max_data_row(file_path, sheet_name, column_letter):
wb = load_workbook(file_path, read_only=True)
ws = wb[sheet_name]
for row in reversed(range(1, ws.max_row + 1)):
if ws[f"{column_letter}{row}"].value:
return row
return 0
# 处理单个Sheet的图片插入
def process_excel(file_path, sheet_name, column_letter, start_row, progress_widget=None, label_widget=None,
max_img_width=200, max_img_height=200):
import tempfile
from PIL import Image
# 兼容 Pillow 新旧版本的重采样参数
try:
resample_method = Image.Resampling.LANCZOS
except AttributeError:
resample_method = Image.ANTIALIAS
app = xw.App(visible=False)
wb = app.books.open(file_path)
ws = wb.sheets[sheet_name]
sheet_folder = os.path.join("images", sheet_name)
os.makedirs(sheet_folder, exist_ok=True)
max_row = get_max_data_row(file_path, sheet_name, column_letter)
# 收集下载链接
url_map = {}
for row in range(start_row, max_row + 1):
cell = ws.range(f"{column_letter}{row}")
if cell.value:
url_map[row] = cell.value
# 多线程下载图片
downloaded_images = download_images_multithreaded(url_map, sheet_folder)
for row in range(start_row, max_row + 1):
if progress_widget:
progress_value = row - start_row + 1
progress_widget["value"] = progress_value
progress_widget.update()
if label_widget:
label_widget.config(text=f"正在处理 [{sheet_name}] 第 {row} 行 / 共 {max_row} 行")
label_widget.update()
if row in downloaded_images:
img_path = os.path.abspath(downloaded_images[row])
print(f"🖼️ 正在插入图片：{img_path}（存在：{os.path.exists(img_path)}）")
try:
cell = ws.range(f"{column_letter}{row}")
left = cell.left
top = cell.top
img = Image.open(img_path)
img_width, img_height = img.size
# 计算缩放比例，保持图片比例
scale_ratio = min(max_img_width / img_width, max_img_height / img_height)
new_width = int(img_width * scale_ratio)
new_height = int(img_height * scale_ratio)
resized_img = img.resize((new_width, new_height), resample_method)
# 新建白底背景，尺寸固定为 max_img_width x max_img_height
canvas = Image.new("RGB", (max_img_width, max_img_height), (255, 255, 255))
paste_x = (max_img_width - new_width) // 2
paste_y = (max_img_height - new_height) // 2
canvas.paste(resized_img, (paste_x, paste_y))
with tempfile.NamedTemporaryFile(delete=False, suffix=".png") as temp_file:
canvas.save(temp_file.name)
temp_img_path = temp_file.name
# 调整单元格宽高以匹配图片尺寸，增加10%的边距
cell_width = new_width * 0.14 * 1.1 # Excel列宽单位转换，增加10%边距
cell_height = new_height * 0.75 * 1.1 # Excel行高单位转换，增加10%边距
ws.range(f"{column_letter}{row}").column_width = cell_width
ws.range(f"{column_letter}{row}").row_height = cell_height
# 插入图片，并设置图片大小与调整后的单元格匹配
pic = ws.pictures.add(
temp_img_path,
left=left,
top=top,
width=new_width,
height=new_height
)
# 设置图片随单元格移动但不随单元格调整大小
pic.api.Placement = 1 # 1 = xlMove (图片随单元格移动)
print(f"✅ 插入成功：[{sheet_name}] 第 {row} 行")
os.remove(temp_img_path)
except Exception as e:
print(f"❌ 插入失败（{sheet_name} 第 {row} 行）：{e}")
output_path = file_path.replace(".xlsx", f"_{sheet_name}_带图.xlsx")
wb.save(output_path)
wb.close()
app.quit()
return output_path
# 批量处理多个sheet
def batch_process(file_path, sheet_names, column_letter, start_row):
results = []
total_sheets = len(sheet_names)
for idx, sheet in enumerate(sheet_names, 1):
progress_label.config(text=f"处理第 {idx}/{total_sheets} 个Sheet：{sheet}")
progress_label.update()
progress["value"] = 0
max_row = get_max_data_row(file_path, sheet, column_letter)
progress["maximum"] = max_row - start_row + 1 if max_row >= start_row else 1
output = process_excel(file_path, sheet, column_letter, start_row, progress_widget=progress,
label_widget=progress_label)
results.append(output)
return results
# GUI逻辑
def start_process():
file_path = file_var.get()
sheet_names_str = sheet_var.get()
column_letter = column_var.get().upper()
try:
start_row = int(start_row_var.get())
except:
messagebox.showerror("错误", "起始行必须是数字")
return
if not os.path.exists(file_path):
messagebox.showerror("错误", "文件路径无效")
return
sheet_names = [s.strip() for s in sheet_names_str.split(",") if s.strip()]
if not sheet_names:
messagebox.showerror("错误", "请输入至少一个Sheet名称")
return
try:
outputs = batch_process(file_path, sheet_names, column_letter, start_row)
msg = "图片插入完成，生成文件列表：\n" + "\n".join(outputs)
messagebox.showinfo("完成", msg)
except Exception as e:
messagebox.showerror("出错", str(e))
def choose_file():
file_path = filedialog.askopenfilename(filetypes=[("Excel 文件", "*.xlsx")])
file_var.set(file_path)
# 创建图形界面
root = tk.Tk()
root.title("Excel 图片插入工具")
root.option_add("*Font", "SimHei 10")
file_var = tk.StringVar()
sheet_var = tk.StringVar(value="Sheet1")
column_var = tk.StringVar(value="A")
start_row_var = tk.StringVar(value="2")
tk.Label(root, text="选择 Excel 文件：").grid(row=0, column=0, sticky='e', padx=10, pady=10)
tk.Entry(root, textvariable=file_var, width=50).grid(row=0, column=1, padx=5, pady=10)
tk.Button(root, text="浏览", command=choose_file).grid(row=0, column=2, padx=10, pady=10)
tk.Label(root, text="Sheet 名称（多个用逗号分隔）：").grid(row=1, column=0, sticky='e', padx=10, pady=10)
tk.Entry(root, textvariable=sheet_var).grid(row=1, column=1, padx=5, pady=10)
tk.Label(root, text="图片列（如 B）：").grid(row=2, column=0, sticky='e', padx=10, pady=10)
tk.Entry(root, textvariable=column_var).grid(row=2, column=1, padx=5, pady=10)
tk.Label(root, text="起始行号（如 2）：").grid(row=3, column=0, sticky='e', padx=10, pady=10)
tk.Entry(root, textvariable=start_row_var).grid(row=3, column=1, padx=5, pady=10)
tk.Button(
root,
text="开始处理",
command=start_process,
bg="#4CAF50",
fg="white",
font=("SimHei", 12, "bold"),
width=15
).grid(row=4, column=0, columnspan=3, pady=20)
progress_label = tk.Label(root, text="")
progress_label.grid(row=5, column=0, columnspan=3, pady=5)
progress = ttk.Progressbar(
root,
orient="horizontal",
length=400,
mode="determinate"
)
progress.grid(row=6, column=0, columnspan=3, pady=5)
copyright_label = tk.Label(
root,
text="© 2025 Project Name: Excel图片插入工具 Author: songyuting. All rights reserved.",
font=("SimHei", 8)
)
copyright_label.grid(row=7, column=0, columnspan=3, pady=10, sticky='s')
root.grid_rowconfigure(7, weight=1)
root.grid_columnconfigure(1, weight=1)
root.mainloop() 我需要保留不规则图片处理及保留图片清晰度的部分，使插入的每个图片的宽度和高度不超出单元格，我的目的是让图片随单元格变化大小及位置
最新发布
本文介绍如何使用 Worksheet.get_Range 方法获取 Microsoft Office Interop Excel 中的单元格或单元格范围。此方法已被 Range 属性替代，但了解其用法对于理解早期代码或特定需求仍有帮助。
扫一扫
595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
