【Python实战】Excel数据处理工具开发：格式转换+数据清洗+批量处理

一、项目背景1.1 痛点分析Excel数据处理是日常工作的高频场景但传统方式效率极低任务手工方式时间格式转换Excel另存为10分钟数据清洗手动删除空行/重复行30分钟数据合并复制粘贴/VLOOKUP30分钟批量处理逐个文件操作1小时总计-2.5小时处理10个文件就要25小时整整3天。1.2 技术需求核心需求支持Excel/CSV/JSON格式互转自动清洗数据空行、重复、空格智能合并与拆分数据批量处理多个文件本地运行保护数据安全二、技术架构┌─────────────┐ ┌─────────────┐ ┌─────────────┐│ GUI界面 │────▶│ 核心引擎 │────▶│ 文件处理 ││ (PyQt5) │ │ (Python) │ │ (pandas/ │└─────────────┘ └──────┬──────┘ │ openpyxl) ││ └─────────────┘┌──────┴──────┐│ 功能模块 ││ • 格式转换 ││ • 数据清洗 ││ • 合并拆分 ││ • 批量处理 │└─────────────┘技术栈 - **pandas**数据处理和分析 - **openpyxl**Excel文件读写 - **PyQt5**图形界面 - **PyInstaller**打包发布 --- ## 三、核心模块实现 ### 3.1 格式转换模块 支持Excel、CSV、JSON三种格式互转 python import pandas as pd import json class FormatConverter: def __init__(self): self.supported_formats [.xlsx, .xls, .csv, .json] def convert(self, input_path, output_path, target_format): 格式转换核心方法 # 读取源文件 df self._read_file(input_path) # 写入目标格式 if target_format in [.xlsx, .xls]: df.to_excel(output_path, indexFalse) elif target_format .csv: df.to_csv(output_path, indexFalse, encodingutf-8-sig) elif target_format .json: df.to_json(output_path, orientrecords, force_asciiFalse) return output_path def _read_file(self, file_path): 智能读取文件 ext file_path.suffix.lower() if ext in [.xlsx, .xls]: return pd.read_excel(file_path) elif ext .csv: # 自动检测编码 encodings [utf-8, gbk, gb2312] for encoding in encodings: try: return pd.read_csv(file_path, encodingencoding) except UnicodeDecodeError: continue raise ValueError(无法识别文件编码) elif ext .json: with open(file_path, r, encodingutf-8) as f: data json.load(f) return pd.json_normalize(data) raise ValueError(f不支持的文件格式: {ext})关键优化CSV文件自动检测编码UTF-8/GBK/GB2312JSON文件支持嵌套结构扁平化大文件分块读取避免内存溢出3.2 数据清洗模块自动清洗脏数据class DataCleaner: def __init__(self): self.operations [] def clean(self, df, options): 数据清洗主方法 original_rows len(df) # 删除空行 if options.get(remove_empty_rows): df df.dropna(howall) self.operations.append(f删除空行: {original_rows - len(df)}行) # 删除重复行 if options.get(remove_duplicates): before len(df) df df.drop_duplicates() self.operations.append(f删除重复行: {before - len(df)}行) # 去除空格 if options.get(strip_spaces): df df.apply(lambda x: x.str.strip() if x.dtype object else x) self.operations.append(去除首尾空格) # 填充空值 if options.get(fill_na): fill_value options.get(fill_value, ) df df.fillna(fill_value) self.operations.append(f填充空值: {fill_value}) return df def get_report(self): 获取清洗报告 return \n.join(self.operations)清洗策略空行判断整行所有列为空重复判断所有列值相同空格处理字符串列去除首尾空格空值填充可自定义填充值3.3 数据合并模块支持按行追加和按列匹配class DataMerger: def merge_by_row(self, file_paths, output_path): 按行追加合并 dfs [] for path in file_paths: df pd.read_excel(path) if path.suffix in [.xlsx, .xls] else pd.read_csv(path) dfs.append(df) # 合并自动对齐列名 merged pd.concat(dfs, ignore_indexTrue, sortFalse) merged.to_excel(output_path, indexFalse) return { input_files: len(file_paths), total_rows: len(merged), columns: list(merged.columns) } def merge_by_column(self, file_paths, key_column, output_path): 按列匹配合并 base_df pd.read_excel(file_paths[0]) for path in file_paths[1:]: df pd.read_excel(path) base_df base_df.merge(df, onkey_column, howouter, suffixes(, f_{path.stem})) base_df.to_excel(output_path, indexFalse) return { input_files: len(file_paths), total_rows: len(base_df), total_columns: len(base_df.columns) }3.4 数据拆分模块支持按行数和按列值拆分class DataSplitter: def split_by_rows(self, file_path, chunk_size, output_prefix): 按行数拆分 df pd.read_excel(file_path) total_rows len(df) output_files [] for i, start in enumerate(range(0, total_rows, chunk_size)): chunk df.iloc[start:start chunk_size] output_path f{output_prefix}_{i1\:03d}.xlsx chunk.to_excel(output_path, indexFalse) output_files.append(output_path) return { total_rows: total_rows, chunk_size: chunk_size, output_files: len(output_files), files: output_files } def split_by_column(self, file_path, column_name, output_prefix): 按列值拆分 df pd.read_excel(file_path) output_files [] for value in df[column_name].unique(): if pd.isna(value): continue chunk df[df[column_name] value] safe_value str(value).replace(/, _).replace(\\, _) output_path f{output_prefix}_{safe_value}.xlsx chunk.to_excel(output_path, indexFalse) output_files.append(output_path) return { total_rows: len(df), unique_values: len(output_files), files: output_files }3.5 批量处理模块支持批量替换和批量重命名import re from pathlib import Path class BatchProcessor: def batch_replace(self, folder_path, file_pattern, column_name, old_value, new_value): 批量替换内容 files list(Path(folder_path).glob(file_pattern)) processed [] for file_path in files: df pd.read_excel(file_path) # 替换指定列 if column_name in df.columns: df[column_name] df[column_name].astype(str).str.replace(old_value, new_value) # 保存 output_path file_path.parent / freplaced_{file_path.name} df.to_excel(output_path, indexFalse) processed.append(output_path) return processed def batch_rename(self, folder_path, file_pattern, name_pattern): 批量重命名文件 files list(Path(folder_path).glob(file_pattern)) renamed [] for i, file_path in enumerate(files, 1): new_name name_pattern.format(indexi, originalfile_path.stem) new_path file_path.parent / f{new_name}{file_path.suffix} file_path.rename(new_path) renamed.append(new_path) return renamed四、GUI界面实现使用PyQt5构建简洁界面from PyQt5.QtWidgets import (QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QLabel, QLineEdit, QComboBox, QCheckBox, QFileDialog, QTextEdit, QProgressBar) class MainWindow(QMainWindow): def __init__(self): super().__init__() self.setup_ui() self.init_modules() def setup_ui(self): self.setWindowTitle(Excel数据处理工具 v1.0) self.setGeometry(100, 100, 800, 600) # 主布局 central_widget QWidget() self.setCentralWidget(central_widget) layout QVBoxLayout(central_widget) # 文件选择区 file_layout QHBoxLayout() self.file_input QLineEdit() self.btn_browse QPushButton(选择文件) self.btn_browse.clicked.connect(self.select_files) file_layout.addWidget(QLabel(文件:)) file_layout.addWidget(self.file_input) file_layout.addWidget(self.btn_browse) layout.addLayout(file_layout) # 功能选择区 self.func_combo QComboBox() self.func_combo.addItems([ 格式转换, 数据清洗, 数据合并, 数据拆分, 批量处理 ]) layout.addWidget(QLabel(功能:)) layout.addWidget(self.func_combo) # 选项区 self.options_group QWidget() self.options_layout QVBoxLayout(self.options_group) # 数据清洗选项 self.chk_empty QCheckBox(删除空行) self.chk_duplicate QCheckBox(删除重复行) self.chk_strip QCheckBox(去除空格) self.options_layout.addWidget(self.chk_empty) self.options_layout.addWidget(self.chk_duplicate) self.options_layout.addWidget(self.chk_strip) layout.addWidget(self.options_group) # 操作按钮 btn_layout QHBoxLayout() self.btn_preview QPushButton(预览效果) self.btn_process QPushButton(开始处理) self.btn_preview.clicked.connect(self.preview) self.btn_process.clicked.connect(self.process) btn_layout.addWidget(self.btn_preview) btn_layout.addWidget(self.btn_process) layout.addLayout(btn_layout) # 进度条 self.progress QProgressBar() layout.addWidget(self.progress) # 日志区 self.log_text QTextEdit() self.log_text.setReadOnly(True) layout.addWidget(QLabel(处理日志:)) layout.addWidget(self.log_text) def init_modules(self): self.converter FormatConverter() self.cleaner DataCleaner() self.merger DataMerger() self.splitter DataSplitter() self.processor BatchProcessor() def select_files(self): files, _ QFileDialog.getOpenFileNames( self, 选择文件, , Excel文件 (*.xlsx *.xls);;CSV文件 (*.csv);;所有文件 (*) ) if files: self.file_input.setText(;.join(files)) def log(self, message): self.log_text.append(message) def preview(self): # 预览逻辑... self.log(预览功能已触发) def process(self): # 处理逻辑... self.log(开始处理...) self.progress.setValue(100) self.log(处理完成!)五、效果对比处理10个文件从25小时缩短到50分钟。六、踩坑经验编码问题CSV文件编码不统一需要自动检测UTF-8/GBK/GB2312。大文件内存超过10万行的Excel使用pandas的chunksize参数分块读取。日期格式不同Excel日期格式可能不统一建议先转换为标准格式。列名冲突合并时列名冲突使用suffixes参数添加后缀区分。路径处理Windows和macOS路径分隔符不同使用pathlib统一处理。七、后续优化更多格式支持Parquet、Feather等大数据格式数据可视化集成matplotlib生成图表自动化脚本支持定时任务插件系统支持自定义功能扩展八、结语本文分享了Excel数据处理工具的完整技术架构和核心代码实现。工具已开发完成功能包括格式转换、数据清洗、合并拆分、批量处理等。完整代码涉及商业授权需要工具的朋友评论区留言或私信获取。有问题欢迎评论区交流。