# -*- coding: utf-8 -*-
import pandas as pd
from datetime import datetime
import tkinter as tk
from tkinter import ttk, filedialog, messagebox, scrolledtext
import os
import traceback
import re
from openpyxl import load_workbook
from concurrent.futures import ThreadPoolExecutor
from tkinter import font as tkfont
import psutil
import time
import threading
import math
from PIL import Image, ImageTk
class EnhancedVersionUpdaterApp:
def __init__(self, root):
self.root = root
self.root.title("Excel批量修改工具 - 专业版")
self.root.geometry("1400x950")
self.root.configure(bg="#f0f2f5")
# 设置主题颜色
self.primary_color = "#4e73df"
self.secondary_color = "#858796"
self.success_color = "#1cc88a"
self.warning_color = "#f6c23e"
self.danger_color = "#e74a3b"
self.light_color = "#f8f9fc"
self.dark_color = "#5a5c69"
# 设置全局字体
default_font = tkfont.nametofont("TkDefaultFont")
default_font.configure(family="Segoe UI", 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 = []
self.doc_checks = []
# 特殊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 = [
re.compile(r'\d{4}-\d{2}-\d{2}'), # YYYY-MM-DD
re.compile(r'\d{4}/\d{2}/\d{2}'), # YYYY/MM/DD
re.compile(r'\d{4}年\d{2}月\d{2}日'), # 中文日期
re.compile(r'\d{2}-\d{2}-\d{4}'), # MM-DD-YYYY
re.compile(r'\d{2}/\d{2}/\d{4}') # MM/DD/YYYY
]
self.create_enhanced_ui()
self.setup_style()
# 启动内存监控线程
self.memory_usage = 0
self.memory_thread = threading.Thread(target=self.monitor_memory, daemon=True)
self.memory_thread.start()
def monitor_memory(self):
"""监控内存使用情况"""
while True:
process = psutil.Process(os.getpid())
self.memory_usage = process.memory_info().rss / (1024 * 1024) # MB
time.sleep(2)
def setup_style(self):
"""设置界面样式"""
style = ttk.Style()
# 配置主题
style.theme_use('clam')
# 通用样式
style.configure('TFrame', background=self.light_color)
style.configure('TLabel', background=self.light_color, foreground=self.dark_color)
style.configure('TLabelframe', background=self.light_color, relief="flat", borderwidth=0)
style.configure('TLabelframe.Label', background=self.light_color, foreground=self.primary_color, font=('Segoe UI', 10, 'bold'))
# 按钮样式
style.configure('TButton', background="#f8f9fc", foreground=self.dark_color,
borderwidth=1, relief="solid", padding=6, font=('Segoe UI', 9))
style.map('TButton',
background=[('active', '#e6e6e6'), ('!active', '#f8f9fc')],
foreground=[('active', self.dark_color), ('!active', self.dark_color)])
# 特殊按钮样式
style.configure('Primary.TButton', background=self.primary_color, foreground="white")
style.map('Primary.TButton',
background=[('active', '#2e59d9'), ('!active', self.primary_color)],
foreground=[('active', 'white'), ('!active', 'white')])
style.configure('Success.TButton', background=self.success_color, foreground="white")
style.map('Success.TButton',
background=[('active', '#17a673'), ('!active', self.success_color)],
foreground=[('active', 'white'), ('!active', 'white')])
style.configure('Warning.TButton', background=self.warning_color, foreground="white")
style.map('Warning.TButton',
background=[('active', '#dda20a'), ('!active', self.warning_color)],
foreground=[('active', 'white'), ('!active', 'white')])
# 进度条样式
style.configure("Custom.Horizontal.TProgressbar",
thickness=15,
troughcolor=self.light_color,
background=self.primary_color,
lightcolor=self.primary_color,
darkcolor=self.primary_color)
# Treeview样式
style.configure("Treeview",
background="white",
foreground=self.dark_color,
rowheight=28,
fieldbackground="white",
font=('Segoe UI', 9))
style.configure("Treeview.Heading",
background=self.primary_color,
foreground="white",
font=('Segoe UI', 10, 'bold'),
padding=(5, 5))
style.map("Treeview.Heading",
background=[('active', '#2e59d9')])
# Notebook样式
style.configure("TNotebook", background=self.light_color)
style.configure("TNotebook.Tab",
background=self.light_color,
foreground=self.dark_color,
padding=(12, 6),
font=('Segoe UI', 10, 'bold'))
style.map("TNotebook.Tab",
background=[('selected', 'white'), ('!selected', self.light_color)],
foreground=[('selected', self.primary_color), ('!selected', self.secondary_color)])
def create_enhanced_ui(self):
"""创建增强版用户界面"""
# 主框架
main_frame = ttk.Frame(self.root)
main_frame.pack(fill=tk.BOTH, expand=True, padx=15, pady=15)
# 标题栏
title_frame = ttk.Frame(main_frame)
title_frame.pack(fill=tk.X, pady=(0, 15))
ttk.Label(title_frame, text="Excel批量修改工具",
font=('Segoe UI', 18, 'bold'),
foreground=self.primary_color).pack(side=tk.LEFT)
# 内存指示器
self.memory_label = ttk.Label(title_frame, text="内存使用: 0 MB", font=('Segoe UI', 9))
self.memory_label.pack(side=tk.RIGHT, padx=10)
# 文件选择区域
file_frame = ttk.LabelFrame(main_frame, text="文件操作", padding=10)
file_frame.pack(fill=tk.X, pady=(0, 15))
file_input_frame = ttk.Frame(file_frame)
file_input_frame.pack(fill=tk.X, pady=5)
ttk.Label(file_input_frame, text="Excel文件路径:").pack(side=tk.LEFT)
self.file_entry = ttk.Entry(file_input_frame, width=70)
self.file_entry.pack(side=tk.LEFT, padx=5, fill=tk.X, expand=True)
ttk.Button(file_input_frame, text="浏览文件", command=self.browse_file, style='Primary.TButton').pack(side=tk.LEFT)
# 状态和进度区域
status_frame = ttk.Frame(file_frame)
status_frame.pack(fill=tk.X, pady=(10, 0))
# 进度条
self.progress_var = tk.DoubleVar()
self.progress_bar = ttk.Progressbar(status_frame, variable=self.progress_var,
maximum=100, style="Custom.Horizontal.TProgressbar")
self.progress_bar.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=(0, 10))
# 进度标签
self.progress_label = ttk.Label(status_frame, text="就绪", width=15)
self.progress_label.pack(side=tk.RIGHT)
# 主内容区域 - 使用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)
# 文档检查标签页
doc_check_tab = ttk.Frame(notebook)
notebook.add(doc_check_tab, text="文档检查")
self.create_doc_check_tab(doc_check_tab)
# 底部状态栏
status_bar = ttk.Frame(main_frame, relief=tk.SUNKEN, padding=(5, 3))
status_bar.pack(fill=tk.X, pady=(10, 0))
self.status_var = tk.StringVar()
self.status_var.set("就绪 - 等待操作")
ttk.Label(status_bar, textvariable=self.status_var,
anchor=tk.W, font=('Segoe UI', 9)).pack(fill=tk.X)
# 定期更新内存使用情况
self.update_memory_display()
def update_memory_display(self):
"""更新内存显示"""
self.memory_label.config(text=f"内存使用: {self.memory_usage:.2f} MB")
self.root.after(2000, self.update_memory_display)
def create_project_tab(self, parent):
"""创建项目变更标签页"""
# 主框架
main_frame = ttk.Frame(parent)
main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(input_frame, text="原项目编号:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
self.old_project_entry = ttk.Entry(input_frame, width=30)
self.old_project_entry.grid(row=0, column=1, sticky="w", padx=5, pady=5)
ttk.Label(input_frame, text="新项目编号:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
self.new_project_entry = ttk.Entry(input_frame, width=30)
self.new_project_entry.grid(row=1, column=1, sticky="w", padx=5, pady=5)
ttk.Button(input_frame, text="查找项目",
command=lambda: self.executor.submit(self.load_project_changes),
style='Primary.TButton').grid(row=0, column=2, rowspan=2, padx=10, pady=5)
# 表格容器框架
table_frame = ttk.LabelFrame(main_frame, text="变更列表")
table_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
# 创建Treeview和滚动条
tree_container = ttk.Frame(table_frame)
tree_container.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
tree_scroll_y = ttk.Scrollbar(tree_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(tree_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
columns = ("Sheet", "位置", "原项目", "新项目", "状态")
self.project_tree = ttk.Treeview(
tree_container,
columns=columns,
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set,
selectmode="extended"
)
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)
# 配置列
col_widths = {"Sheet": 200, "位置": 100, "原项目": 200, "新项目": 200, "状态": 100}
for col in columns:
self.project_tree.heading(col, text=col)
self.project_tree.column(col, width=col_widths.get(col, 120), anchor="w")
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_project_status("待更新"),
style='Success.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_project_status("不更新"),
style='Warning.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_project_updates),
style='Primary.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_project_report,
style='Primary.TButton').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, padx=10, pady=10)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(input_frame, text="原时间:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
self.old_date_entry = ttk.Entry(input_frame, width=30)
self.old_date_entry.grid(row=0, column=1, sticky="w", padx=5, pady=5)
ttk.Label(input_frame, text="新时间:").grid(row=1, column=0, sticky="w", padx=5, pady=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, pady=5)
ttk.Button(input_frame, text="查找时间",
command=lambda: self.executor.submit(self.load_date_changes),
style='Primary.TButton').grid(row=0, column=2, rowspan=2, padx=10, pady=5)
# 表格容器框架
table_frame = ttk.LabelFrame(main_frame, text="变更列表")
table_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
# 创建Treeview和滚动条
tree_container = ttk.Frame(table_frame)
tree_container.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
tree_scroll_y = ttk.Scrollbar(tree_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(tree_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
columns = ("Sheet", "位置", "原时间", "新时间", "状态")
self.date_tree = ttk.Treeview(
tree_container,
columns=columns,
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set,
selectmode="extended"
)
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)
# 配置列
col_widths = {"Sheet": 200, "位置": 100, "原时间": 200, "新时间": 200, "状态": 100}
for col in columns:
self.date_tree.heading(col, text=col)
self.date_tree.column(col, width=col_widths.get(col, 120), anchor="w")
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_date_status("待更新"),
style='Success.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_date_status("不更新"),
style='Warning.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_date_updates),
style='Primary.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_date_report,
style='Primary.TButton').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, padx=10, pady=10)
# 输入区域
input_frame = ttk.Frame(main_frame)
input_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Label(input_frame, text="原担当:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
self.old_responsible_entry = ttk.Entry(input_frame, width=30)
self.old_responsible_entry.grid(row=0, column=1, sticky="w", padx=5, pady=5)
ttk.Label(input_frame, text="新担当:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
self.new_responsible_entry = ttk.Entry(input_frame, width=30)
self.new_responsible_entry.grid(row=1, column=1, sticky="w", padx=5, pady=5)
ttk.Button(input_frame, text="查找担当",
command=lambda: self.executor.submit(self.load_responsible_changes),
style='Primary.TButton').grid(row=0, column=2, rowspan=2, padx=10, pady=5)
# 表格容器框架
table_frame = ttk.LabelFrame(main_frame, text="变更列表")
table_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
# 创建Treeview和滚动条
tree_container = ttk.Frame(table_frame)
tree_container.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
tree_scroll_y = ttk.Scrollbar(tree_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(tree_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
columns = ("Sheet", "位置", "原担当", "新担当", "状态")
self.responsible_tree = ttk.Treeview(
tree_container,
columns=columns,
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set,
selectmode="extended"
)
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)
# 配置列
col_widths = {"Sheet": 200, "位置": 100, "原担当": 200, "新担当": 200, "状态": 100}
for col in columns:
self.responsible_tree.heading(col, text=col)
self.responsible_tree.column(col, width=col_widths.get(col, 120), anchor="w")
# 操作按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Button(btn_frame, text="标记更新",
command=lambda: self.update_responsible_status("待更新"),
style='Success.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="标记不更新",
command=lambda: self.update_responsible_status("不更新"),
style='Warning.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="执行更新",
command=lambda: self.executor.submit(self.apply_responsible_updates),
style='Primary.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出报告",
command=self.export_responsible_report,
style='Primary.TButton').pack(side=tk.RIGHT, padx=5)
def create_doc_check_tab(self, parent):
"""创建文档检查标签页"""
# 主框架
main_frame = ttk.Frame(parent)
main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 按钮区域
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=(0, 10))
ttk.Button(btn_frame, text="执行文档检查",
command=lambda: self.executor.submit(self.perform_document_checks),
style='Primary.TButton').pack(side=tk.LEFT, padx=5)
ttk.Button(btn_frame, text="导出检查报告",
command=self.export_doc_check_report,
style='Primary.TButton').pack(side=tk.LEFT, padx=5)
# 检查结果区域
result_frame = ttk.LabelFrame(main_frame, text="文档检查结果")
result_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 10))
# 创建Treeview和滚动条
tree_container = ttk.Frame(result_frame)
tree_container.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
tree_scroll_y = ttk.Scrollbar(tree_container)
tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
tree_scroll_x = ttk.Scrollbar(tree_container, orient=tk.HORIZONTAL)
tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
columns = ("检查项", "状态", "详情", "建议")
self.doc_tree = ttk.Treeview(
tree_container,
columns=columns,
show="headings",
height=15,
yscrollcommand=tree_scroll_y.set,
xscrollcommand=tree_scroll_x.set
)
self.doc_tree.pack(fill=tk.BOTH, expand=True)
# 配置滚动条
tree_scroll_y.config(command=self.doc_tree.yview)
tree_scroll_x.config(command=self.doc_tree.xview)
# 配置列
col_widths = {"检查项": 200, "状态": 80, "详情": 300, "建议": 300}
for col in columns:
self.doc_tree.heading(col, text=col)
self.doc_tree.column(col, width=col_widths.get(col, 150), anchor="w")
# 配置标签样式
self.doc_tree.tag_configure("pass", background="#e8f5e9")
self.doc_tree.tag_configure("warning", background="#fff8e1")
self.doc_tree.tag_configure("error", background="#ffebee")
self.doc_tree.tag_configure("info", background="#e3f2fd")
def perform_document_checks(self):
"""执行文档检查"""
try:
self.running_tasks += 1
self.update_progress(0, "正在检查文档...")
self.update_status("正在执行文档检查...")
self.file_path = self.file_entry.get()
if not self.file_path:
messagebox.showerror("错误", "请先选择Excel文件")
return
if not os.path.exists(self.file_path):
messagebox.showerror("错误", "文件不存在！")
return
self.doc_checks = []
# 1. 文件基本信息检查
file_size = os.path.getsize(self.file_path) / (1024 * 1024) # MB
file_ext = os.path.splitext(self.file_path)[1].lower()
self.doc_checks.append({
"检查项": "文件格式",
"状态": "通过" if file_ext in ['.xlsx', '.xls'] else "错误",
"详情": f"文件格式: {file_ext}",
"建议": "使用.xlsx格式以获得最佳兼容性" if file_ext != '.xlsx' else ""
})
self.doc_checks.append({
"检查项": "文件大小",
"状态": "通过" if file_size < 10 else "警告",
"详情": f"{file_size:.2f} MB",
"建议": "文件过大可能影响性能，建议拆分" if file_size >= 10 else ""
})
# 2. 工作簿结构检查
wb = load_workbook(self.file_path, read_only=True)
# 工作表数量
sheet_count = len(wb.sheetnames)
self.doc_checks.append({
"检查项": "工作表数量",
"状态": "通过" if 1 <= sheet_count <= 20 else "警告",
"详情": f"{sheet_count} 个工作表",
"建议": "工作表数量过多可能导致性能问题" if sheet_count > 20 else ""
})
# 隐藏工作表
hidden_sheets = [name for name in wb.sheetnames if wb[name].sheet_state == 'hidden']
self.doc_checks.append({
"检查项": "隐藏工作表",
"状态": "警告" if hidden_sheets else "通过",
"详情": f"{len(hidden_sheets)} 个隐藏工作表" if hidden_sheets else "无隐藏工作表",
"建议": "检查隐藏工作表内容是否必要" if hidden_sheets else ""
})
# 工作表保护
protected_sheets = [name for name in wb.sheetnames if wb[name].protection.sheet]
self.doc_checks.append({
"检查项": "受保护工作表",
"状态": "信息",
"详情": f"{len(protected_sheets)} 个受保护工作表" if protected_sheets else "无受保护工作表",
"建议": "确保有必要的访问权限" if protected_sheets else ""
})
# 3. 内容检查
# 公式错误
formula_errors = {}
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
for row in sheet.iter_rows():
for cell in row:
if cell.data_type == 'e': # 错误类型
if sheet_name not in formula_errors:
formula_errors[sheet_name] = 0
formula_errors[sheet_name] += 1
error_details = ", ".join([f"{k}:{v}" for k, v in formula_errors.items()]) if formula_errors else "无"
self.doc_checks.append({
"检查项": "公式错误",
"状态": "错误" if formula_errors else "通过",
"详情": f"共发现 {sum(formula_errors.values())} 处错误" if formula_errors else "无公式错误",
"建议": "修复公式错误以确保数据准确性" if formula_errors else ""
})
# 特殊字符
non_printable_chars = []
non_printable_pattern = re.compile(r'[\x00-\x1F\x7F-\x9F]') # 控制字符
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
for row in sheet.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str) and non_printable_pattern.search(cell.value):
non_printable_chars.append(f"{sheet_name}!{cell.coordinate}")
self.doc_checks.append({
"检查项": "特殊字符",
"状态": "警告" if non_printable_chars else "通过",
"详情": f"发现 {len(non_printable_chars)} 个特殊字符" if non_printable_chars else "无特殊字符",
"建议": "移除不可打印字符" if non_printable_chars else ""
})
# 超链接检查
hyperlinks = {}
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
if sheet.hyperlinks:
hyperlinks[sheet_name] = len(sheet.hyperlinks)
self.doc_checks.append({
"检查项": "超链接",
"状态": "信息",
"详情": f"共 {sum(hyperlinks.values())} 个超链接" if hyperlinks else "无超链接",
"建议": "验证超链接有效性" if hyperlinks else ""
})
# 4. 元数据检查
# 文件属性
created_time = datetime.fromtimestamp(os.path.getctime(self.file_path))
modified_time = datetime.fromtimestamp(os.path.getmtime(self.file_path))
self.doc_checks.append({
"检查项": "文件属性",
"状态": "信息",
"详情": f"创建: {created_time.strftime('%Y-%m-%d')}, 修改: {modified_time.strftime('%Y-%m-%d')}",
"建议": ""
})
# 显示结果
self._display_doc_checks()
self.update_progress(100, "检查完成")
self.update_status(f"文档检查完成，共 {len(self.doc_checks)} 项检查")
except Exception as e:
messagebox.showerror("错误", f"文档检查失败: {str(e)}")
print(traceback.format_exc())
finally:
self.running_tasks -= 1
def _display_doc_checks(self):
"""显示文档检查结果"""
self.doc_tree.delete(*self.doc_tree.get_children())
for check in self.doc_checks:
status = check["状态"]
tags = ("pass",) if status == "通过" else ("warning",) if status == "警告" else ("error",) if status == "错误" else ("info",)
self.doc_tree.insert("", "end",
values=(check["检查项"], check["状态"], check["详情"], check["建议"]),
tags=tags)
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.update_status("正在查找项目编号变更...")
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
# 使用只读模式加载工作簿以节省内存
wb = load_workbook(self.file_path, read_only=True)
total_sheets = len(wb.sheetnames)
self.project_updates = []
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
sheet = wb[sheet_name]
# 分块处理大型工作表
for row_chunk in self._chunked_rows(sheet, chunk_size=100):
for cell in row_chunk:
if cell.value and self.old_project in str(cell.value):
self.project_updates.append({
"sheet": sheet_name,
"cell": cell.coordinate,
"old_value": cell.value,
"new_value": cell.value.replace(self.old_project, self.new_project),
"status": "待审核"
})
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.update_status("正在查找时间变更...")
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
# 使用只读模式加载工作簿以节省内存
wb = load_workbook(self.file_path, read_only=True)
total_sheets = len(wb.sheetnames)
self.project_updates = []
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
sheet = wb[sheet_name]
# 分块处理大型工作表
for row_chunk in self._chunked_rows(sheet, chunk_size=100):
for cell in row_chunk:
if cell.value and self.old_project in str(cell.value):
self.project_updates.append({
"sheet": sheet_name,
"cell": cell.coordinate,
"old_value": cell.value,
"new_value": cell.value.replace(self.old_project, self.new_project),
"status": "待审核"
})
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.update_status("正在查找担当变更...")
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
# 使用只读模式加载工作簿以节省内存
wb = load_workbook(self.file_path, read_only=True)
total_sheets = len(wb.sheetnames)
self.project_updates = []
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
sheet = wb[sheet_name]
# 分块处理大型工作表
for row_chunk in self._chunked_rows(sheet, chunk_size=100):
for cell in row_chunk:
if cell.value and self.old_project in str(cell.value):
self.project_updates.append({
"sheet": sheet_name,
"cell": cell.coordinate,
"old_value": cell.value,
"new_value": cell.value.replace(self.old_project, self.new_project),
"status": "待审核"
})
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 _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 _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 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 export_doc_check_report(self):
"""导出文档检查报告"""
if not self.doc_checks:
messagebox.showwarning("警告", "没有可导出的文档检查数据")
return
try:
file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel文件", "*.xlsx")],
title="保存文档检查报告"
)
if not file_path:
return
df = pd.DataFrame(self.doc_checks)
df.to_excel(file_path, index=False)
messagebox.showinfo("成功", f"文档检查报告已导出到: {file_path}")
except Exception as e:
messagebox.showerror("错误", f"导出失败: {str(e)}")
def on_closing(self):
"""关闭窗口时的处理"""
if self.running_tasks > 0:
if messagebox.askokcancel("警告", "有任务正在运行，确定要退出吗？"):
self.executor.shutdown(wait=False)
self.root.destroy()
else:
self.root.destroy()
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()
这是我按照你的要求更改后的代码，但是其中有一些错误，例如AttributeError: 'EnhancedVersionUpdaterApp' object has no attribute 'memory_usage'. Did you mean: 'memory_label'?，请检查类似的问题，并修改完成给我
博主因工作需求将电脑内存从512M升级到1G(512M*2)的DDR333，在运行Eclipse、Word等内存消耗大的程序后，明显感受到计算机速度提升，特别是在进行大型程序切换时不再出现假死现象。
扫一扫
80
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
