[Python] 纯文本查看 复制代码
import pandas as pd
import openpyxl
from openpyxl.utils import get_column_letter
from copy import copy
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
from PIL import Image, ImageTk
import os
import sys
import time
import threading
import subprocess
def resource_path(relative_path):
""" 获取资源绝对路径，兼容开发环境和 PyInstaller 打包环境 """
try:
# PyInstaller 创建临时文件夹并存储路径在 _MEIPASS
base_path = sys._MEIPASS
except Exception:
base_path = os.path.abspath(".")
return os.path.join(base_path, relative_path)
class GeneralExcelGenerator:
def __init__(self, root):
self.root = root
self.root.title("通用Excel模板批量填充工具")
self.root.geometry("1000x800") # 调高窗口以容纳图片
self.template_path = tk.StringVar()
self.data_path = tk.StringVar()
self.output_path = tk.StringVar()
# 缓存原图用于弹出预览
self.full_image = None
self.setup_ui()
def setup_ui(self):
# 创建 Notebook (标签页控件)
self.notebook = ttk.Notebook(self.root)
self.notebook.pack(fill="both", expand=True, padx=10, pady=10)
# 创建两个 Tab
self.tab_operate = ttk.Frame(self.notebook)
self.tab_manual = ttk.Frame(self.notebook)
self.notebook.add(self.tab_operate, text=" 操作界面 ")
self.notebook.add(self.tab_manual, text=" 使用说明 ")
self.create_operate_widgets()
self.create_manual_widgets()
def create_operate_widgets(self):
"""第一个Tab：核心操作区域"""
style = ttk.Style()
style.configure("Accent.TButton", font=("微软雅黑", 11, "bold"))
# 1. 模板配置
frame1 = ttk.LabelFrame(self.tab_operate, text="Step 1: 模板配置", padding="10")
frame1.pack(fill="x", padx=20, pady=10)
ttk.Entry(frame1, textvariable=self.template_path, width=70).grid(row=0, column=0, padx=5)
ttk.Button(frame1, text="选择模板", command=self.select_template).grid(row=0, column=1, padx=5)
# 2. 数据源配置
frame2 = ttk.LabelFrame(self.tab_operate, text="Step 2: 数据源配置", padding="10")
frame2.pack(fill="x", padx=20, pady=10)
ttk.Entry(frame2, textvariable=self.data_path, width=70).grid(row=0, column=0, padx=5)
ttk.Button(frame2, text="选择数据", command=self.select_data).grid(row=0, column=1, padx=5)
# 3. 输出路径
frame3 = ttk.LabelFrame(self.tab_operate, text="Step 3: 保存路径", padding="10")
frame3.pack(fill="x", padx=20, pady=10)
ttk.Entry(frame3, textvariable=self.output_path, width=70).grid(row=0, column=0, padx=5)
# 4. 执行按钮
btn_frame = ttk.Frame(self.tab_operate)
btn_frame.pack(pady=15)
self.generate_btn = ttk.Button(
btn_frame, text="开始批量填充生成",
command=self.start_generate_thread,
style="Accent.TButton", width=25
)
self.generate_btn.pack()
# 5. 进度反馈
self.progress_bar = ttk.Progressbar(self.tab_operate, orient="horizontal", length=800, mode="determinate")
self.progress_bar.pack(pady=10)
self.progress_var = tk.StringVar(value="准备就绪")
ttk.Label(self.tab_operate, textvariable=self.progress_var, font=("微软雅黑", 10), foreground="#2E8B57").pack()
def create_manual_widgets(self):
"""第二个Tab：带说明文字和图片预览"""
paned = tk.PanedWindow(self.tab_manual, orient=tk.VERTICAL)
paned.pack(fill="both", expand=True)
# 上半部分：文字说明区域
text_frame = ttk.Frame(paned)
paned.add(text_frame, height=300)
scrollbar = ttk.Scrollbar(text_frame)
scrollbar.pack(side=tk.RIGHT, fill="y")
manual_text = (
"【通用Excel模板批量填充工具 - 使用手册】\n\n"
"1. 工作原理：\n"
" 本工具通过读取【数据文件】（Excel）中的每一行信息，将其填充到【模板文件】（Excel）\n"
" 并为每一行数据生成一个独立的 Sheet（工作表）。\n\n"
"2. 模板准备：\n"
" - 在 Excel 模板的单元格中输入 {{字段名}}（注意是双大括号）。\n"
" - 这里的“字段名”必须与数据文件中的“列标题”完全一致。\n\n"
"3. 数据源准备：\n"
" - 数据文件第一行应为表头（列名）。\n"
" - 【第一列】的值将被用作生成后 Sheet 的名称。\n\n"
"4. 注意事项：\n"
" - 生成过程中请勿打开正在使用的模板文件或目标输出文件。\n"
" - (提示：点击下方图片可查看原图解)\n"
)
self.text_area = tk.Text(
text_frame,
wrap=tk.WORD,
font=("微软雅黑", 10),
yscrollcommand=scrollbar.set,
padx=20,
pady=10,
bg="#F9F9F9"
)
self.text_area.insert(tk.END, manual_text)
self.text_area.config(state=tk.DISABLED)
self.text_area.pack(fill="both", expand=True)
scrollbar.config(command=self.text_area.yview)
# 下半部分：图片说明区域
img_frame = ttk.LabelFrame(paned, text="操作图解 (点击图片查看原图)")
paned.add(img_frame)
self.img_label = ttk.Label(img_frame, text="正在加载说明图片...", cursor="hand2")
self.img_label.pack(fill="both", expand=True, padx=10, pady=10)
# 绑定点击事件
self.img_label.bind("<Button-1>", lambda e: self.show_full_image())
self.load_manual_image()
def load_manual_image(self):
"""加载 help2.png"""
try:
img_path = resource_path("help2.png")
if os.path.exists(img_path):
self.full_image = Image.open(img_path)
display_img = self.full_image.copy()
display_img.thumbnail((900, 400))
self.photo = ImageTk.PhotoImage(display_img)
self.img_label.config(image=self.photo, text="")
else:
self.img_label.config(text=f"未找到说明图片: help2.png\n请确保打包或运行目录下存在该图片。")
except Exception as e:
self.img_label.config(text=f"图片加载失败: {str(e)}")
def show_full_image(self):
"""弹出大图预览窗并最大化"""
if self.full_image is None:
return
top = tk.Toplevel(self.root)
top.title("操作图解预览")
try:
top.state('zoomed') # 最大化
except:
top.attributes("-fullscreen", True)
img_w, img_h = self.full_image.size
canvas = tk.Canvas(top, highlightthickness=0)
hbar = ttk.Scrollbar(top, orient=tk.HORIZONTAL, command=canvas.xview)
vbar = ttk.Scrollbar(top, orient=tk.VERTICAL, command=canvas.yview)
canvas.config(xscrollcommand=hbar.set, yscrollcommand=vbar.set)
hbar.pack(side=tk.BOTTOM, fill=tk.X)
vbar.pack(side=tk.RIGHT, fill=tk.Y)
canvas.pack(side=tk.LEFT, expand=True, fill=tk.BOTH)
self.full_photo = ImageTk.PhotoImage(self.full_image)
canvas.create_image(0, 0, anchor=tk.NW, image=self.full_photo)
canvas.config(scrollregion=(0, 0, img_w, img_h))
# --- 原有逻辑保持不变 ---
def select_template(self):
path = filedialog.askopenfilename(filetypes=[("Excel", "*.xlsx")])
if path: self.template_path.set(path)
def select_data(self):
path = filedialog.askopenfilename(filetypes=[("Excel", "*.xlsx")])
if path:
self.data_path.set(path)
base = os.path.splitext(path)[0]
self.output_path.set(f"{base}_结果_{time.strftime('%H%M%S')}.xlsx")
def _copy_worksheet(self, source_ws, target_ws):
for row in range(1, source_ws.max_row + 1):
for col in range(1, source_ws.max_column + 1):
source_cell = source_ws.cell(row=row, column=col)
target_cell = target_ws.cell(row=row, column=col, value=source_cell.value)
if source_cell.has_style:
target_cell.font = copy(source_cell.font)
target_cell.border = copy(source_cell.border)
target_cell.fill = copy(source_cell.fill)
target_cell.number_format = source_cell.number_format
target_cell.alignment = copy(source_cell.alignment)
for col in range(1, source_ws.max_column + 1):
col_letter = get_column_letter(col)
target_ws.column_dimensions[col_letter].width = source_ws.column_dimensions[col_letter].width
for row in range(1, source_ws.max_row + 1):
if source_ws.row_dimensions[row].height:
target_ws.row_dimensions[row].height = source_ws.row_dimensions[row].height
for merged_range in source_ws.merged_cells.ranges:
target_ws.merge_cells(str(merged_range))
def start_generate_thread(self):
if not all([self.template_path.get(), self.data_path.get()]):
messagebox.showwarning("提示", "请确保已选择模板文件和数据文件！")
return
self.generate_btn.config(state=tk.DISABLED)
threading.Thread(target=self.generate_excel_logic, daemon=True).start()
def generate_excel_logic(self):
try:
self.progress_var.set("正在加载数据...")
df = pd.read_excel(self.data_path.get())
total_rows = len(df)
template_wb = openpyxl.load_workbook(self.template_path.get())
template_ws = template_wb.active
new_wb = openpyxl.Workbook()
new_wb.remove(new_wb.active)
for index, row in df.iterrows():
raw_sheet_val = str(row.iloc[0])
safe_name = "".join([c for c in raw_sheet_val if c not in r'\*?:/[]'])[:30]
self.progress_var.set(f"正在处理 ({index + 1}/{total_rows}): {safe_name}")
self.root.after(0, lambda v=((index + 1) / total_rows * 100): self.update_progress(v))
new_ws = new_wb.create_sheet(title=safe_name)
self._copy_worksheet(template_ws, new_ws)
for r_idx in range(1, new_ws.max_row + 1):
for c_idx in range(1, new_ws.max_column + 1):
cell = new_ws.cell(row=r_idx, column=c_idx)
if cell.value and isinstance(cell.value, str) and "{{" in cell.value:
field_name = cell.value.replace("{{", "").replace("}}", "").strip()
found = False
for col_name in df.columns:
clean_col_name = col_name.split('.')[0] if '.' in col_name else col_name
if clean_col_name == field_name:
cell.value = row[col_name] if pd.notna(row[col_name]) else ""
found = True
break
self.progress_var.set("保存中...")
out_file = self.output_path.get()
new_wb.save(out_file)
self.root.after(0, lambda: self.ask_to_open_file(out_file))
except Exception as e:
self.root.after(0, lambda err=str(e): messagebox.showerror("错误", err))
finally:
self.root.after(0, self.reset_ui)
def ask_to_open_file(self, file_path):
ans = messagebox.askyesno("生成成功", f"✅ 文件已生成！\n路径：{file_path}\n\n是否立即打开该文件？")
if ans:
try:
if os.name == 'nt':
os.startfile(file_path)
elif os.name == 'posix':
subprocess.call(['open' if sys.platform == 'darwin' else 'xdg-open', file_path])
except Exception as e:
messagebox.showwarning("提醒", f"无法自动打开文件。\n{e}")
def update_progress(self, value):
self.progress_bar['value'] = value
def reset_ui(self):
self.generate_btn.config(state=tk.NORMAL)
self.progress_var.set("准备就绪")
self.progress_bar['value'] = 0
if __name__ == "__main__":
try:
from ctypes import windll
windll.shcore.SetProcessDpiAwareness(1)
except:
pass
root = tk.Tk()
app = GeneralExcelGenerator(root)
root.mainloop()
发表于 2026-2-4 20:57
