简介面向MATLAB数据处理场景这份资源专门讲解Excel文件的读取与写入围绕读取与写入两个核心函数进行系统梳理适合需要实现数据导入导出、处理表格数据的初学者和进阶用户。资源包共包含15个文件包括8个m格式示例脚本、5个xlsx数据工作簿、1个txt说明文档和1个xls旧版文件压缩包整体仅947KB结构清晰其中脚本覆盖读取、写入、随机数据生成等典型操作Excel文件可用于直接验证txt文档补充函数背景与使用说明。目前已有728人学习下载。借助这些代码与数据读者可以掌握数值矩阵、文本内容与原始数据三种返回值的区别学会灵活指定工作表与单元格范围并了解新旧Excel格式兼容性、异常处理及批量写入的注意事项。同时示例中的函数调用方式可直接迁移到实际项目中提升数据交互效率。 很多人一听到“Excel读写”就觉得是入门级的东西无非就是打开文件、填个表、存个盘。但真正被Excel折磨过的人都知道Excel的读写远远不是“打开-编辑-保存”这么简单。你可能是用Python批量处理几千个报表的工程师可能是用VBA给业务部门写自动化工具的内勤也可能是要定时把Excel数据导入数据库的后端开发——不同角色面对同一个Excel文件读写的逻辑和坑完全不一样。这篇就围绕“Excel文件的读取和写入”这个主题把我实际开发中踩过的坑、用顺手的方案、以及遇到的热门问题一次性说清楚。不整虚的全是可落地的干货。1. 内容整体设计与思路拆解先明确你的使用场景再选工具1.1 核心需求解析你到底是哪种“读取和写入”我见过太多人一上来就问“哪个库读写Excel最好”这个问题本身就问错了。Excel的读写需求至少可以拆成三类每一类的技术选型完全不同第一类是数据型读写核心是表格里的值。比如把数据库导出的数据写进Excel或者把Excel里的几千行数据读出来做分析、入库。这类需求的本质是“数据的搬运”不在乎格式多花哨只在乎速度、准确性和数据类型是否正确。第二类是样式型读写核心是格式。比如给报表设置单元格背景色、边框、合并单元格、调整列宽、写入公式、插入图表。这类需求比数据读写复杂一个量级因为Excel的单元格样式属性太多了而且不同库对样式的支持程度差别巨大。第三类是对象型读写核心是Excel里的非单元格元素。比如Shape形状、图片、SmartArt、数据透视表、控件按钮。这个领域最典型的例子就是热搜词里的“excel vba shape.method”意味着你要操作的是Shape对象的方法而不是单元格的值。你只有先明确自己属于哪一类才能选对工具。拿Python生态举例数据型读写首选pandas样式型读写用openpyxl对象型读写基本只能靠VBA或者win32com调用Excel应用本身。选错工具的结果就是——花了大半天写代码最后发现你要的功能这个库根本不支持或者支持得极其别扭。1.2 主流方案对比Python、VBA、C#/Java到底怎么选方案适用场景优势劣势Python pandas数据分析、批量数据读写处理速度快、语法简洁、生态丰富写样式比较麻烦格式控制弱Python openpyxl需要精细控制格式的读写对样式、公式、图表支持完整大文件读写速度慢内存占用高VBAExcel内自动化、操作Shape等对象深度集成Excel能操作所有对象模型只能在Windows桌面环境运行C# NPOI/EPPlus后台服务生成Excel文件服务端能力强不依赖Office环境中文资料相对少学习曲线陡Java POI企业级后端处理Excel稳定可靠功能全面API设计较繁琐代码量大这个表我做了至少五年方案才真正理清楚。刚入行的时候我迷信pandas能搞定一切结果遇到一个需求要给合并单元格加边框pandas根本不支持——最后还是openpyxl重新处理一遍。后来又觉得VBA万能直到需要部署到Linux服务器上定时生成Excel报表VBA直接出局。拿热搜词“c# 后台处理前端传过来的excel”来说这种场景下C# NPOI几乎是标准答案。前端上传Excel文件你只需要在服务端读取文件流解析第一行表头、校验必填字段、做数据格式检查然后判断是入库还是回写错误信息给前端。整个过程不能用VBA因为服务器上没装Office也不能用COM组件性能和并发都扛不住。2. 核心细节解析与实操要点读文件要留心的几个关键环节2.1 文件格式的“坑”.xls和.xlsx根本不是一回事很多人写Excel读写代码第一个坑就踩在文件格式上。.xls是Excel 97-2003的二进制格式.xlsx是Office 2007之后基于XML的格式两者底层的存储机制完全不同。代码层面最直接的后果就是很多库同时支持两种格式但处理方式不一样。比如pandas读取Excel时读.xlsx用的是openpyxl引擎读.xls用的却是xlrd引擎。而xlrd从2.0版本开始官方宣布只支持.xls不再支持.xlsx——你要是用老版本的代码去读.xlsx直接报错。实操建议是在自己的项目里能用.xlsx就统一用.xlsx。如果收到的是用户上传的.xls文件第一道工序永远应该是“格式转换”。用Python可以一行代码搞定但要注意转换后别丢了格式信息import pandas as pd # 读取老格式文件 df pd.read_excel(old_file.xls, enginexlrd) # 写为新格式 df.to_excel(new_file.xlsx, indexFalse)2.2 数据类型的“隐形炸弹”读出来全是字符串这是Excel读取中最高频的问题没有之一。你Excel里明明看到的是数字但程序读出来是字符串你看到的日期读出来变成了数字戳serial number。很多新手在这上面Debug大半天最后才发现是数据类型的问题。原理上.xlsx格式的单元格底层有两种存储方式inlineStr内嵌字符串和sharedString共享字符串表而纯数字和日期其实都是以数字形式存储的日期靠数字的显示格式来决定如何展示。比如Excel中的“2024-01-15”底层存的其实是数字“45285”只是套用了日期格式才有这种显示效果。所以读Excel的时候类型转换必须主动做不能靠猜。我的习惯是读取之后立即打印dtype和示例值确认import pandas as pd df pd.read_excel(data.xlsx, sheet_nameSheet1) print(df.dtypes) print(df.head(3))如果发现日期列是object类型或者数字列里有“,”千分符必须要清洗。热搜词里专门有一条“excel提单元格有数字汉字,只提取数字”这种场景最常见的处理是用正则表达式匹配数字部分import re def extract_number(value): match re.search(r\d(\.\d)?, str(value)) return float(match.group()) if match else None df[数量] df[数量].apply(extract_number)另外一个常被忽略的点是pandas读取时默认会把整列推断为一种数据类型当某一列同时存在“数字”和“文本”时整列会变成object字符串。这就是为什么你明明筛选条件没问题出来的结果却总是空。2.3 大文件处理的性能焦虑为什么读个Excel要卡半天Excel不是数据库它设计出来是给人操作界面用的不是给程序大规模读写的。当文件超过1万行、几十个sheet、每列几千个单元格的时候任何一个库都会变慢。这里有一个重要的经验法则能用CSV就不要用Excel。如果你的流程只是数据读写不涉及格式完全可以先把Excel转成CSV再处理速度能提升一个数量级。如果必须直面大Excel文件openpyxl提供了只读模式read_onlyTrue按行流式读取而不是一次性加载整个工作簿到内存from openpyxl import load_workbook wb load_workbook(large_data.xlsx, read_onlyTrue, data_onlyFalse) ws wb[Sheet1] for row in ws.iter_rows(min_row2, values_onlyTrue): # 逐行处理不占内存 process(row)这个模式对内存的优化非常明显。实测一个10万行、20列的Excel普通模式读下来内存占用能到1GB以上只读模式基本控制在100MB以内。3. 实操过程与核心环节实现从零搭建一套Excel读写方案3.1 Python openpyxl 的写入实操从建工作簿到写公式openpyxl是我个人最常用、也最推荐的用于样式型Excel写操作的库。下面这段代码演示了大多数报表场景的完整写入流程from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter # 1. 创建工作簿和工作表 wb Workbook() ws wb.active ws.title 销售报表 # 2. 写入标题行并设置样式 headers [产品名称, 销量, 单价, 销售额] ws.append(headers) head_font Font(name微软雅黑, size12, boldTrue, colorFFFFFF) head_fill PatternFill(start_color4472C4, end_color4472C4, fill_typesolid) head_alignment Alignment(horizontalcenter, verticalcenter) for cell in ws[1]: cell.font head_font cell.fill head_fill cell.alignment head_alignment # 3. 写入业务数据 data [ [苹果, 120, 5.5, B2*C2], [香蕉, 85, 3.8, B3*C3], [橘子, 94, 4.2, B4*C4], ] for row_data in data: ws.append(row_data) # 4. 设置列宽用字母定位 ws.column_dimensions[A].width 18 ws.column_dimensions[B].width 12 ws.column_dimensions[C].width 12 ws.column_dimensions[D].width 14 # 5. 保存文件 wb.save(销售报表.xlsx)这段代码虽然短但里面有几个细节值得展开说。写入公式的时候公式字符串必须以等号开头这一点很直观但记住公式是在Excel打开时触发的用openpyxl写入的公式如果最终用户使用WPS打开有时候公式不会自动重算——需要检查WPS的“自动重算”设置。另一个实用经验是如果目标文件需要给不懂技术的人用最好把表头和公式都写清楚甚至可以加上数据验证DataValidation让填写的人从下拉框里选防止乱输。3.2 读取Excel并清洗数据解决“不标准”的用户数据实际工作中读Excel最痛苦的不是读——而是读出来的数据不干净。请求里的热搜词“excel表格 数据清洗”和“excel多条件筛选”就点出了这个痛点。业务人员手动维护的Excel表几乎必然存在空行、合并单元格、重复数据、格式不一致、单位混用等“脏数据”。我整理了一套通用的清洗流程大概分四步去空行和空列df.dropna(howall)和df.dropna(axis1, howall)可以快速删掉整行整列为空的数据。处理合并单元格先用fillna(methodffill)向下填充把合并单元格的值补全再处理就不容易出错。规范化文本去掉首尾空格、统一去除换行符、把中文括号替换为英文括号等等。类型转换把“1,200”转成1200把“85%”转成0.85把“2024.01.01”转成标准日期。举个例子如果用户上传的表里有“部门合并单元格”直接读出来时合并区域只有左上角有值其余是NaN。这时候需要做向下填充df pd.read_excel(部门数据.xlsx, engineopenpyxl) df[[部门, 负责人]] df[[部门, 负责人]].fillna(methodffill)清洗完之后才能进入数据校验和入库环节。热搜词里还有一条“excel导入数据库”这个流程的标准姿势是先读文件、清洗数据、用pandas拼接成DataFrame然后直接用df.to_sql()写入数据库。但注意要有异常捕获一条坏数据不能导致全部回滚我的做法是先做校验收集所有错误行生成一个新的Excel错误报告返回给前端告诉用户哪些行有问题、什么问题。3.3 VBA与Shape操作另一个维度的“读写”如果说前面的重点是“数据的读写”那么VBA的世界里还有一块是“Excel对象的读写”。热搜词有一条“excel vba shape.method”这是很多做仪表盘、动态图表、自动排版的人会碰到的东西。在Excel里Shape是漂浮在单元格之上的一类对象包括矩形、图片、按钮等。VBA操作Shape的基本思路是Sub 绘制矩形() Dim shp As Shape Set shp ActiveSheet.Shapes.AddShape(msoShapeRectangle, 100, 50, 120, 60) shp.Fill.ForeColor.RGB RGB(68, 114, 196) shp.TextFrame.Characters.Text 点击此处 shp.Name btn_1 End Sub这里最核心的认知是Shape对象有自己的坐标系Left、Top、Width、Height单位是磅point和单元格的行列坐标不是一套体系。如果你想把Shape精确对齐在某个单元格区域上需要把单元格的像素坐标和磅坐标互相转换这是VBA排版自动化中最容易翻车的地方。跨Sheet操作Shape时还有个经常遇到的细节AddShape返回的是Shape对象后续要用它时最好备份引用或者给它一个确定的Name因为Excel中很多操作会改变Shape的索引。我在帮业务部门做报价单模板时曾用VBA批量绘制了几十个屑圆框按钮结果一个循环里删除了某个Shape后索引全部错乱Debug了大半天才找到问题。解决方案是在循环中从后往前操作或者用Name直接定位。3.4 服务端读写C#/Java处理前端上传的Excel再来说后台处理场景。热搜词里“c# 后台处理前端传过来的excel”是很典型的服务端需求。这里用C# NPOI举例因为NPOI不依赖Office环境、兼容性强而且免费开源。核心流程分三步接收上传文件流、解析表格内容、执行后续业务。代码骨架如下using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; public ListDictionarystring, object ParseExcel(Stream fileStream) { var result new ListDictionarystring, object(); IWorkbook workbook new XSSFWorkbook(fileStream); ISheet sheet workbook.GetSheetAt(0); IRow headerRow sheet.GetRow(0); int columnCount headerRow.LastCellNum; for (int rowIdx 1; rowIdx sheet.LastRowNum; rowIdx) { IRow row sheet.GetRow(rowIdx); if (row null) continue; var rowDict new Dictionarystring, object(); for (int colIdx 0; colIdx columnCount; colIdx) { var cell row.GetCell(colIdx); string header headerRow.GetCell(colIdx)?.ToString(); rowDict[header] cell?.ToString(); } result.Add(rowDict); } return result; }这里有两个坑要提醒一是不要直接用cell.ToString()Excel中数字单元格的ToString可能会带上科学计数法二是空行判断要小心LastRowNum在用户删除过行后会很大建议实际遍历时先判断row.GetRow(rowIdx)是否为null。实际项目里我还会把每一行的行号记录下来方便错误回传时告诉用户“第3行、第5行有问题”。4. 常见问题与排查技巧实录Excel读写避坑手册这些年来被问得最多、也最典型的Excel读写问题我整理成了一份速查表基本覆盖了热搜词里出现的大部分场景。问题现象根本原因解决方案读出来日期变成数字戳Excel底层存数字靠格式显示日期读取时指定parse_dates或读取后手动转换openpyxl保存后公式不计算Excel文件只存公式不存值用libreoffice或Excel重算一次或用data_onlyFalse读公式双击单元格才能触发公式公式未自动重算写代码时设置wb.calculation.fullCalcOnLoad True筛选的IP地址排序乱文本排序按字符逐位比较将IP拆成四段数字排序或用keylambda x: tuple(map(int, x.split(.)))多个用户同时编辑互相覆盖Excel原生没有并发控制改用在线协作或服务端统一读写read_excel报错xlrd不支持xlsxxlrd 2.0后只支持xls安装openpyxl代码里指定engineopenpyxl写中文乱码编码设置不对写文件时指定编码读文件时注意encodingutf-8合并单元格只显示第一格值Excel特性合并区域只有左上角有值读后用fillna(methodffill)填充除了这些表里的还有一个特别实际的坑要单独拿出来说用户上传的文件可能是.xls、.xlsx、.csv还有可能是假后缀。比如把CSV文件直接改名为.xlsx这时候用openpyxl读取会直接报错。我通常的做法是读取文件的魔数magic number根据文件头判断真实格式而不是相信后缀名。这样能杜绝一大部分“文件损坏打不开”的报错。再分享一个排查技巧遇到“明明数据没问题但结果不对”的情况先怀疑数据类型再怀疑编码最后怀疑行列索引。把读取到的原始数据打印出一行仔细对比往往很快就能发现问题。5. 最后再分享一个经验先写“读写骨架”再填充业务逻辑我做了很多Excel自动化项目之后最大的体感是Excel读写本身的代码量并不大真正耗时的是围绕读写的“上下游”——数据清理、错误反馈、边界处理、兼容性测试。所以我在实际开发中会先写一个“读写骨架”就是一套通用的读取函数、清洗函数、写入函数业务逻辑通过参数注入。这样新需求来的时候我只需要改业务函数不需要重新调读写代码。比如读取函数统一返回带原文行号的DataFrame一旦后续校验失败就能直接定位到具体行这个设计在处理用户上传数据时帮了大忙。另外一个让我省了很多返工的做法是写文件之前先想清楚谁在看这份文件。如果文件是给系统读的保持简单只留数据不要加各种花哨的颜色和合并单元格如果文件是给人看的那么样式该加就加但一定要对所有人通用的查看器比如WPS做兼容测试。别辛辛苦苦做好一个报表结果别人用WPS打开后格式全乱了这才是最让人崩溃的。Excel读写这件事门槛低但上限高。很多人觉得它简单是因为只在一个狭窄的场景里用过真正做到面对任何文件都能高效处理、不出错、不丢数据才算是把这块玩明白了。希望这篇能帮你少走一些弯路至少在遇到Excel读写问题时脑子里已经有一个清晰的排查顺序。本文还有配套的精品资源点击获取
