import osimport jsonimport tkinter as tkfrom tkinter import filedialog, messagebox, ttkimport pandas as pdfrom openpyxl import load_workbook, Workbookfrom openpyxl.utils.dataframe import dataframe_to_rowsclass ExcelSumApp: def __init__(self, root): self.root = root self.root.title("Excel多文件区域求和工具") self.root.geometry("540x500") # 配置文件路径 self.config_file = "app_config.json" # 变量 self.input_folder_path = tk.StringVar() self.output_folder_path = tk.StringVar() self.sheet_name = tk.StringVar() self.cell_range = tk.StringVar() # 加载配置 self.load_config() self.setup_ui() def load_config(self): """加载配置文件""" try: if os.path.exists(self.config_file): with open(self.config_file, 'r', encoding='utf-8') as f: config = json.load(f) self.sheet_name.set(config.get('sheet_name', 'Sheet1')) self.cell_range.set(config.get('cell_range', 'A1:D20')) else: # 默认值 self.sheet_name.set('Sheet1') self.cell_range.set('A1:D20') except Exception as e: print(f"加载配置失败: {e}") self.sheet_name.set('Sheet1') self.cell_range.set('A1:D20') def save_config(self): """保存配置到文件""" try: config = { 'sheet_name': self.sheet_name.get(), 'cell_range': self.cell_range.get() } with open(self.config_file, 'w', encoding='utf-8') as f: json.dump(config, f, ensure_ascii=False, indent=2) except Exception as e: print(f"保存配置失败: {e}") def setup_ui(self): # 主框架 main_frame = ttk.Frame(self.root, padding="10") main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S)) # 输入文件夹选择 ttk.Label(main_frame, text="输入文件夹:").grid(row=0, column=0, sticky=tk.W, pady=5) input_frame = ttk.Frame(main_frame) input_frame.grid(row=1, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=5) ttk.Entry(input_frame, textvariable=self.input_folder_path, width=60).grid(row=0, column=0, padx=(0, 5), sticky=(tk.W, tk.E)) ttk.Button(input_frame, text="浏览", command=self.select_input_folder).grid(row=0, column=1) # 输出文件夹选择 ttk.Label(main_frame, text="输出文件夹:").grid(row=2, column=0, sticky=tk.W, pady=5) output_frame = ttk.Frame(main_frame) output_frame.grid(row=3, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=5) ttk.Entry(output_frame, textvariable=self.output_folder_path, width=60).grid(row=0, column=0, padx=(0, 5), sticky=(tk.W, tk.E)) ttk.Button(output_frame, text="浏览", command=self.select_output_folder).grid(row=0, column=1) # Sheet名称 ttk.Label(main_frame, text="汇总表名称:").grid(row=4, column=0, sticky=tk.W, pady=5) sheet_entry = ttk.Entry(main_frame, textvariable=self.sheet_name, width=50) sheet_entry.grid(row=4, column=1, sticky=tk.W, pady=5) # 单元格区域 ttk.Label(main_frame, text="单元格区域:").grid(row=5, column=0, sticky=tk.W, pady=5) range_entry = ttk.Entry(main_frame, textvariable=self.cell_range, width=50) range_entry.grid(row=5, column=1, sticky=tk.W, pady=5) # 操作按钮 button_frame = ttk.Frame(main_frame) button_frame.grid(row=6, column=0, columnspan=3, pady=20) ttk.Button(button_frame, text="开始汇总", command=self.process_files).pack(side=tk.LEFT, padx=5) ttk.Button(button_frame, text="退出", command=self.on_closing).pack(side=tk.LEFT, padx=5) # 进度条 self.progress = ttk.Progressbar(main_frame, mode='determinate') self.progress.grid(row=7, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=10) # 日志显示 self.log_text = tk.Text(main_frame, height=12, width=70) self.log_text.grid(row=8, column=0, columnspan=3, pady=10, sticky=(tk.W, tk.E, tk.N, tk.S)) # 配置列权重 main_frame.columnconfigure(0, weight=1) main_frame.columnconfigure(1, weight=2) # 给输入框更多空间 main_frame.rowconfigure(8, weight=1) # 让日志窗口可以扩展 # 绑定窗口关闭事件 self.root.protocol("WM_DELETE_WINDOW", self.on_closing) def on_closing(self): """窗口关闭时保存配置""" self.save_config() self.root.destroy() def select_input_folder(self): folder_path = filedialog.askdirectory(title="选择输入文件夹") if folder_path: self.input_folder_path.set(folder_path) def select_output_folder(self): folder_path = filedialog.askdirectory(title="选择输出文件夹") if folder_path: self.output_folder_path.set(folder_path) def log_message(self, message): self.log_text.insert(tk.END, message + "\n") self.log_text.see(tk.END) self.root.update_idletasks() def process_files(self): input_folder = self.input_folder_path.get() output_folder = self.output_folder_path.get() sheet_name = self.sheet_name.get() cell_range = self.cell_range.get() if not input_folder or not output_folder: messagebox.showerror("错误", "请选择输入和输出文件夹") return if not os.path.isdir(input_folder): messagebox.showerror("错误", "输入文件夹不存在") return if not os.path.isdir(output_folder): messagebox.showerror("错误", "输出文件夹不存在") return # 获取所有Excel文件 excel_files = [] for file in os.listdir(input_folder): if file.lower().endswith(('.xlsx', '.xls')): excel_files.append(os.path.join(input_folder, file)) if not excel_files: messagebox.showinfo("提示", "输入文件夹中没有找到Excel文件") return self.log_message(f"找到 {len(excel_files)} 个Excel文件") # 初始化结果DataFrame result_df = None processed_count = 0 # 设置进度条 self.progress['maximum'] = len(excel_files) for i, file_path in enumerate(excel_files): try: self.log_message(f"正在处理: {os.path.basename(file_path)}") # 读取指定区域的数据 df = pd.read_excel(file_path, sheet_name=sheet_name, header=None) # 解析单元格范围 range_data = self.parse_cell_range(cell_range, df.shape[0], df.shape[1]) start_row, end_row, start_col, end_col = range_data # 提取指定区域数据 region_df = df.iloc[start_row:end_row+1, start_col:end_col+1] # 如果是第一个文件，初始化结果DataFrame if result_df is None: result_df = region_df.fillna(0) # 将空值替换为0 else: # 确保尺寸一致 if region_df.shape == result_df.shape: result_df = result_df.add(region_df.fillna(0), fill_value=0) else: self.log_message(f"警告: {os.path.basename(file_path)} 区域形状与第一个文件不匹配，跳过") continue processed_count += 1 self.progress['value'] = i + 1 self.root.update_idletasks() except Exception as e: self.log_message(f"处理 {os.path.basename(file_path)} 时出错: {str(e)}") if result_df is not None: # 保存结果到新文件 output_file = os.path.join(output_folder, "汇总结果.xlsx") # 使用openpyxl创建工作簿以精确控制单元格 wb = Workbook() ws = wb.active ws.title = sheet_name # 将DataFrame数据添加到工作表 for r_idx, row in enumerate(dataframe_to_rows(result_df, index=False, header=False), 1): for c_idx, value in enumerate(row, 1): ws.cell(row=r_idx, column=c_idx, value=value) wb.save(output_file) self.log_message(f"\n处理完成！") self.log_message(f"共处理 {processed_count} 个文件") self.log_message(f"结果已保存至: {output_file}") messagebox.showinfo("完成", f"处理完成！\n共处理 {processed_count} 个文件\n结果已保存至: {output_file}") else: messagebox.showwarning("警告", "没有成功处理任何文件") def parse_cell_range(self, cell_range, max_rows, max_cols): """ 解析单元格范围字符串，如"A1:D20" 返回 (start_row, end_row, start_col, end_col)，索引从0开始 """ try: parts = cell_range.split(':') if len(parts) != 2: raise ValueError("单元格范围格式错误") start_addr, end_addr = parts # 解析起始地址 start_col_str = ''.join([c for c in start_addr if c.isalpha()]) start_row_str = ''.join([c for c in start_addr if c.isdigit()]) start_row = int(start_row_str) - 1 # 转换为0基索引 start_col = self.col_name_to_num(start_col_str) - 1 # 转换为0基索引 # 解析结束地址 end_col_str = ''.join([c for c in end_addr if c.isalpha()]) end_row_str = ''.join([c for c in end_addr if c.isdigit()]) end_row = int(end_row_str) - 1 # 转换为0基索引 end_col = self.col_name_to_num(end_col_str) - 1 # 转换为0基索引 # 验证范围是否超出表格边界 if start_row >= max_rows or end_row >= max_rows or start_col >= max_cols or end_col >= max_cols: raise ValueError("单元格范围超出表格边界") return start_row, end_row, start_col, end_col except Exception as e: raise ValueError(f"解析单元格范围失败: {str(e)}") def col_name_to_num(self, name): """ 将Excel列名转换为数字 (A->1, B->2, ..., Z->26, AA->27, ...) """ num = 0 for c in name.upper(): num = num * 26 + (ord(c) - ord('A') + 1) return numdef main(): root = tk.Tk() app = ExcelSumApp(root) root.mainloop()if __name__ == "__main__": main()
