import os
import re
import sys
import tempfile
from datetime import datetime
import openpyxl
from openpyxl.drawing.image import Image as OpenpyxlImage
from openpyxl.utils import get_column_letter
from PIL import Image
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
from tkinter.font import Font
import threading
class ImageToExcelProcessor:
def __init__(self, root):
self.root = root
self.root.title("图片批量插入Excel工具")
self.root.geometry("750x500") # 增加高度以容纳新控件
self.root.configure(bg="#f0f0f0")
self.root.resizable(False, False)
# 自定义字体
self.title_font = Font(family="Microsoft YaHei", size=12, weight="bold")
self.label_font = Font(family="Microsoft YaHei", size=10)
self.button_font = Font(family="Microsoft YaHei", size=10, weight="bold")
self.status_font = Font(family="Microsoft YaHei", size=9)
# 创建主框架
main_frame = ttk.Frame(self.root, padding=15)
main_frame.pack(fill=tk.BOTH, expand=True)
# 标题
title_label = ttk.Label(
main_frame,
text="图片批量插入Excel工具",
font=self.title_font,
foreground="#2c3e50",
)
title_label.grid(row=0, column=0, columnspan=3, pady=(0, 20))
# 创建表单框架
form_frame = ttk.LabelFrame(main_frame, text="处理参数", padding=10)
form_frame.grid(row=1, column=0, columnspan=3, sticky="we", padx=5, pady=5)
# 图片根目录选择
ttk.Label(form_frame, text="图片目录:", font=self.label_font).grid(
row=0, column=0, padx=5, pady=10, sticky="e"
)
self.source_dir_var = tk.StringVar()
source_dir_entry = ttk.Entry(
form_frame, textvariable=self.source_dir_var, width=60, font=self.label_font
)
source_dir_entry.grid(row=0, column=1, padx=5, pady=5, sticky="we")
browse_source_btn = ttk.Button(
form_frame, text="浏览...", command=self.browse_source_dir
)
browse_source_btn.grid(row=0, column=2, padx=5, pady=5)
# Excel输出路径选择
current_time = datetime.now().strftime("%Y%m%d_%H%M%S")
default_filename = f"output_{current_time}.xlsx"
default_path = os.path.join(
os.path.expanduser("~"), "Desktop", default_filename
)
self.output_excel_var = tk.StringVar(value=default_path)
ttk.Label(form_frame, text="Excel输出路径:", font=self.label_font).grid(
row=1, column=0, padx=5, pady=10, sticky="e"
)
output_excel_entry = ttk.Entry(
form_frame,
textvariable=self.output_excel_var,
width=60,
font=self.label_font,
)
output_excel_entry.grid(row=1, column=1, padx=5, pady=5, sticky="we")
browse_excel_btn = ttk.Button(
form_frame, text="浏览...", command=self.browse_excel_output
)
browse_excel_btn.grid(row=1, column=2, padx=5, pady=5)
# 模板目录配置项（新增）
default_template_dir = os.path.join(os.path.expanduser("~"), "Desktop", "DPA")
self.template_dir_var = tk.StringVar(value=default_template_dir)
ttk.Label(form_frame, text="Excel模板文件:", font=self.label_font).grid(
row=2, column=0, padx=5, pady=10, sticky="e"
)
self.template_file_var = tk.StringVar()
template_file_entry = ttk.Entry(
form_frame,
textvariable=self.template_file_var,
width=60,
font=self.label_font,
)
template_file_entry.grid(row=2, column=1, padx=5, pady=5, sticky="we")
browse_template_btn = ttk.Button(
form_frame, text="浏览...", command=self.browse_template_file
)
browse_template_btn.grid(row=2, column=2, padx=5, pady=5)
# 按钮框架
button_frame = ttk.Frame(main_frame)
button_frame.grid(row=3, column=0, columnspan=3, pady=20)
# 执行按钮
self.process_btn = ttk.Button(
button_frame,
text="开始处理并生成Excel",
command=self.start_processing_thread,
width=25,
style="Accent.TButton",
)
self.process_btn.pack(pady=5)
# 状态标签
status_frame = ttk.Frame(main_frame)
status_frame.grid(row=4, column=0, columnspan=3, sticky="we", pady=10)
self.status_var = tk.StringVar(
value="准备就绪：请选择图片目录、模板目录和Excel输出路径"
)
status_label = ttk.Label(
status_frame,
textvariable=self.status_var,
font=self.status_font,
foreground="#3498db",
wraplength=650,
anchor="center",
)
status_label.pack(fill=tk.X, padx=10)
# 进度条
self.progress = ttk.Progressbar(
status_frame, orient="horizontal", length=650, mode="determinate"
)
self.progress.pack(pady=5)
# 版权信息
footer_frame = ttk.Frame(main_frame)
footer_frame.grid(row=5, column=0, columnspan=3, pady=(10, 0))
ttk.Label(
footer_frame,
text="Developed by ICT ",
font=("Microsoft YaHei", 8),
foreground="#7f8c8d",
).pack()
# 配置样式
self.configure_styles()
# 临时文件列表
self.temp_files = []
def configure_styles(self):
"""配置自定义样式"""
style = ttk.Style()
style.configure("TFrame", background="#f0f0f0")
style.configure("TLabelframe", background="#ffffff", borderwidth=1)
style.configure("TLabelframe.Label", font=self.title_font)
# 主按钮样式
style.configure(
"Accent.TButton",
font=self.button_font,
foreground="black",
background="#3498db",
padding=8,
)
style.map(
"Accent.TButton",
background=[("active", "#2980b9"), ("disabled", "#bdc3c7")],
)
# 状态标签样式
style.configure("Red.TLabel", foreground="#e74c3c")
style.configure("Green.TLabel", foreground="#27ae60")
# 进度条样式
style.configure(
"Custom.Horizontal.TProgressbar",
thickness=16,
background="#2ecc71",
troughcolor="#ecf0f1",
)
def browse_template_file(self):
"""浏览并选择Excel模板文件"""
file_path = filedialog.askopenfilename(
filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")],
title="选择Excel模板文件",
)
if file_path:
self.template_file_var.set(file_path)
def browse_source_dir(self):
"""浏览并选择图片源目录"""
dir_path = filedialog.askdirectory(title="选择图片目录")
if dir_path:
self.source_dir_var.set(dir_path)
def browse_excel_output(self):
"""浏览并选择Excel输出路径"""
file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")],
title="保存Excel文件",
)
if file_path:
self.output_excel_var.set(file_path)
def browse_template_dir(self):
"""浏览并选择模板目录（新增方法）"""
dir_path = filedialog.askdirectory(title="选择模板目录")
if dir_path:
self.template_dir_var.set(dir_path)
def natural_sort_key(self, s):
"""自然排序键函数，用于按数字顺序排序文件名"""
return [
int(text) if text.isdigit() else text.lower()
for text in re.split(r"(\d+)", s)
]
def find_template_file(self, template_code):
"""根据模板代码查找模板文件（使用配置的模板目录）"""
template_dir = self.template_dir_var.get()
if not os.path.isdir(template_dir):
raise FileNotFoundError(f"模板目录不存在: {template_dir}")
# 在模板目录中查找匹配的模板文件
for filename in os.listdir(template_dir):
if filename.startswith(template_code) and filename.lower().endswith(
".xlsx"
):
return os.path.join(template_dir, filename)
return None
def insert_images_to_template(self, template_path, output_path, image_data):
"""将图片插入Excel模板的指定位置"""
temp_files = []
try:
# 加载模板
wb = openpyxl.load_workbook(template_path)
# Excel单位转换常量
EXCEL_DPI = 96
CM_TO_INCH = 0.3937
TARGET_WIDTH_CM = 7.39
TARGET_HEIGHT_CM = 5.77
# 计算目标尺寸
target_width_inch = TARGET_WIDTH_CM * CM_TO_INCH
target_height_inch = TARGET_HEIGHT_CM * CM_TO_INCH
target_width_px = target_width_inch * EXCEL_DPI
target_height_px = target_height_inch * EXCEL_DPI
total_images = sum(len(images) for _, _, images in image_data)
self.progress["maximum"] = total_images
self.progress["value"] = 0
processed_images = 0
# 处理每个图片组
for sheet_name, start_cell, direction, image_paths in image_data:
if sheet_name not in wb.sheetnames:
self.status_var.set(f"警告: 工作表 '{sheet_name}' 不存在，跳过")
self.root.update()
continue
ws = wb[sheet_name]
col, row = self.parse_cell_reference(start_cell)
# 处理每个图片
for image_path in image_paths:
try:
# 创建临时文件
temp_img_path = os.path.join(
tempfile.gettempdir(),
f"excel_temp_{os.path.basename(image_path)}_{datetime.now().strftime('%H%M%S%f')}.png",
)
with Image.open(image_path) as img:
# 计算缩放比例
width_ratio = target_width_px / img.width
height_ratio = target_height_px / img.height
scale_ratio = min(width_ratio, height_ratio)
# 缩放图片
scaled_width = int(img.width * scale_ratio)
scaled_height = int(img.height * scale_ratio)
# 保存临时文件
img.resize((scaled_width, scaled_height)).save(
temp_img_path, "PNG"
)
temp_files.append(temp_img_path)
# 插入图片到Excel
excel_img = OpenpyxlImage(temp_img_path)
cell_ref = f"{get_column_letter(col)}{row}"
ws.add_image(excel_img, cell_ref)
# 更新状态
processed_images += 1
self.progress["value"] = processed_images
self.status_var.set(
f"插入图片: {os.path.basename(image_path)} 到 {sheet_name}!{cell_ref}"
)
self.root.update()
# 移动到下一个单元格
if direction == "H":
col += 1 # 横向插入
else:
row += 1 # 纵向插入
except Exception as e:
self.status_var.set(
f"处理失败 {os.path.basename(image_path)}: {str(e)}"
)
self.root.update()
# 保存Excel文件
if os.path.exists(output_path):
name, ext = os.path.splitext(output_path)
timestamp = datetime.now().strftime("%H%M%S")
output_path = f"{name}_{timestamp}{ext}"
wb.save(output_path)
self.status_var.set(f"Excel文件已保存: {output_path}")
return output_path, processed_images, len(image_data), temp_files
except Exception as e:
raise RuntimeError(f"生成Excel失败: {str(e)}")
def parse_cell_reference(self, cell_ref):
"""解析单元格引用（如 'C3'）为列索引和行号"""
# 拆分列字母和行号
col_part = "".join(filter(str.isalpha, cell_ref))
row_part = "".join(filter(str.isdigit, cell_ref))
if not col_part or not row_part:
raise ValueError(f"无效的单元格引用: {cell_ref}")
# 转换列字母为数字索引
col_index = 0
for char in col_part.upper():
col_index = col_index * 26 + (ord(char) - ord("A") + 1)
return col_index, int(row_part)
def process_images(self, source_dir):
"""处理图片：收集图片并组织成数据结构"""
image_data = [] # 存储元组(sheet_name, start_cell, direction, image_paths)
# 遍历第一级目录（包含模板代码的目录）
for template_dir in os.listdir(source_dir):
template_path = os.path.join(source_dir, template_dir)
if not os.path.isdir(template_path):
continue
# 遍历第二级目录（sheet名称）
for sheet_dir in os.listdir(template_path):
sheet_path = os.path.join(template_path, sheet_dir)
if not os.path.isdir(sheet_path):
continue
# 遍历第三级目录（单元格定位）
for cell_dir in os.listdir(sheet_path):
cell_path = os.path.join(sheet_path, cell_dir)
if not os.path.isdir(cell_path):
continue
# 解析单元格和方向（例如：C3-H）
if "-" not in cell_dir:
self.status_var.set(
f"警告: 无效的目录名格式 '{cell_dir}', 应为 '单元格-方向'"
)
continue
cell_ref, direction = cell_dir.rsplit("-", 1)
direction = direction.upper()
if direction not in ["H", "S"]:
self.status_var.set(
f"警告: 无效的方向 '{direction}', 应为 'H' 或 'S'"
)
continue
# 收集图片
image_paths = []
for filename in os.listdir(cell_path):
file_path = os.path.join(cell_path, filename)
if os.path.isfile(file_path) and filename.lower().endswith(
(".png", ".jpg", ".jpeg", ".gif", ".bmp", ".tiff", ".webp")
):
image_paths.append(file_path)
if image_paths:
# 按自然顺序排序图片
image_paths.sort(
key=lambda x: self.natural_sort_key(os.path.basename(x))
)
image_data.append((sheet_dir, cell_ref, direction, image_paths))
return image_data, template_code
def start_processing_thread(self):
"""启动处理线程，避免界面冻结"""
self.process_btn.config(state=tk.DISABLED)
self.status_var.set("开始处理...")
self.progress["value"] = 0
self.root.update()
# 启动后台处理线程
processing_thread = threading.Thread(target=self.process_and_generate)
processing_thread.daemon = True
processing_thread.start()
def process_and_generate(self):
"""主流程：处理图片→生成Excel"""
source_dir = self.source_dir_var.get()
excel_output = self.output_excel_var.get()
template_file = self.template_file_var.get() # 获取模板文件路径
temp_files = []
try:
# 验证输入
if not source_dir:
raise ValueError("请选择图片目录")
if not os.path.isdir(source_dir):
raise FileNotFoundError(f"图片目录不存在: {source_dir}")
if not excel_output:
raise ValueError("请选择Excel输出路径")
if not template_file:
raise ValueError("请选择Excel模板文件")
if not os.path.isfile(template_file):
raise FileNotFoundError(f"模板文件不存在: {template_file}")
# 处理图片并组织数据结构
self.status_var.set("正在解析图片目录结构...")
self.root.update()
image_data = self.process_images(source_dir) # 修改为只返回image_data
if not image_data:
raise ValueError("未找到任何有效的图片数据")
# 生成Excel
self.status_var.set("正在将图片插入Excel模板...")
self.root.update()
excel_path, img_count, group_count, excel_temp_files = (
self.insert_images_to_template(template_file, excel_output, image_data)
)
temp_files.extend(excel_temp_files)
# 完成处理并显示结果
self.status_var.set("处理完成！")
messagebox.showinfo(
"成功",
f"图片处理与Excel生成完成！\n\n"
f"图片目录: {source_dir}\n"
f"使用模板: {os.path.basename(template_file)}\n"
f"Excel文件位置: {excel_path}\n"
f"插入图片数量: {img_count}张\n"
f"处理的分组数量: {group_count}组",
)
except Exception as e:
self.status_var.set(f"处理出错: {str(e)}")
messagebox.showerror("错误", f"处理过程中发生错误:\n{str(e)}")
finally:
# 清理临时文件
for temp_file in temp_files:
try:
if os.path.exists(temp_file):
os.remove(temp_file)
except:
pass
self.process_btn.config(state=tk.NORMAL)
self.progress["value"] = 0
if __name__ == "__main__":
root = tk.Tk()
if sys.platform == "win32":
import ctypes
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID("ImageProcessor")
app = ImageToExcelProcessor(root)
root.mainloop()
代碼似乎不太對，我現在圖片路徑C:\Users\U180705\Desktop\T0 5891110BU1 25899011-01-00\1Bump\C3-H 。模板路徑C:\Users\U180705\Desktop\DPA\空白模板\T0.xlsx. T0 5891110BU1 25899011-01-00 的前兩碼是模板的excel的名稱。1Bump 是模板裡面的sheet名稱。C3-H 是定位到這個1Bump sheet的C3單元格，H表示橫向對C3-H文件夾下的圖片進行放置。即C3,C4,C5 單元格放置
最新发布
本文介绍如何使用 FileColumn 进行图片上传，并详细解释了如何验证文件格式、文件大小及图片尺寸。同时提供了常见错误处理方法及图片路径获取技巧。
扫一扫
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
