# -*- coding: utf-8 -*-
import pandas as pd
from datetime import datetime
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import os
import traceback
import re
from openpyxl import load_workbook
from concurrent.futures import ThreadPoolExecutor
from tkinter import font as tkfont
class EnhancedVersionUpdaterApp:
def __init__(self, root):
self.root = root
self.root.title("Excel批量修改工具")
self.root.geometry("1300x900")
# 设置全局字体
default_font = tkfont.nametofont("TkDefaultFont")
default_font.configure(size=10)
self.root.option_add("*Font", default_font)
# 使用线程池提高性能
self.executor = ThreadPoolExecutor(max_workers=4)
self.running_tasks = 0
# 初始化变量
self.file_path = ""
self.old_project = ""
self.new_project = ""
self.old_date = ""
self.new_date = datetime.now().strftime("%Y-%m-%d")
self.old_responsible = ""
self.new_responsible = ""
self.project_updates = []
self.date_updates = []
self.responsible_updates = []
# 特殊sheet配置
self.special_sheets = {
'変更履歴': {'process': False},
'history': {'process': False},
'log': {'process': False},
'封面': {'process': True, 'update_time': False},
'表紙': {'process': True, 'update_time': False},
'cover': {'process': True, 'update_time': False}
}
# 时间格式正则表达式
self.date_patterns = [
r'\d{4}-\d{2}-\d{2}', # YYYY-MM-DD
r'\d{4}/\d{2}/\d{2}', # YYYY/MM/DD
r'\d{4}年\d{2}月\d{2}日', # 中文日期
r'\d{2}-\d{2}-\d{4}', # MM-DD-YYYY
r'\d{2}/\d{2}/\d{4}' # MM/DD/YYYY
]
self.create_enhanced_ui()
self.setup_style()
def setup_style(self):
"""设置界面样式"""
style = ttk.Style()
style.configure('TFrame', background='white')
style.configure('TLabel', background='white')
style.configure('Treeview', rowheight=25)
style.configure('Treeview.Heading', font=('Arial', 10, 'bold'))
style.configure('TNotebook.Tab', padding=[10, 5])
style.configure('TButton', padding=5)
style.map('TButton',
background=[('active', '#e6e6e6'), ('!active', '#f0f0f0')],
foreground=[('active', 'black'), ('!active', 'black')])
def create_enhanced_ui(self):
"""创建增强版用户界面"""
# 主框架
main_frame = ttk.Frame(self.root)
main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 顶部工具栏
toolbar_frame = ttk.Frame(main_frame)
toolbar_frame.pack(fill=tk.X, pady=(0, 10))
# 文件选择区域
file_frame = ttk.LabelFrame(toolbar_frame, text="文件选择", padding=10)
file_frame.pack(side=tk.LEFT, fill=tk.X, expand=True)
ttk.Label(file_frame, text="Excel文件路径:").pack(side=tk.LEFT)
self.file_entry = ttk.Entry(file_frame, width=60)
self.file_entry.pack(side=tk.LEFT, padx=5, fill=tk.X, expand=True)
ttk.Button(file_frame, text="浏览", command=self.browse_file).pack(side=tk.LEFT)
# 进度条
self.progress_var = tk.DoubleVar()
self.progress_bar = ttk.Progressbar(toolbar_frame, variable=self.progress_var, maximum=100)
self.progress_bar.pack(side=tk.RIGHT, fill=tk.X, expand=True, padx=(10, 0))
self.progress_label = ttk.Label(toolbar_frame, text="就绪")
self.progress_label.pack(side=tk.RIGHT, padx=5)
# 主内容区域 - 使用Notebook实现标签页
notebook = ttk.Notebook(main_frame)
notebook.pack(fill=tk.BOTH, expand=True)
# 项目变更标签页
project_tab = ttk.Frame(notebook)
notebook.add(project_tab, text="项目编号变更")
self.create_project_tab(project_tab)
# 时间变更标签页
date_tab = ttk.Frame(notebook)
notebook.add(date_tab, text="时间变更")
self.create_date_tab(date_tab)
# 担当变更标签页
responsible_tab = ttk.Frame(notebook)
notebook.add(responsible_tab, text="担当变更")
self.create_responsible_tab(responsible_tab)
# 底部状态栏
self.status_var = tk.StringVar()
self.status_var.set("就绪")
status_bar = ttk.Label(main_frame, textvariable=self.status_var, relief=tk.SUNKEN)
status_bar.pack(fill=tk.X, pady=(5, 0))
def create_project_tab(self, parent):
"""创建项目变更标签页 - 仅表格可滚动"""
# 主框架
main_frame = ttk.Frame(parent)
main_frame.pack(fill=tk.BOTH, expand=True)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=5)
ttk.Label(input_frame, text="原项目编号:").grid(row=0, column=0, sticky="w", padx=5)
self.old_project_entry = ttk.Entry(input_frame, width=30)
self.old_project_entry.grid(row=0, column=1, sticky="w", padx=5)
ttk.Label(input_frame, text="新项目编号:").grid(row=1, column=0, sticky="w", padx=5)
self.new_project_entry = ttk.Entry(input_frame, width=30)
self.new_project_entry.grid(row=1, column=1, sticky="w", padx=5)
ttk.Button(input_frame, text="查找项目",
command=lambda: self.executor.submit(self.load_project_changes)).grid(row=0, column=2, rowspan=2, padx=10)
# 表格容器框架
table_container = ttk.Frame(main_frame)
table_container.pack(fill=tk.BOTH, expand=True, pady=5)
# 创建Treeview和滚动条
tree_scroll_y = ttk.Scrollbar(table_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(table_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
self.project_tree = ttk.Treeview(
table_container,
columns=("Sheet", "位置", "原项目", "新项目", "状态"),
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set
)
self.project_tree.pack(fill=tk.BOTH, expand=True)
# 配置滚动条
tree_scroll_y.config(command=self.project_tree.yview)
tree_scroll_x.config(command=self.project_tree.xview)
# 配置列
for col, width in [("Sheet", 200), ("位置", 100), ("原项目", 200), ("新项目", 200), ("状态", 100)]:
self.project_tree.column(col, width=width, anchor="w")
self.project_tree.heading(col, text=col)
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=5)
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_project_status("待更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_project_status("不更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_project_updates)).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_project_report).pack(side=tk.RIGHT, padx=5)
def create_date_tab(self, parent):
"""创建时间变更标签页 - 仅表格可滚动"""
# 主框架
main_frame = ttk.Frame(parent)
main_frame.pack(fill=tk.BOTH, expand=True)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=5)
ttk.Label(input_frame, text="原时间:").grid(row=0, column=0, sticky="w", padx=5)
self.old_date_entry = ttk.Entry(input_frame, width=30)
self.old_date_entry.grid(row=0, column=1, sticky="w", padx=5)
ttk.Label(input_frame, text="新时间:").grid(row=1, column=0, sticky="w", padx=5)
self.new_date_entry = ttk.Entry(input_frame, width=30)
self.new_date_entry.insert(0, self.new_date)
self.new_date_entry.grid(row=1, column=1, sticky="w", padx=5)
ttk.Button(input_frame, text="查找时间",
command=lambda: self.executor.submit(self.load_date_changes)).grid(row=0, column=2, rowspan=2, padx=10)
# 表格容器框架
table_container = ttk.Frame(main_frame)
table_container.pack(fill=tk.BOTH, expand=True, pady=5)
# 创建Treeview和滚动条
tree_scroll_y = ttk.Scrollbar(table_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(table_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
self.date_tree = ttk.Treeview(
table_container,
columns=("Sheet", "位置", "原时间", "新时间", "状态"),
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set
)
self.date_tree.pack(fill=tk.BOTH, expand=True)
# 配置滚动条
tree_scroll_y.config(command=self.date_tree.yview)
tree_scroll_x.config(command=self.date_tree.xview)
# 配置列
for col, width in [("Sheet", 200), ("位置", 100), ("原时间", 200), ("新时间", 200), ("状态", 100)]:
self.date_tree.column(col, width=width, anchor="w")
self.date_tree.heading(col, text=col)
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=5)
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_date_status("待更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_date_status("不更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_date_updates)).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_date_report).pack(side=tk.RIGHT, padx=5)
def create_responsible_tab(self, parent):
"""创建担当变更标签页 - 仅表格可滚动"""
# 主框架
main_frame = ttk.Frame(parent)
main_frame.pack(fill=tk.BOTH, expand=True)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=5)
ttk.Label(input_frame, text="原担当:").grid(row=0, column=0, sticky="w", padx=5)
self.old_responsible_entry = ttk.Entry(input_frame, width=30)
self.old_responsible_entry.grid(row=0, column=1, sticky="w", padx=5)
ttk.Label(input_frame, text="新担当:").grid(row=1, column=0, sticky="w", padx=5)
self.new_responsible_entry = ttk.Entry(input_frame, width=30)
self.new_responsible_entry.grid(row=1, column=1, sticky="w", padx=5)
ttk.Button(input_frame, text="查找担当",
command=lambda: self.executor.submit(self.load_responsible_changes)).grid(row=0, column=2, rowspan=2, padx=10)
# 表格容器框架
table_container = ttk.Frame(main_frame)
table_container.pack(fill=tk.BOTH, expand=True, pady=5)
# 创建Treeview和滚动条
tree_scroll_y = ttk.Scrollbar(table_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(table_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
self.responsible_tree = ttk.Treeview(
table_container,
columns=("Sheet", "位置", "原担当", "新担当", "状态"),
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set
)
self.responsible_tree.pack(fill=tk.BOTH, expand=True)
# 配置滚动条
tree_scroll_y.config(command=self.responsible_tree.yview)
tree_scroll_x.config(command=self.responsible_tree.xview)
# 配置列
for col, width in [("Sheet", 200), ("位置", 100), ("原担当", 200), ("新担当", 200), ("状态", 100)]:
self.responsible_tree.column(col, width=width, anchor="w")
self.responsible_tree.heading(col, text=col)
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=5)
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_responsible_status("待更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_responsible_status("不更新")).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_responsible_updates)).pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_responsible_report).pack(side=tk.RIGHT, padx=5)
def _display_data(self, treeview, data):
"""显示数据到表格 - 添加自动滚动到顶部功能"""
treeview.delete(*treeview.get_children())
for update in data:
tags = ("to_update",) if update["status"] == "待更新" else ("no_update",) if update["status"] == "不更新" else ()
treeview.insert("", "end",
values=(update["sheet"],
update["cell"],
update["old_value"],
update["new_value"],
update["status"]),
tags=tags)
treeview.tag_configure("to_update", background="lightyellow")
treeview.tag_configure("no_update", background="lightgray")
# 自动滚动到顶部
treeview.yview_moveto(0)
def browse_file(self):
"""浏览文件"""
file_path = filedialog.askopenfilename(
filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")],
title="选择Excel文件"
)
if file_path:
self.file_entry.delete(0, tk.END)
self.file_entry.insert(0, file_path)
self.file_path = file_path
self.update_status(f"已选择文件: {os.path.basename(file_path)}")
def update_status(self, message):
"""更新状态栏"""
self.status_var.set(message)
self.root.update_idletasks()
def update_progress(self, value, message=None):
"""更新进度条"""
self.progress_var.set(value)
if message:
self.progress_label.config(text=message)
self.root.update_idletasks()
def load_project_changes(self):
"""加载项目编号变更"""
try:
self.running_tasks += 1
self.update_progress(0, "正在查找项目编号...")
self.file_path = self.file_entry.get()
self.old_project = self.old_project_entry.get().strip()
self.new_project = self.new_project_entry.get().strip()
if not all([self.file_path, self.old_project, self.new_project]):
messagebox.showerror("错误", "请填写所有必填字段")
return
if not os.path.exists(self.file_path):
messagebox.showerror("错误", "文件不存在!")
return
self.project_updates = self._detect_changes(
target_value=self.old_project,
new_value=self.new_project,
value_type="project"
)
if not self.project_updates:
messagebox.showinfo("提示", "未找到匹配的项目编号")
return
self._display_data(self.project_tree, self.project_updates)
self.update_status(f"找到 {len(self.project_updates)} 处项目编号需要更新")
self.update_progress(100, "查找完成")
except Exception as e:
messagebox.showerror("错误", f"加载失败: {str(e)}")
print(traceback.format_exc())
finally:
self.running_tasks -= 1
def load_date_changes(self):
"""加载时间变更"""
try:
self.running_tasks += 1
self.update_progress(0, "正在查找时间...")
self.file_path = self.file_entry.get()
self.old_date = self.old_date_entry.get().strip()
self.new_date = self.new_date_entry.get().strip()
if not all([self.file_path, self.old_date, self.new_date]):
messagebox.showerror("错误", "请填写所有必填字段")
return
if not os.path.exists(self.file_path):
messagebox.showerror("错误", "文件不存在!")
return
self.date_updates = self._detect_changes(
target_value=self.old_date,
new_value=self.new_date,
value_type="date"
)
if not self.date_updates:
messagebox.showinfo("提示", "未找到匹配的时间")
return
self._display_data(self.date_tree, self.date_updates)
self.update_status(f"找到 {len(self.date_updates)} 处时间需要更新")
self.update_progress(100, "查找完成")
except Exception as e:
messagebox.showerror("错误", f"加载失败: {str(e)}")
print(traceback.format_exc())
finally:
self.running_tasks -= 1
def load_responsible_changes(self):
"""加载担当变更"""
try:
self.running_tasks += 1
self.update_progress(0, "正在查找担当...")
self.file_path = self.file_entry.get()
self.old_responsible = self.old_responsible_entry.get().strip()
self.new_responsible = self.new_responsible_entry.get().strip()
if not all([self.file_path, self.old_responsible, self.new_responsible]):
messagebox.showerror("错误", "请填写所有必填字段")
return
if not os.path.exists(self.file_path):
messagebox.showerror("错误", "文件不存在!")
return
self.responsible_updates = self._detect_changes(
target_value=self.old_responsible,
new_value=self.new_responsible,
value_type="responsible"
)
if not self.responsible_updates:
messagebox.showinfo("提示", "未找到匹配的担当")
return
self._display_data(self.responsible_tree, self.responsible_updates)
self.update_status(f"找到 {len(self.responsible_updates)} 处担当需要更新")
self.update_progress(100, "查找完成")
except Exception as e:
messagebox.showerror("错误", f"加载失败: {str(e)}")
print(traceback.format_exc())
finally:
self.running_tasks -= 1
def _detect_changes(self, target_value, new_value, value_type):
"""通用变更检测方法"""
updates = []
try:
wb = load_workbook(self.file_path)
total_sheets = len(wb.sheetnames)
for i, sheet_name in enumerate(wb.sheetnames):
self.update_progress((i+1)/total_sheets*100, f"正在处理工作表: {sheet_name}")
sheet_config = self._get_sheet_config(sheet_name)
if not sheet_config['process']:
continue
if value_type == "date" and not sheet_config.get('update_time', True):
continue
sheet = wb[sheet_name]
if value_type == "date":
cells = self._find_date_cells(sheet, target_value)
else:
cells = self._find_cells_with_value(sheet, target_value)
for cell in cells:
updates.append({
"sheet": sheet_name,
"cell": cell.coordinate,
"old_value": cell.value,
"new_value": new_value,
"status": "待审核"
})
except Exception as e:
print(f"DEBUG - 读取错误: {str(e)}")
return updates
def _display_data(self, treeview, data):
"""显示数据到表格"""
treeview.delete(*treeview.get_children())
for update in data:
tags = ("to_update",) if update["status"] == "待更新" else ("no_update",) if update["status"] == "不更新" else ()
treeview.insert("", "end",
values=(update["sheet"],
update["cell"],
update["old_value"],
update["new_value"],
update["status"]),
tags=tags)
treeview.tag_configure("to_update", background="lightyellow")
treeview.tag_configure("no_update", background="lightgray")
def update_project_status(self, status):
"""更新项目变更状态"""
self._update_status(self.project_tree, self.project_updates, status)
def update_date_status(self, status):
"""更新时间变更状态"""
self._update_status(self.date_tree, self.date_updates, status)
def update_responsible_status(self, status):
"""更新担当变更状态"""
self._update_status(self.responsible_tree, self.responsible_updates, status)
def _update_status(self, treeview, data, status):
"""通用状态更新方法"""
selected = treeview.selection()
if not selected:
messagebox.showwarning("警告", "请先选择记录")
return
for item in selected:
index = treeview.index(item)
data[index]["status"] = status
self._display_data(treeview, data)
def apply_project_updates(self):
"""执行项目编号变更"""
self._apply_updates(
updates=self.project_updates,
success_message="项目编号更新完成!",
treeview=self.project_tree
)
def apply_date_updates(self):
"""执行时间变更"""
self._apply_updates(
updates=self.date_updates,
success_message="时间更新完成!",
treeview=self.date_tree
)
def apply_responsible_updates(self):
"""执行担当变更"""
self._apply_updates(
updates=self.responsible_updates,
success_message="担当更新完成!",
treeview=self.responsible_tree
)
def _apply_updates(self, updates, success_message, treeview):
"""通用更新应用方法"""
if not any(u["status"] == "待更新" for u in updates):
messagebox.showwarning("警告", "没有标记为'待更新'的记录")
return
try:
self.running_tasks += 1
self.update_progress(0, "正在更新...")
wb = load_workbook(self.file_path)
total_updates = len([u for u in updates if u["status"] == "待更新"])
processed = 0
for update in [u for u in updates if u["status"] == "待更新"]:
sheet = wb[update["sheet"]]
sheet[update["cell"]] = update["new_value"]
processed += 1
self.update_progress(processed/total_updates*100, f"正在更新 {update['sheet']} {update['cell']}")
wb.save(self.file_path)
messagebox.showinfo("成功", success_message)
# 重新加载数据
if treeview == self.project_tree:
self.load_project_changes()
elif treeview == self.date_tree:
self.load_date_changes()
else:
self.load_responsible_changes()
except Exception as e:
messagebox.showerror("错误", f"更新失败: {str(e)}")
print(traceback.format_exc())
finally:
self.running_tasks -= 1
self.update_progress(100, "更新完成")
def export_project_report(self):
"""导出项目变更报告"""
self._export_report(self.project_updates, "项目变更报告")
def export_date_report(self):
"""导出时间变更报告"""
self._export_report(self.date_updates, "时间变更报告")
def export_responsible_report(self):
"""导出担当变更报告"""
self._export_report(self.responsible_updates, "担当变更报告")
def _export_report(self, data, report_name):
"""通用报告导出方法"""
if not data:
messagebox.showwarning("警告", f"没有可导出的{report_name}数据")
return
try:
file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel文件", "*.xlsx")],
title=f"保存{report_name}"
)
if not file_path:
return
df = pd.DataFrame([{
"工作表": item["sheet"],
"单元格位置": item["cell"],
"原内容": item["old_value"],
"新内容": item["new_value"],
"状态": item["status"]
} for item in data])
df.to_excel(file_path, index=False)
messagebox.showinfo("成功", f"{report_name}已导出到: {file_path}")
except Exception as e:
messagebox.showerror("错误", f"导出失败: {str(e)}")
def _get_sheet_config(self, sheet_name):
"""获取sheet配置"""
sheet_lower = sheet_name.lower()
for kw in self.special_sheets:
if kw.lower() in sheet_lower:
config = self.special_sheets[kw].copy()
config['is_special'] = True
return config
return {'process': True, 'update_time': True, 'is_special': False}
def _find_cells_with_value(self, sheet, target_value):
"""查找包含目标值的所有单元格"""
found_cells = []
pattern = re.compile(rf'.*{re.escape(str(target_value))}.*', re.IGNORECASE)
for row in sheet.iter_rows():
for cell in row:
if cell.value and pattern.search(str(cell.value)):
found_cells.append(cell)
return found_cells
def _find_date_cells(self, sheet, target_date=None):
"""查找所有包含日期的单元格"""
date_cells = []
for row in sheet.iter_rows():
for cell in row:
if cell.value and self._is_date(cell.value):
if target_date:
try:
cell_date = pd.to_datetime(cell.value).strftime('%Y-%m-%d')
if target_date in str(cell_date):
date_cells.append(cell)
except:
if target_date in str(cell.value):
date_cells.append(cell)
else:
date_cells.append(cell)
return date_cells
def _is_date(self, value):
"""判断值是否为日期"""
try:
pd.to_datetime(value)
return True
except:
# 检查是否是字符串形式的日期
if isinstance(value, str):
for pattern in self.date_patterns:
if re.fullmatch(pattern, value.strip()):
return True
return False
def on_closing(self):
"""关闭窗口时的处理"""
if self.running_tasks > 0:
if messagebox.askokcancel("警告", "有任务正在运行,确定要退出吗?"):
self.executor.shutdown(wait=False)
self.root.destroy()
else:
self.root.destroy()
if __name__ == "__main__":
root = tk.Tk()
app = EnhancedVersionUpdaterApp(root)
root.protocol("WM_DELETE_WINDOW", app.on_closing)
root.mainloop()
1、请增加一些关于文档检查的功能
2、请美化一下界面
3、请加快一下运行速度,并减少对内存的占用
最新发布