简介面向.NET开发人员的OpenXml读写Excel实例代码资料聚焦使用OpenXml SDK直接操作xlsx文件避开传统COM组件依赖适合需要在服务端或自动化报表场景中处理Excel的开发者。文档以PDF形式提供共1个文件约50KB内容紧凑涵盖工作簿、工作表、单元格的读取与写入并包含Import与Export两类核心方法的代码解析。实例演示了工作表数据提取为DataTable、共享字符串处理、数值与布尔类型转换以及通过样式表设置字体、填充、边框等操作可帮助读者快速搭建自己的Excel读写工具。已有565人学习下载对于希望掌握OpenXml基础用法并减少Office环境依赖的开发者这是一份小巧实用的参考资料。1. OpenXml 读写 Excel一份可以直接抄的 xlsx 底层操作代码做信息系统开发的人早晚会遇到一个尴尬场景客户要的报表是 Excel但你不敢在服务器上装 Office更不敢用 COM 组件去调 Excel 进程——卡死、权限、杀毒软件拦截每个坑都能让你加班到半夜。这份源码给的是另一条路用 OpenXml SDK 直接操作 xlsx 文件本身。xlsx 本质上是一组经过压缩的 XML 文件SDK 帮你把解包、解析、回写这些杂事封装好了你要做的只是告诉它「在哪个单元格写什么值」。这套代码适合两类人一类是被 Excel COM 组件折磨过、想换底层方案的 .NET 开发者另一类是正在做数据导入导出功能、需要一个稳定可复现的读写模板的从业者。我拆完这份代码最大的感受是它不算完美但核心思路是对的而且能直接跑通。2. 先把 xlsx 的结构摸清楚OpenXml 读写不是黑匣子是三个包2.1 xlsx 文件里到底装了什么很多人第一次接触 OpenXml 时有个误解以为它是某种加密格式。实际上 xlsx 就是一个 ZIP 压缩包里面装着十几个 XML 文件。用解压工具打开任意一个 xlsx你会看到xl/workbook.xml、xl/worksheets/sheet1.xml、xl/sharedStrings.xml这些文件。整个结构分三层最外层是SpreadsheetDocument代表整个 Excel 文件往下一层是WorkbookPart对应工作簿再往下一层是WorksheetPart对应每个工作表。这份代码里的导入导出逻辑就是围绕这三层结构展开的。SpreadsheetReader.Create()创建一个内存中的 xlsx 文件流SpreadsheetDocument.Open(stream, true)以可写方式打开展开文档然后通过WorkbookPart找到工作表用WorksheetPart读写具体单元格。理解这个三层关系你才能明白为什么代码里取一个单元格值要绕那么多层先拿Cell再判断它的DataType是不是SharedString是的话还要去SharedStringTablePart里反查真实值。这种设计不是没事找事而是 xlsx 格式的一个关键优化如果 1000 行数据里都在写待审核这三个字Excel 不会重复存储 1000 份字符串而是只在单元格里存一个数字索引真正的字符串统一放在sharedStrings.xml里。好处是文件体积大幅减小坏处是你读数据时必须多做一步索引转真实值的操作。2.2 代码里用了哪些关键 API这份代码依赖三个库DocumentFormat.OpenXml是官方 SDK 的核心程序集负责处理文档结构和 XML 序列化DocumentFormat.OpenXml.Extensions和DocumentFormat.OpenXml.Spreadsheet提供了一组辅助类比如这里的SpreadsheetReader、SpreadsheetWriter、WorksheetWriter——注意这几个类型并不是官方 SDK 自带的而是代码作者基于 SDK 做的二次封装。从结果反推SpreadsheetReader.Create()应该在内存流里创建了一个标准的工作簿模板自带三个默认的空工作表所以导出前才有RemoveWorksheet(doc, Sheet1)、Sheet2、Sheet3这三行。WorksheetWriter.PasteText(location, text, style)则是把字符串直接写进指定坐标。这套封装的好处是调用方代码极其简洁DataTable table new DataTable(1); table.Columns.Add(2); for (int i 0; i 10; i) { DataRow row table.NewRow(); row[0] i; table.Rows.Add(row); } ListDataTable list new ListDataTable(); list.Add(table); OpenXmlSDKExporter.Export(AppDomain.CurrentDomain.BaseDirectory \\excel.xlsx, list);逻辑说明这段是调用方的示例代码DataTable的表名会直接变成工作表的名称列名为2循环写入 10 行整数。注意这里row[0] i存的是 int 类型但导出的Export方法里统一调用了ToString()所以最终 Excel 单元格里存的是文本格式的数字不是数值格式。这是这套代码的一个特性后面讲坑的时候会展开说。参数说明Export方法的第一个参数是输出文件的物理路径第二个参数是ListDataTable允许一次导出多个工作表。如果你的业务需要把一个 DataSet 里的多张表分别导出到不同 Sheet直接往这个 List 里加就行比用 COM 的方式省太多事。导入方向的调用同样简单ListDataTable tables OpenXmlSDKExporter.Import(path);返回的是一个ListDataTable每个DataTable对应一个工作表表名取自 xlsx 里的 Sheet 名。整份代码的导入导出核心逻辑加起来不到 200 行但覆盖了最常用的读写场景。3. 导出方向从 DataTable 到 xlsx 的完整流程拆解3.1 导出流程分成几步Export方法的执行路径可以拆成四步创建空白工作簿、删除默认工作表、逐表写入数据、保存到物理文件。核心代码在这里using (MemoryStream stream SpreadsheetReader.Create()) { using (SpreadsheetDocument doc SpreadsheetDocument.Open(stream, true)) { SpreadsheetWriter.RemoveWorksheet(doc, Sheet1); SpreadsheetWriter.RemoveWorksheet(doc, Sheet2); SpreadsheetWriter.RemoveWorksheet(doc, Sheet3); foreach (DataTable table in tables) { WorksheetPart sheet SpreadsheetWriter.InsertWorksheet(doc, table.TableName); WorksheetWriter writer new WorksheetWriter(doc, sheet); SpreadsheetStyle style SpreadsheetStyle.GetDefault(doc); foreach (DataRow row in table.Rows) { for (int i 0; i table.Columns.Count; i) { string columnName SpreadsheetReader.GetColumnName(A, i); string location columnName (table.Rows.IndexOf(row) 1); writer.PasteText(location, row[i].ToString(), style); } } writer.Save(); } SpreadsheetWriter.StreamToFile(path, stream); } }逻辑说明内存流在using块内创建保证读写过程中不触碰磁盘SpreadsheetDocument.Open(stream, true)的第二个参数true表示以可编辑方式打开这样才能后续插入工作表。删除三个默认 Sheet 是必要的否则生成的文件里会带着无用的空表。InsertWorksheet每次调用会生成一个新的 WorksheetPart表名直接用table.TableName这要求调用方建 DataTable 时就给表取好名字。参数说明SpreadsheetReader.GetColumnName(A, i)做了两件事——把起始列A转成数字索引再基于这个索引做偏移。i0时返回Ai1时返回B到i25时返回Zi26时返回AA。这实际上是 26 进制转换的前置封装。location columnName (table.Rows.IndexOf(row) 1)中行号从 1 开始是 Excel 的 A1 引用风格。3.2 写入效率逐格写入和整表写入的差距这份代码用的PasteText是逐格写入也就是每个单元格单独调用一次写入方法。数据量小几百行时完全没问题但如果你要导出几万行速度会明显变慢。原因很简单每调用一次PasteTextSDK 都要在 XML 树里找到对应位置并插入节点这个开销是累积的。我会建议在数据量超过 5000 行时改用 OpenXml SDK 的批量写入方式先构造一个SheetData对象把Row和Cell全部组织好一次性挂到 Worksheet 上。SDK 还提供了OpenXmlWriter可以像流水线一样逐行写 XML 节点这个方案的内存占用更低、速度更快。原代码适合做学习和中小数据量场景生产环境大表导出需要另做优化。这里还要注意PasteText写入的格式问题代码里所有值都经过了ToString()所以日期、数字、布尔值在 Excel 里全是文本。如果你需要数字格式比如求和公式能直接识别得用PasteText的重载或配套的样式接口把单元格类型设成数值。这是这套代码最容易被忽略的一个隐含行为。4. 导入方向从 xlsx 到 DataTable 的解析与 SharedString 处理4.1 导入流程遍历行和单元格靠 CellReference 定位列名Import方法做的事情是把 xlsx 的每个工作表读成一个 DataTable表名是 Sheet 名列名是 Excel 的字母列号A、B、C…。它的处理思路是先扫一遍所有行收集出现过的列字母并排序再重新遍历逐行取值。这个设计能保证列的顺序和 Excel 中一致即使某些行的单元格是空的、没有出现在 XML 节点里也不会漏列。关键代码是这一段foreach (Cell cell in row) { string columnName Regex.Match(cell.CellReference.Value, [a-zA-Z]).Value; if (!columnsNames.Contains(columnName)) { columnsNames.Add(columnName); } }逻辑说明cell.CellReference是像 B3 这样的引用字符串正则[a-zA-Z]匹配出其中的字母部分作为列名。这段代码巧妙的地方在于不依赖单元格在 Row 元素里的顺序而是直接用坐标定位。但要提醒的是调用了Regex.Match每格一次大数据量时正则开销不可忽略。我在实际项目里会用IndexOf加循环去手动截取字母部分来替代正则大概能省 30% 的解析时间。4.2 SharedString 的读取与反查xlsx 的字符串存储在共享字符串表里单元格里存的只是一个整数索引。GetValue就是处理这个映射的核心函数public static String GetValue(Cell cell, SharedStringTablePart stringTablePart) { if (cell.ChildElements.Count 0) return null; String value cell.CellValue.InnerText; if ((cell.DataType ! null) (cell.DataType CellValues.SharedString)) value stringTablePart.SharedStringTable .ChildElements[Int32.Parse(value)] .InnerText; return value; }逻辑说明先判断单元格有没有子元素没有说明是空单元格直接返回 null。cell.DataType为SharedString时CellValue.InnerText是共享字符串表的索引数字通过Int32.Parse转成整数再定位到SharedStringTable的对应子元素取真实文本。如果单元格没有设置DataType那CellValue.InnerText就是字面值直接返回。这里有个坑有些 Excel 工具生成的 xlsx 里没有显式声明DataType的单元格可能存的是内联字符串is节点而不是共享字符串。这份代码没处理InlineString的情况遇到这种文件时取值会出错。我在下面的避坑章节会详细说。4.3 两个读取路径的取舍DOM 解析还是 SAX 解析原代码用的是 DOM 方式对每个工作表先DescendantsRow()拿所有行再遍历单元格。这种方式实现简单、代码可读性强但数据量大时内存占用飙升——整个工作表结构全部加载到内存里。下面表格是两种方式的对比维度DOM 方式本代码SAX 流式方式内存占用整个工作表加载到内存逐行读入占用固定性能10 万行以上明显卡顿10 万行可流畅处理实现复杂度简单直接查对象树需要手动管理状态机适用场景单表 1 万行以内大文件导入、内存受限环境SDK 自带的OpenXmlReader就是流式读取 API配合共享字符串表的缓存机制可以做到边读边丢。我建议生产环境导入超过 1 万行的文件时直接用OpenXmlReader改造。原代码作者把列号排序放在第二次遍历前列的做法值得保留第一遍扫所有单元格收集列名并排序第二遍取数据时就能按列名直接定位避免空单元格导致列错位。5. 列号转换的核心细节26 进制算法与字母列的边界坑5.1 为什么不能用 char 直接转导入导出都要用到列号转换比如 A 转成 1Z 转成 26AA 转成 27。新手第一反应是把 A 减 65 加 1但遇到 AA、AB 这类超过一位的列号就会翻车原因很简单Excel 列号是 26 进制A 到 Z 是第一轮AA 到 AZ 是第二轮BA 到 BZ 第三轮。这个进制没有 0 这个数字所以直接用(char - A 1)只能处理单个字母。这份代码用了一个更复杂的实现递归拆分每一位逐位乘以 26 的幂次累加。Letter_to_num方法还特别处理了最高位加一的逻辑这是 26 进制无零进制最容易出错的地方。private static int Letter_to_num(string str) { char[] letter str.ToCharArray(); int reNum 0; int power 1; int times 1; int num letter.Length; reNum Char_num(letter[num - 1]); if (num 2) { for (int i num - 1; i 0; i--) { power 1; for (int j 0; j i; j) { power * 26; } reNum (power * (Char_num(letter[num - i - 1]) times)); times 0; } } return reNum; }逻辑说明Char_num(A)返回 0、Char_num(B)返回 1这和一些人的直觉相反——不是 1 到 26而是 0 到 25。于是 A 的返回值是 0但实际 Excel 列号里 A 是第一列所以最末位直接用返回值不对。代码里做了一个修正末尾位的值直接加Char_num(letter[num-1])如果末尾是 A 则加了 0等于缺了 1但前面高位计算时统一加了times第一次是 1补齐了这个偏差。这就是 26 进制无零进制最核心的修正点。参数说明这段代码的时间和空间复杂度其实不够漂亮用两层循环算幂。但好处是不依赖Math.Pow纯整数运算没有浮点误差。如果要替换实现最简洁的写法是result result * 26 (c - A 1)从高位往低位遍历一次就能完成建议直接改用这个。5.2 字母列转数字的验证方法自己实现完这个转换最好写个验证函数确认边界值都对。下面是最容易出错的几组映射字母正确数字常见错误写法得到的结果A10Z260如果按取模写AA270AB281AZ5225BA5326你如果自己写转换函数验证这几组值就够了。还有一个更省力的办法用SpreadsheetReader.GetColumnName(A, i)反推把i0到i100的列名先打印出来再拿这些列名去喂给Letter_to_num看算出来的值是否为i1。双向验证两边一起查能发现很多隐蔽问题。5.3 这份代码里的一个小 bugLevel 数组第 9 个元素代码里Level数组的值是{A,B,C,D,E,F,G,H,I,G,K,L,...}注意第 9 个是 G 而不是 J。这导致Num_to_letter在转到 I 之后会输出 G后面 K、L 正常但序列整体错了一位。如果只在导入方向用Letter_to_num这个 bug 不影响但如果你想用Num_to_letter生成列名到第 9 列就翻车。建议第一件事就是把 G 改成 J。这段代码我猜测是作者手敲数组时笔误让我想起以前自己写的列号数组也栽在这种低级错误上复制源码时这类隐藏错误最坑人。6. 避坑指南OpenXml 读写 Excel 最常见的五个翻车现场6.1 打开文件报 文件损坏 或 内容错误现象代码生成或读取的 xlsx 用 Excel 打开提示文件损坏或提示Excel 发现无法读取的内容。原因最常见的是内存流保存不完整或者写入 XML 结构不合法。原代码用MemoryStream在内存里组装文件如果写完没正确 flush 就StreamToFile文件会被截断。另一个高频原因是用DocumentFormat.OpenXml.Extensions这类非官方封装库时某些版本生成的 XML 命名空间不兼容。解决分两步排查。第一步把导出的 xlsx 用解压工具打开检查[Content_Types].xml是否存在且完整第二步检查工作表 XML 里的根节点声明缺少命名空间前缀x时 Excel 会拒绝打开。我习惯在StreamToFile之前主动doc.Save()一次确保所有变更都持久化到流里再写文件。6.2 读取时取到的字符串全是乱码或索引数字现象导入后表格里出现的不是张三这种真实文本而是 0、1、2 这类数字。原因没有走 SharedString 反查逻辑。原代码的GetValue判断cell.DataType CellValues.SharedString才去反查但某些 xlsx 生成本身不规范明明存的是共享字符串索引却没有声明DataType反过来也有单元格声明了DataTypeSharedString但CellValue节点根本不存在代码会抛 null 引用异常。解决把GetValue的逻辑改成双保险——先判断CellValue是否为空再判断DataType是否声明如果CellValue存在但DataType为空把文本内容尝试转成整数能转成功就查 SharedStringTable转不成功才当成字面值返回。这套兼容逻辑几乎能覆盖市面上 99% 的 xlsx 文件。6.3 内存流用完未释放导致文件被占用无法覆盖现象运行第二次导出时提示 文件正在被另一进程使用或者导出后想删除临时文件删除不掉。原因MemoryStream在using块内会被释放但SpreadsheetWriter.StreamToFile(path, stream)这个方法有可能内部使用了静态缓存或者没有及时释放对流的引用。另外如果调用方在Export方法外还保留了DataTable的引用内存不会立即回收但文件句柄不会因此被占——大概率是StreamToFile后没把文件流 Dispose。解决在StreamToFile之后主动调用stream.Dispose()并且用GC.Collect()兜底回收。更稳妥的做法是在StreamToFile内部用FileStream配合FileMode.Create写完就Flush加Close。从那以后我每次做 Excel 导入导出都会在测试脚本里强制走一遍生成-读取-删除全流程确认文件句柄完全释放才收工。6.4 数字写入后变成了文本SUM 公式求值为 0现象代码导出的数据在 Excel 里看起来是数字但用 SUM 求和结果是 0单元格左上角有绿色三角提示此单元格中的数字为文本形式。原因原代码的PasteText把所有值ToString()后写入XML 节点里没有声明单元格类型为数字Excel 默认当作文本处理。文本数字不参与运算SUM 会直接忽略。解决如果你需要真正的数字格式得在写入时设置单元格的DataType为CellValues.Number或者用 SDK 提供的CellValue构造器直接塞数值。注意不要把DataType和样式混为一谈DataType决定值的解析方式样式决定显示格式两者要分开设置。如果你想保留原代码的简洁性也可以在 Excel 里用分列功能把文本数字批量转成数值但这不是自动化方案。6.5 剪贴板相关故障Excel 里 CtrlV 粘贴数据时失效或卡死现象用 OpenXml 导出的 xlsx 打开后向工作表粘贴数据时粘贴功能无响应或粘贴后格式错乱。原因这种情况通常不是代码直接造成的而是 xlsx 文件本身带有异常样式定义或损坏的列宽设置导致 Excel 在响应剪贴板操作时解析异常。解决先排查文件本身是否能用 Excel 正常保存一次如果能说明原文件没问题再检查代码里是否写了不规范的样式定义比如设置了不存在字体或边框索引。我从实际经验来看大多数 CtrlV 失效的 xlsx 都是样式表styles.xml里引用了未定义的填充或字体 id处理办法是生成文件时限制只用SpreadsheetStyle.GetDefault(doc)不额外添加自定义样式。6.6 空行表读出来为空结果被跳过业务无法感知现象某个 Sheet 里只有表头没有数据导入后这个表消失不见了。原因原代码的Import方法末尾有两个判断——if (table.Rows.Count 0) continue;和if (table.Columns.Count 0) continue;空表会被直接跳过。这在有些场景下是合理的避免输出空表但业务上需要知道这个 Sheet 存在但没数据时就会丢掉信息。解决给Import方法加一个参数控制是否返回空表或者用返回值里附带 Sheet 名称列表的方式让调用方区分文件里根本没有这个表和表存在但无数据。我在做数据校验模块时就吃过这个亏——上游漏配了 Sheet导入程序静默跳过结果下游收到一个少了一张表的 DataSet排查了一整天才发现是空表被过滤掉了。7. 进阶玩法把这套代码改造成并发安全的通用工具类7.1 加一层并发锁多线程导出互不干扰原代码直接操作MemoryStream如果多个线程同时调用Export方法写不同的文件可能出现流冲突或者路径覆盖。最简单的改造方案是加一个静态锁对象private static readonly object exportLock new object(); public static void ExportSafe(string path, ListDataTable tables) { lock (exportLock) { Export(path, tables); } }逻辑说明lock保证同一时刻只有一个导出任务在执行适合并发量不高的内部系统。但要注意如果有长时间导出任务排队其他线程会阻塞等待——所以这只适合中小数据量的场景。如果你要真正的并行导出更稳妥的做法是不用共享的MemoryStream每次调用都new一个新的流配合Guid生成临时文件名写完再改名。7.2 用缓存字典优化 SharedString 反查速度原代码的GetValue每次通过Int32.Parse转索引再去ChildElements[索引]取字符串这个索引操作本身有开销。数据量大时同一个字符串可能被查几千次。改造方向是构造一个字典缓存private Dictionaryint, string sharedStringCache; private string GetCachedValue(int index) { if (sharedStringCache null) { sharedStringCache new Dictionaryint, string(); var items stringTablePart.SharedStringTable.ChildElements; for (int i 0; i items.Count; i) { sharedStringCache[i] items[i].InnerText; } } return sharedStringCache[index]; }逻辑说明一次性把共享字符串表加载到字典里后续取值直接 O(1) 命中避免反复解析 XML 子节点。如果读 10 万行数据这个优化能把导入时间从十秒级降到秒级前提是共享字符串表本身不要太大通常不会超过几千条。7.3 生成文件的合规性验证清单代码跑通只是第一步交付前建议按下面这个清单过一遍第一用 Excel 打开导出的文件确认没有修复弹窗。第二用解压工具检查[Content_Types].xml、workbook.xml、sharedStrings.xml是否存在且内容完整。第三抽查几个单元格的CellReference是不是符合 A1 引用风格防止列错位。第四有公式的单元格在 Excel 里手动按 F9 重算一次确认公式引用的单元格范围没错。第五用 WPS Office 再打开一次WPS 对不规范 XML 的容错比 Excel 差经常能暴露隐蔽问题。这套验证流程我每次发布新版本前都会强制走一遍。OpenXml 开发有个特点大多数错误不会在生成时报出来而是等文件被打开时才暴露相当于一个延迟引爆的雷。多做一次验证少加一次班。希望这份拆解对你有用也建议你把这份代码当作起点逐步替换掉系统里的 COM 依赖——从长远看走 OpenXml 这条路更稳也更可控。本文还有配套的精品资源点击获取