Python配合PyQt5构建Excel数据分析系统：从pandas处理到matplotlib可视化

简介一套基于Python开发的Excel数据分析系统包含完整源码、可执行程序、配置说明书与使用说明书主要面向毕设学生和Python实战学习者项目从环境配置到功能实现均有说明可帮助理解完整的桌面软件开发流程。系统基于PyQt5构建图形界面集成pandas、matplotlib、xlrd支持导入Excel、数据提取、定向筛选、多表合并、统计排行、图表生成与贡献度分析所有功能均可通过图形界面快速操作。压缩包共688个文件约100.28MB包含535个Python源文件、23个exe可执行程序、pyd/dll运行库、12个xls示例表格、14张png界面截图、doc/txt说明书及ui界面文件结构与依赖齐全。配套说明书详细说明了Windows 7/10、Python 3.6与PyCharm环境搭建以及os、sys、glob、numpy等依赖的用法源码经过严格调试可直接运行适合用作毕业设计基础项目也便于按模块学习界面开发和数据分析实战。目前已有587人学习下载。1. 为什么这套Excel数据分析系统值得自己动手拆一遍前一阵我拆了一套Python开发的Excel数据分析系统源码里混合着activate.bat、sysconfig.cfg和可执行exe看目录结构就知道是用venv隔离环境后打包出来的。这套系统把常见的Excel操作收敛进一个PyQt5窗口导入EXCEL、提取列表数据、定向筛选、多表合并、多表统计排行、生成图表、贡献度分析界面不花哨但每个功能都命中职场表格处理的真实痛点。对正在做毕设的学生来说它是完整可跑的pandas实战样例对想练手的Python学习者它展示了glob文件扫描、DataFrame筛选合并、matplotlib嵌入Qt这套组合拳。我把运行配置、核心代码和打包过程都复现了一遍下面按数据层、业务层、可视化和交付物这条线拆开讲踩过的坑也都标出来。2. 环境搭建与数据层设计从glob文件扫描到pandas DataFrame2.1 开发环境选型与模块分工系统指定了Python 3.6和PyCharm 2017.3.3界面用Qt Designer画运行时用PyQt5承载窗口部件。数据计算基本交给pandas和它底层的numpy图表展示由matplotlib完成Excel文件解析走xlrd。这套组合在Windows 7/10上兼容性很好PyCharm里直接解释器选venv目录下的python.exe即可运行源码。模块分工如下表模块职责在系统中的具体入口glob扫描目录下的Excel文件glob.glob(*.xlsx)os / sys拼接路径、识别打包环境os.path.join、sys._MEIPASSpandas数据存储与聚合read_excel、concat、groupbyPyQt5窗口、表格、按钮交互QMainWindow、QTableWidgetmatplotlib图表绘制并嵌入QtFigureCanvasQTAggxlrd读取xls格式的Excelpd.read_excel(enginexlrd)其中glob这个内置模块常被忽略但在批量导入文件时它比os.listdir更省事因为支持通配符模式匹配。数据层统一用DataFrame承载一张表的内容多张表则放进一个列表后续所有筛选和合并都围绕这个内存结构展开。2.2 用glob批量定位Excel文件导入EXCEL的第一步不是弹出文件选择框而是先扫描某个数据目录下所有表格文件。源码里用glob匹配xlsx和xls再排序去重保证每次导入的先后顺序一致避免合并时行序乱掉import glob import os def scan_excel_files(data_dir): # 同时匹配 .xlsx 和 .xls 两种后缀 xlsx_files glob.glob(os.path.join(data_dir, *.xlsx)) xls_files glob.glob(os.path.join(data_dir, *.xls)) all_files xlsx_files xls_files # 去重并排序保证导入顺序稳定 return sorted(set(all_files))glob.glob传入的模式里*是通配符*.xlsx匹配目录下所有以.xlsx结尾的文件。set去重是为了防止某些目录下同时存在符号链接导致同一文件被匹配两次sorted则保证文件列表按路径字典序展示界面表格里不会忽前忽后。我一般还会在这之后过滤掉~$开头的临时文件因为Excel打开文档时会生成这种隐藏锁文件glob也能匹配到它们# 过滤Excel临时锁文件 files [f for f in scan_excel_files(data) if not os.path.basename(f).startswith(~$)]这一步在真实工作目录里很常见毕设源码可能没写但接真实数据时一定要补上。2.3 xlrd引擎与pandas读取的兼容性边界读取Excel是数据层最容易翻车的位置。xlrd库在2.0版本之后终止了对xlsx格式的支持只保留xls所以如果安装的是新版xlrdpd.read_excel读xlsx会直接报错。安全的做法是按后缀选择engineimport pandas as pd def read_excel_safe(file_path): # .xlsx 用 openpyxl.xls 用 xlrd if file_path.endswith(.xlsx): return pd.read_excel(file_path, engineopenpyxl) elif file_path.endswith(.xls): return pd.read_excel(file_path, enginexlrd, dtypestr) else: raise ValueError(不支持的Excel格式: file_path)engine是pandas read_excel函数中指定解析库的参数。dtypestr只对xls生效目的是把身份证、订单号这类长数字列保留成文本避免自动转成科学计数法。这里要注意的是xlrd 1.2.0是最后支持xls且不携带安全问题的版本requirements.txt里最好锁定为xlrd1.2.0。如果系统要同时处理xls和xlsx代码里两者引擎切换逻辑必须保留只装一个库没法两头兼顾。2.4 提取列表数据从DataFrame到QTableWidget系统里的提取列表数据功能本质是把当前DataFrame的列名和数据行转换成界面控件能显示的格式。PyQt5的QTableWidget不认DataFrame需要手动设置行列数和单元格内容def dataframe_to_table(df, table_widget): # 设置表格行列数 rows, cols df.shape table_widget.setRowCount(rows) table_widget.setColumnCount(cols) # 设置列标题 table_widget.setHorizontalHeaderLabels(df.columns.astype(str).tolist()) # 逐单元格填充数据 for i in range(min(rows, 5000)): # 限制最多显示5000行 for j in range(cols): item QTableWidgetItem(str(df.iat[i, j])) table_widget.setItem(i, j, item)df.shape返回(行数, 列数)setRowCount和setColumnCount先固定表格尺寸。setHorizontalHeaderLabels接受的是字符串列表Excel列名可能是数字或日期所以先astype(str)处理。填充时用df.iat取值比df.iloc更快因为iat按行号列号直接取标量。限制5000行是为了防止几百MB的Excel把界面卡死大文件应在导入时给出数据量过大已截断显示的提示。3. 定向筛选与多表合并核心业务逻辑的实现3.1 定向筛选模糊匹配与条件组合定向筛选在界面上表现为选择一个列名、输入一个关键词程序返回该列包含关键词的所有行。底层实现是pandas的布尔索引加字符串匹配def filter_rows(df, column, keyword): keyword keyword.strip() if not keyword: return df.copy(), 筛选条件为空返回全部数据 # 统一转字符串后做包含匹配naFalse 忽略空值 mask df[column].astype(str).str.contains(keyword, caseFalse, naFalse) result df.loc[mask, :] return result, f匹配到 {len(result)} 行共 {len(df)} 行这里有两个关键参数caseFalse让匹配不区分大小写适合公司名称这类容易大小写混用的文本naFalse让空值行不进入匹配否则NaN会被转成字符串nan输入na可能误命中一堆空值。df.loc[mask, :]比直接df[mask]语义更明确表示按行筛选且保留所有列。筛选结果必须返回副本即调用.copy()因为后续会把这个结果加入合并列表如果和原始DataFrame共用内存后续排序会互相污染。3.2 多表合并列名统一与concat参数合并多张Excel表前先要确认各表列名是否一致。源码中直接用了pd.concat但如果列名有差异合并结果会出现大量NaN列。我一般在concat之前先做一个列名归一化import pandas as pd def normalize_columns(frames, mappingNone): normalized [] for idx, df in enumerate(frames): if mapping: df df.rename(columnsmapping) # 统一把列名转成字符串 df.columns df.columns.astype(str) normalized.append(df) return normalized def merge_frames(frames): frames normalize_columns(frames) # 纵向拼接ignore_index 重建行号sortFalse 保持列顺序 merged pd.concat(frames, axis0, ignore_indexTrue, sortFalse) return mergedaxis0表示按行堆叠各表列名相同时效果等同于SQL里的UNION ALL。ignore_indexTrue会丢掉原表自带的行号重新生成0到n-1不设置的话合并后行索引会出现重复后续groupby的排序结果会很怪。sortFalse让concat不按列名字母排序而是以第一个传入的DataFrame列顺序为基准这样界面表格列显示不会跳变。如果怀疑两张表列名有差异用set(df1.columns) ^ set(df2.columns)找出对称差集提前决定是补空列还是删除。3.3 多表统计排行groupby聚合与排序规则排行功能把合并后的大表按一个业务字段分组对另一个数值字段求和再按合计值降序取前N名。源码的groupby写法值得注意def rank_data(df, group_col, value_col, top_n10): # 只保留需要的两列减少groupby内存消耗 base df[[group_col, value_col]].copy() # 分组求和reset_index 让分组列回到普通列 stat base.groupby(group_col, as_indexFalse)[value_col].sum() stat.columns [group_col, 合计] # 降序排列取前N重排行号 result stat.sort_values(合计, ascendingFalse).head(top_n).reset_index(dropTrue) return resultgroupby的as_indexFalse参数直接把分组字段保留为一列省去reset_index。[value_col].sum()只对数值列求和如果提交的value_col是文本pandas会抛错误提示所以调用前最好先用pd.to_numeric做一次安全转换。sort_values的ascendingFalse是降序排行榜规则通常是销量越高名次越靠前head(top_n)截断后reset_index(dropTrue)让名次从0开始写界面时直接用行号加1就是最终名次。3.4 功能联动与备份筛选结果如何进入下一步系统的操作链是导入Excel → 从列表里选中某张表 → 定向筛选 → 将筛选结果加入合并队列 → 多表合并 → 排行/图表。这里最容易出错的点是用户对同一张表执行多次筛选后一次筛选是否基于原始表源码的处理方式比较稳妥原始DataFrame单独保存筛选时总是从原始表取副本origin_frames {} # 文件路径 - 原始DataFrame filtered_frames {} # 文件路径 - 筛选后的DataFrame def apply_filter(file_key, column, keyword): df origin_frames[file_key].copy() # 总是从原始表出发 result, msg filter_rows(df, column, keyword) filtered_frames[file_key] result return result, msg如果不复制原始表第二次筛选会在第一次筛选结果上继续过滤用户会误以为数据丢了。这个设计是这套系统最值得借鉴的地方它把原始数据和派生数据清晰分开。我在实际二次开发时还加了一个重置筛选按钮本质就是把filtered_frames[key]重新赋值为origin_frames[key].copy()用户体验提升明显。4. 图表生成与贡献度分析matplotlib嵌入PyQt54.1 将matplotlib画布嵌入Qt窗口系统生成图表时不是弹出独立窗口而是把matplotlib图片直接画在主界面的QWidget区域里实现方式是使用FigureCanvasQTAgg作为Qt画布组件。这个组件继承自Qt的QWidget可以addWidget到布局中from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg from matplotlib.figure import Figure class DataChart(FigureCanvasQTAgg): def __init__(self, parentNone, width6, height4, dpi100): # 创建Figure对象设定画布像素大小 self.fig Figure(figsize(width, height), dpidpi) super().__init__(self.fig) self.setParent(parent) def clear_plot(self): # 每次绘制前清空之前的图形 self.fig.clear()figsize(6,4)的单位是英寸dpi100表示每英寸100像素所以这个画布实际尺寸是600*400像素。clear_plot是必须的因为图表控件会被反复调用不清空的话旧线条会残留在新图上。嵌入时的完整流程是在Qt Designer里放一个空的QWidget占位然后代码里用QHBoxLayout把DataChart实例添加进去替代占位控件。4.2 排行图表的绘制参数与样式排行数据适合用横向条形图因为分类名称通常较长横向排列能完整显示文字。绘制时需要对y轴做反转处理def plot_ranking(self, labels, values): self.clear_plot() ax self.fig.add_subplot(111) # 横向条形图颜色使用统一的浅蓝色 ax.barh(labels, values, color#4C72B0) # 反转y轴让第一条数据显示在最上方和表格排行顺序一致 ax.invert_yaxis() # 在条形图末端标注数值 for i, v in enumerate(values): ax.text(v max(values) * 0.01, i, str(v), vacenter) ax.set_xlabel(合计值) ax.set_title(多表统计排行) self.draw()barh的第一个参数是y轴标签列表第二个是长度列表。invert_yaxis是重点matplotlib默认把列表第一个元素放在y轴最底部而排行榜习惯是第一名在最顶部所以必须反转。ax.text在条形末端加标签v max(values)*0.01让文字稍微超出条形末端避免重叠vacenter垂直居中。这套参数直接决定了图表在答辩和汇报时是否好懂。4.3 贡献度分析占比计算与饼图优化贡献度分析就是求每个分类的数值占总体的百分比然后绘制饼图。源码里计算占比的逻辑很清晰但绘制饼图时要注意分类过多的情况def contribution(df, category_col, value_col): stat df.groupby(category_col)[value_col].sum().reset_index() total stat[value_col].sum() if total 0: return stat, 总值为0无法计算贡献度 stat[占比] (stat[value_col] / total * 100).round(2) stat stat.sort_values(占比, ascendingFalse) return stat, 计算完成 def plot_pie(self, stat, top_n8): self.clear_plot() # 只保留前 top_n 个分类其余合并为“其他” main stat.head(top_n).copy() other stat.iloc[top_n:] if len(other) 0: other_row pd.DataFrame({占比: [other[占比].sum()], category_col: [其他]}) main pd.concat([main, other_row], ignore_indexTrue) ax self.fig.add_subplot(111) ax.pie(main[占比], labelsmain[category_col], autopct%1.1f%%, startangle90, counterclockFalse) ax.axis(equal) self.draw()total0的判断必不可少筛选后的数据可能全为空值sum为0再除会得到inf。饼图参数里autopct%1.1f%%让扇区显示保留一位小数的百分比startangle90让起始角度从y轴正方向开始counterclockFalse让扇区顺时针排列。合并小于前N名的分类为其他是很常见的可视化习惯避免饼图被十几个细碎扇区切割得无法阅读。axis(equal)保证饼图是正圆否则显示成椭圆会失真。4.4 图表导出与刷新系统提供把当前图表保存为图片的功能这个功能很大程度方便了报告撰写。保存的关键是拿到Figure对象的savefig方法def export_chart(self, filename): # 设置300dpi适合打印和插入文档 self.fig.savefig(filename, dpi300, bbox_inchestight)dpi300是印刷级清晰度bbox_inchestight自动裁掉图表边缘多余的留白。在PyQt5里触发保存的按钮一般配合QFileDialog使用file_path, _ QFileDialog.getSaveFileName(None, 保存图表, chart.png, PNG图片 (*.png)) if file_path: chart.export_chart(file_path)注意getSaveFileName返回两个值第二个是文件类型过滤器下划线用来丢弃。保存后的图片可以直接插入Word报告或PPT这在学校论文和项目验收里非常加分。5. 打包可执行程序与文档编写PyInstallervenv5.1 虚拟环境激活与依赖导出源码包里有activate.bat、deactivate.bat和pyvenv.cfg这是venv虚拟环境的标志。打包exe前一定要在当前项目目录创建全新虚拟环境避免全局环境里多余的包混进程序。激活虚拟环境的命令在不同系统下不同# Windows 下激活 venv\Scripts\activate.bat # Linux/macOS 下激活 source venv/bin/activate激活后命令行前缀出现(venv)字样再安装依赖pip install PyQt5 pyqt5-tools pandas matplotlib xlrd1.2.0 pip freeze requirements.txtpip freeze导出的文件每一行是包名版本号格式方便别人用pip install -r requirements.txt复现环境。这里把xlrd锁成1.2.0是必须操作因为新版xlrd不支持xlsx直接pip install xlrd只会装上最新版程序一读xlsx就莫名其妙报错。5.2 PyInstaller打包参数与资源文件打包命令需要理解几个参数的作用# -F 打成一个单独exe-w 运行时不弹出黑色控制台 pyinstaller -F -w main.py-F把Python解释器、依赖库和脚本全部打进一个exe文件方便分发但启动会稍慢。 -w是windowed模式禁止显示命令行窗口如果忘了加双击exe时会先闪过一个黑框影响观感。打包后的exe在dist目录下main.py的名字会作为exe文件名。如果程序里有外部配置文件或图标需要额外指定pyinstaller -F -w main.py --add-data config.ini;. --iconapp.ico--add-data的参数格式在Windows下用分号分隔源文件和目标目录冒号则用于Linux/macOS。config.ini的打开路径要用第6章提到的sys._MEIPASS处理否则运行时找不到文件这是PyInstaller打包最常见的问题。5.3 配置说明书与使用说明书的写作要点源码包附带的两份文档配置说明书和使用说明书其实是衡量一个项目是否成熟的重要标准。配置说明书面向的是让程序跑起来的环境至少包括操作系统版本、Python版本、第三方库列表、如何安装依赖、如何启动源码。使用说明书面向操作流程建议按功能按钮逐个拆解。身为一套毕设项目文档里还要写清楚测试数据放在哪个目录以及程序默认读取哪个文件夹。给使用说明书写操作步骤时我习惯用预期结果一词比如点击导入EXCEL按钮后表格控件会显示第一个sheet的数据状态栏提示成功导入N个文件。这样用户每做一步就能确认是否操作成功比单纯罗列菜单路径有效得多。5.4 打包后自测清单打包完成后不能只双击exe看界面能不能打开必须走完整业务流程。我一般按以下清单自测步骤操作预期结果1准备三张含相同表头的xlsx能被glob扫描到2点击导入EXCEL表格显示每张表的数据3按关键词定向筛选行数明显减少状态栏有提示4多表合并行数为三张表行数之和5多表统计排行合计值降序排列6生成图表并保存PNG图片打开清晰无乱码这个清单也适合直接写进程序使用说明书的附录用户在拿到源码和exe后可以按表验证环境是否正常减少不必要的沟通成本。6. 排错与验证三个必踩的坑6.1 xlrd版本报错最常见的报错是xlrd 2.0 supports only xls。这是xlrd新版强行切掉xlsx导致的。两种解决办法把xlrd降到1.2.0或在read_excel里显式指定engineopenpyxl。我的建议是同时做代码和依赖清单双保险。6.2 中文路径与_MEIPASSWindows路径含中文时打包后的exe经常出现找不到文件或编码错误。这是因为PyInstaller会把临时资源解压到系统临时目录中文字符在部分机器上会乱码。解决办法是用sys._MEIPASS获取实际资源目录import sys import os def resource_path(relative_path): # 打包后资源在临时解压目录源码运行时在当前脚本目录 if hasattr(sys, _MEIPASS): base sys._MEIPASS else: base os.path.dirname(os.path.abspath(__file__)) return os.path.join(base, relative_path)判断hasattr(sys, _MEIPASS)是因为只有PyInstaller打包后的程序才有这个属性源码运行时走else分支。所有读取配置、图标、模板文件的地方都改为调用resource_path中文路径问题基本就能解决。6.3 用脚本验证全流程为验证系统核心逻辑没有因环境差异而破坏我习惯写一个无界面的冒烟测试脚本直接调用数据层和业务层函数import glob import pandas as pd # 模拟导入三个文件 files sorted(glob.glob(data/*.xlsx)) frames [pd.read_excel(f, engineopenpyxl) for f in files] # 多表合并 merged pd.concat(frames, ignore_indexTrue) assert len(merged) sum(len(f) for f in frames) # 定向筛选 mask merged[客户].astype(str).str.contains(科技, naFalse) filtered merged[mask] assert len(filtered) 0 # 贡献度分析 stat merged.groupby(产品)[金额].sum().sort_values(ascendingFalse) print(stat.head(5))assert语句会在条件不满足时抛异常比print更直接。这段脚本不依赖PyQt5界面纯函数逻辑哪里出错立刻看得见。在打包exe之前跑通这个脚本可以提前拦截90%的数据处理问题如果脚本通过而界面操作异常问题多半在PyQt5信号槽绑定或控件刷新上这时逐行检查clicked.connect绑定的槽函数即可。本文还有配套的精品资源点击获取