import os
import shutil
import re
import sys
import tempfile
from datetime import datetime
import openpyxl
from openpyxl.drawing.image import Image as OpenpyxlImage
from PIL import Image as PILImage, UnidentifiedImageError
import tkinter as tk
from tkinter import filedialog, messagebox
class ImageProcessorAndExcelGenerator:
def __init__(self, root):
self.root = root
self.root.title("图片批量重命名与Excel导入工具")
self.root.geometry("650x320")
self.font = ("SimHei", 10)
self.create_widgets()
self.temp_files = []
def create_widgets(self):
# 原始图片根目录选择
tk.Label(self.root, text="图片目录:", font=self.font).grid(
row=0, column=0, padx=5, pady=10, sticky="w"
)
self.source_dir_var = tk.StringVar()
tk.Entry(
self.root, textvariable=self.source_dir_var, width=50, font=self.font
).grid(row=0, column=1, padx=5, pady=10)
tk.Button(
self.root, text="浏览...", command=self.browse_source_dir, font=self.font
).grid(row=0, column=2, padx=5, pady=10)
# Excel输出路径选择
current_time = datetime.now().strftime("%Y%m%d_%H%M%S")
default_filename = f"图片汇总_{current_time}.xlsx"
default_path = os.path.join(
os.path.expanduser("~"), "Desktop", default_filename
)
self.output_excel_var = tk.StringVar(value=default_path)
tk.Label(self.root, text="Excel输出路径:", font=self.font).grid(
row=1, column=0, padx=5, pady=10, sticky="w"
)
tk.Entry(
self.root, textvariable=self.output_excel_var, width=50, font=self.font
).grid(row=1, column=1, padx=5, pady=10)
tk.Button(
self.root, text="浏览...", command=self.browse_excel_output, font=self.font
).grid(row=1, column=2, padx=5, pady=10)
# 执行按钮
tk.Button(
self.root,
text="开始处理并生成Excel",
command=self.process_and_generate,
width=25,
height=2,
font=("SimHei", 12, "bold"),
).grid(row=2, column=0, columnspan=3, pady=20)
# 状态标签
self.status_var = tk.StringVar(
value="准备就绪：请选择原始图片根目录和Excel输出路径"
)
tk.Label(
self.root,
textvariable=self.status_var,
fg="blue",
font=self.font,
wraplength=600,
).grid(row=3, column=0, columnspan=3, padx=10)
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):
file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")],
title="保存Excel文件",
)
if file_path:
self.output_excel_var.set(file_path)
def process_and_copy_images(self, source_dir):
"""处理图片：按路径层级重命名并复制到output子文件夹"""
output_dir = os.path.join(source_dir, "output")
image_extensions = {
".jpg",
".jpeg",
".png",
".gif",
".bmp",
".tiff",
".webp",
".jfif",
}
processed_count = 0
skipped_count = 0
os.makedirs(output_dir, exist_ok=True)
self.status_var.set(f"创建输出目录: {output_dir}")
self.root.update()
for root, _, files in os.walk(source_dir):
if root.startswith(output_dir):
continue
for filename in files:
ext = os.path.splitext(filename)[1].lower()
if ext in image_extensions:
file_path = os.path.join(root, filename)
relative_path = os.path.relpath(root, source_dir)
# 生成新文件名
if relative_path == ".":
new_filename = filename
else:
path_prefix = relative_path.replace(os.path.sep, "-")
new_filename = f"{path_prefix}-{filename}"
# 处理重名文件
output_path = os.path.join(output_dir, new_filename)
counter = 1
while os.path.exists(output_path):
name, ext = os.path.splitext(new_filename)
new_filename = f"{name}_{counter}{ext}"
output_path = os.path.join(output_dir, new_filename)
counter += 1
# 复制文件
try:
shutil.copy2(file_path, output_path)
processed_count += 1
self.status_var.set(
f"处理图片 {processed_count}: {new_filename}"
)
self.root.update()
except Exception as e:
self.status_var.set(f"处理失败 {filename}: {str(e)}")
self.root.update()
skipped_count += 1
else:
skipped_count += 1
return output_dir, processed_count, skipped_count
def natural_sort_key(self, s):
sub_strings = re.split(r"(\d+)", s)
return [int(c) if c.isdigit() else c for c in sub_strings]
def generate_excel_from_images(self, image_folder, output_excel_path):
"""从处理后的图片文件夹生成Excel"""
temp_files = []
wb = openpyxl.Workbook()
ws = wb.active
cm_to_pixels = 96 / 2.54 # 单位转换：厘米到像素
img_width = int(7.2 * cm_to_pixels) # 7.2厘米宽
img_height = int(5.4 * cm_to_pixels) # 5.4厘米高
start_row, start_col = 2, 1
current_row, current_col = start_row, start_col
# 获取并筛选图片文件
all_files = os.listdir(image_folder)
image_extensions = (
".png",
".jpg",
".jpeg",
".gif",
".bmp",
".tiff",
".webp",
".jfif",
)
image_files = [f for f in all_files if f.lower().endswith(image_extensions)]
if not image_files:
raise ValueError(f"处理后的图片文件夹中未找到图片: {image_folder}")
# 按文件名分组（假设格式为"数字-..."）
image_groups = {}
for image_file in image_files:
try:
first_part = image_file.split("-")[0]
if not first_part.isdigit():
raise ValueError(f"前缀不是数字: {first_part}")
group_key = int(first_part)
except (IndexError, ValueError) as e:
raise ValueError(
f"文件名格式错误: '{image_file}'，{str(e)}。正确格式应为'路径前缀-原文件名'"
)
if group_key not in image_groups:
image_groups[group_key] = []
image_groups[group_key].append(image_file)
sorted_groups = sorted(image_groups.items(), key=lambda x: x[0])
if not sorted_groups:
raise ValueError("未成功分组任何图片文件，请检查文件名格式")
# 处理每个分组
total_groups = len(sorted_groups)
for group_idx, (group, files) in enumerate(sorted_groups, 1):
self.status_var.set(f"处理分组 {group}（{group_idx}/{total_groups}）")
self.root.update()
sorted_files = sorted(files, key=self.natural_sort_key)
for file_idx, image_file in enumerate(sorted_files, 1):
self.status_var.set(
f"插入图片 {file_idx}/{len(sorted_files)}（分组 {group}）"
)
self.root.update()
image_path = os.path.join(image_folder, image_file)
# 处理图片尺寸
with PILImage.open(image_path) as img:
if img.width == 0 or img.height == 0:
raise ValueError(f"图片尺寸无效: {image_file}")
img_resized = img.resize((img_width, img_height))
# 保存临时图片
temp_img_path = os.path.join(
tempfile.gettempdir(), f"temp_{group}_{file_idx}.png"
)
temp_files.append(temp_img_path)
img_resized.save(temp_img_path)
# 插入Excel
img_name = os.path.splitext(image_file)[0]
ws.cell(row=current_row - 1, column=current_col, value=img_name)
excel_img = OpenpyxlImage(temp_img_path)
ws.add_image(
excel_img, ws.cell(row=current_row, column=current_col).coordinate
)
# 设置行列尺寸
col_letter = openpyxl.utils.get_column_letter(current_col)
ws.column_dimensions[col_letter].width = img_width / 6.5
ws.row_dimensions[current_row].height = img_height * 0.75
current_col += 1
current_row += 2 # 分组间空行
current_col = start_col
# 保存Excel
if os.path.exists(output_excel_path):
name, ext = os.path.splitext(output_excel_path)
output_excel_path = f"{name}_{datetime.now().strftime('%H%M%S')}{ext}"
self.status_var.set(
f"文件已存在，自动重命名为: {os.path.basename(output_excel_path)}"
)
self.root.update()
wb.save(output_excel_path)
return output_excel_path, len(image_files), total_groups
def process_and_generate(self):
"""主流程：处理图片→生成Excel"""
original_root = self.source_dir_var.get()
excel_output = self.output_excel_var.get()
temp_files = []
try:
# 验证输入
if not original_root:
raise ValueError("请选择原始图片根目录")
if not os.path.isdir(original_root):
raise FileNotFoundError(f"原始目录不存在: {original_root}")
if not excel_output:
raise ValueError("请选择Excel输出路径")
# 步骤1：处理图片
output_img_dir, processed, skipped = self.process_and_copy_images(
original_root
)
if processed == 0:
raise ValueError("未找到任何可处理的图片文件")
# 步骤2：生成Excel
self.status_var.set("开始生成Excel文件...")
self.root.update()
excel_path, img_count, group_count = self.generate_excel_from_images(
output_img_dir, excel_output
)
# 完成
self.status_var.set("全部处理完成！")
messagebox.showinfo(
"成功",
f"图片处理与Excel生成完成！\n"
f"原始目录: {original_root}\n"
f"处理后图片: {output_img_dir}（{processed}张）\n"
f"Excel文件: {excel_path}\n"
f"共插入 {img_count} 张图片，分为 {group_count} 组",
)
except Exception as e:
self.status_var.set(f"处理失败: {str(e)}")
messagebox.showerror("错误", str(e))
finally:
# 清理临时文件
for temp_file in temp_files:
if os.path.exists(temp_file):
try:
os.remove(temp_file)
except:
pass
if __name__ == "__main__":
root = tk.Tk()
app = ImageProcessorAndExcelGenerator(root)
root.mainloop()
将界面优化一下，更美观。给我一个完整的代码
最新发布
本文介绍了如何在Excel中操作，通过选择图片工具，点击更多选项，选取取图片主色调功能，并将颜色保存到指定单元格，以此实现快速调整图片色调。这一方法适用于需要统一图片风格的工作场景。
扫一扫
623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
