简介面向C#开发者的NPOI操作Excel示例压缩包覆盖旧版.xls与新版.xlsx两种格式内含2012Version与201607Version两套工具类及对应依赖库适合需要在.NET项目中快速实现Excel创建、读写与样式设置的初中级开发者。包体共15个文件以12个NPOI相关DLL为主辅以2个C#工具类源码和1个XML配置说明整体仅2.19MB轻量易部署。描述中详细梳理了从NuGet安装、命名空间导入、HSSF/XSSFWorkbook创建、Sheet与Row/Cell操作到字体边框样式、异常处理与大数据量性能优化等关键知识点结合两版工具类可对比不同NPOI版本下的实现差异。已有6347人学习下载对入门Excel自动化处理或封装通用操作类均有较高参考价值。 做过上位机和业务系统的人应该都有这种体会今天车间主任要一份带格式的产量报表明天领导说“把库存表整理一下发我”后天客户又提了一句“能不能把检测结果批量填到模板里”。Excel操作几乎成了C#开发绕不开的日常需求。而一说到在.NET环境里读写Excel很多人的第一反应是装Office COM组件结果一到服务器上就各种踩坑权限不够、Office没装、版本冲突而NPOI这几年的表现确实能帮我们把这些问题全部绕开。NPOI是Apache POI项目的.NET版本移植最大的特点就是不需要安装Office直接在内存里操作Workbook同时支持.xls和.xlsx两种格式还能读写公式、样式、合并单元格、图片等复杂元素。这篇文章我就围绕实际场景把NPOI读写Excel的完整思路、核心代码、和我在真实项目里踩过的坑都梳理一遍。1. 为什么选NPOI从Office COM到纯托管库的切换先聊聊选型。很多老项目里还在用Microsoft.Office.Interop.Excel我之前也用过一段时间。本地开发没问题代码写起来也顺手但一部署到服务器就头疼服务器必须装Office、IIS进程池要放开权限、并发一高Excel进程直接卡死甚至出现过excel.exe进程越开越多最后系统内存耗尽的情况。后来项目组统一把Excel操作组件切换成了NPOI这些问题基本就没再出现过。1.1 NPOI的优势和适用边界NPOI的核心操作对象是IWorkbook它有两个具体实现HSSFWorkbook对应Excel 97-2003也就是.xls格式XSSFWorkbook对应Excel 2007及以上也就是.xlsx格式。这两个实现都实现了IWorkbook接口所以大部分业务代码可以基于接口来写只是在创建工作簿的时候指定具体类型。如下图所示这是最典型的工厂模式用法。IWorkbook workbook; if (isXlsx) { workbook new XSSFWorkbook(); } else { workbook new HSSFWorkbook(); }这种设计的好处是业务层不用关心到底是老格式还是新格式读写逻辑完全一致。NPOI能够做到纯托管方式处理Excel不需要服务器安装任何额外的软件这在工业上位机、Web后端部署的环境里是非常关键的优势。毕竟产线工控机上装什么软件是有严格管控的用NPOI以后就没有这个约束。1.2 和EPPlus、ClosedXML的横向对比顺便说一句.NET生态里处理Excel不只是NPOI一家还有EPPlus和ClosedXML。EPPlus在样式处理和图表生成上做得很好但它是商业授权的商用的话要付Licence费用ClosedXML使用起来非常简洁API设计得很现代化不过它的定位是OpenXML封装层依赖.NET Framework或.NET Core的特定版本策略对老项目的兼容性不如NPOI。NPOI是Apache License 2.0完全免费商用而且对于.xls老格式的支持是其他库做不到的。我还遇到过一些老的工业软件导出的数据文件还是.xls格式这种情况用EPPlus是处理不了的只有NPOI和原生OpenXML才能搞定。2. 环境准备与最基础的读写操作NPOI的接入非常简单。在Visual Studio里直接通过NuGet搜索NPOI安装最新稳定版即可。这里有个细节需要注意自NPOI 2.0版本开始整个库被拆分成了多个程序集NPOI、NPOI.OOXML、NPOI.OpenXml4Net、NPOI.OpenXmlFormats等。直接用NuGet安装主包是没问题的但如果你自己引DLL千万别漏掉NPOI.OOXML否则你在用XSSFWorkbook的时候会直接报找不到类的错误。2.1 三行代码生成最简单的Excel先来一个最简单、但是我自己经常作为“起点模板”的写操作。这段代码的思路是创建Workbook → 创建Sheet → 创建Row → 创建Cell → 写文件。using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; using System.IO; public void CreateSimpleExcel(string filePath, bool isXlsx) { IWorkbook workbook isXlsx ? new XSSFWorkbook() : new HSSFWorkbook(); ISheet sheet workbook.CreateSheet(Sheet1); IRow headerRow sheet.CreateRow(0); headerRow.CreateCell(0).SetCellValue(序号); headerRow.CreateCell(1).SetCellValue(产品编号); headerRow.CreateCell(2).SetCellValue(生产日期); using (FileStream fs new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } }很多新手在这儿容易犯一个错误写完之后没有关闭Workbook。NPOI的Workbook本身不持有非托管资源所以在简单场景下不调用workbook.Close()问题不大但如果你是在循环里反复创建Workbook最好还是用using或者手动释放避免内存持续上涨。对于文件流我建议用using写完之后自动Close和Dispose防止文件被锁定。2.2 读取Excel并输出到控制台读取的逻辑和写入是对称的打开文件流 → 通过WorkbookFactory.Create识别格式 → 遍历Sheet和Row → 读取Cell值。这里有一个很实用的方法叫WorkbookFactory.Create它能根据文件流自动判断是.xls还是.xlsx不用我们自己再去判断后缀或者try-catch两种构造方法。public void ReadExcel(string filePath) { using (FileStream fs new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook WorkbookFactory.Create(fs); // 遍历所有Sheet for (int i 0; i workbook.NumberOfSheets; i) { ISheet sheet workbook.GetSheetAt(i); Console.WriteLine($Sheet: {sheet.SheetName}); // 遍历行 for (int row 0; row sheet.LastRowNum; row) { IRow currentRow sheet.GetRow(row); if (currentRow null) continue; for (int col 0; col currentRow.LastCellNum; col) { ICell cell currentRow.GetCell(col); Console.Write(GetCellValue(cell) \t); } Console.WriteLine(); } } } } private string GetCellValue(ICell cell) { if (cell null) return string.Empty; switch (cell.CellType) { case CellType.String: return cell.StringCellValue; case CellType.Numeric: // 这里要处理日期类型 if (DateUtil.IsCellDateFormatted(cell)) return cell.DateCellValue.ToString(yyyy-MM-dd HH:mm:ss); return cell.NumericCellValue.ToString(); case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Formula: return cell.CellFormula; default: return string.Empty; } }读取的时候最大的坑就是单元格类型判断。Excel单元格本身是弱类型的同一列里可能这行是数字下一行是字符串甚至还有公式、空白、布尔值。如果你直接调用StringCellValue而它实际是个数字就会抛异常。所以像GetCellValue这样一个统一的取值辅助函数建议每个项目里都保留一份。尤其要注意Numeric类型的判断因为Excel里的日期本质上就是数字序列号需要用DateUtil.IsCellDateFormatted先判断一下否则日期会被读成一串小数。3. 实战从“能跑”到“好用”的报表导出基础读写学会了实际业务里还有不少进阶需求表头要带颜色、金额要加千分位、某些行要合并、数据多了列宽要自适应。这些才是真正决定报表“能不能交差”的部分。下面这一段是我在上位机项目里最常用到的一套完整报表生成方案直接照着改就能用。3.1 带样式的生产报表生成先理一下需求。假设车间需要每天生成一份《产线日产量统计表》表头包含“序号、产线名称、班次、产量、合格率、备注”表头背景色要深蓝标题行合并居中产量列保留两位小数数据行要有边框。用NPOI实现的核心代码大概是这样的public void CreateProductionReport(string filePath) { IWorkbook workbook new XSSFWorkbook(); ISheet sheet workbook.CreateSheet(产线日报); // 标题样式合并单元格 居中 字号 IRow titleRow sheet.CreateRow(0); titleRow.CreateCell(0).SetCellValue(产线日产量统计表); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5)); ICellStyle titleStyle workbook.CreateCellStyle(); titleStyle.Alignment HorizontalAlignment.Center; titleStyle.VerticalAlignment VerticalAlignment.Center; IFont titleFont workbook.CreateFont(); titleFont.FontHeightInPoints 14; titleFont.Boldweight (short)FontBoldWeight.Bold; titleStyle.SetFont(titleFont); titleRow.GetCell(0).CellStyle titleStyle; titleRow.HeightInPoints 30; // 表头样式背景色 IRow headerRow sheet.CreateRow(1); string[] headers { 序号, 产线名称, 班次, 产量, 合格率, 备注 }; ICellStyle headerStyle workbook.CreateCellStyle(); headerStyle.FillForegroundColor NPOI.HSSF.Util.HSSFColor.BlueGrey.Index; headerStyle.FillPattern FillPattern.SolidForeground; headerStyle.Alignment HorizontalAlignment.Center; for (int i 0; i headers.Length; i) { ICell cell headerRow.CreateCell(i); cell.SetCellValue(headers[i]); cell.CellStyle headerStyle; } // 数据行写入边框 ICellStyle dataStyle workbook.CreateCellStyle(); dataStyle.BorderTop BorderStyle.Thin; dataStyle.BorderBottom BorderStyle.Thin; dataStyle.BorderLeft BorderStyle.Thin; dataStyle.BorderRight BorderStyle.Thin; string[,] data GetProductionData(); // 从数据库或PLC采集来的数据 for (int i 0; i data.GetLength(0); i) { IRow row sheet.CreateRow(i 2); for (int j 0; j data.GetLength(1); j) { ICell cell row.CreateCell(j); cell.SetCellValue(data[i, j]); cell.CellStyle dataStyle; } } // 列宽自适应 for (int i 0; i headers.Length; i) { sheet.AutoSizeColumn(i); } using (FileStream fs new FileStream(filePath, FileMode.Create)) { workbook.Write(fs); } }这段代码里有三个地方值得单独说明。第一是AddMergedRegion它接收的是CellRangeAddress参数含义是起始行、结束行、起始列、结束列四个都是闭区间。合并之后只有左上角的单元格能设置值如果给其他单元格设置了值Excel打开时就会提示文件损坏。我在项目里还真遇到过这种问题后来排查了半天才发现是往合并区域的非左上角单元格写了内容。第二是AutoSizeColumn。这个方法在XSSFWorkbook里工作得很好但在HSSFWorkbook.xls里经常会失效因为HSSF计算列宽需要英文字体度量中文字符串经常算不准。所以在.xls场景下我一般手动设置固定列宽sheet.SetColumnWidth(0, 8 * 256)这里的单位是1/256个字符宽度8就是8个字符的意思。用Sheet.SetColumnWidth设置中文字符宽度的经验值一般是字符数量乘以2再乘以256比如10个汉字就设10 * 2 * 256。第三是FontBoldweight的设置。在新版NPOI里直接设置font.IsBold true更直观Boldweight这种写法是为了兼容老版本。两个方案都能用建议新项目直接IsBold。3.2 批量导入Excel数据入库报表导出是写那从Excel导入数据库就是读。有个很典型的场景用户拿了一张Excel表格里面是几百条物料信息要求导入到MES系统里。用NPOI读取之后最顺的入库方式是把内存中的数据转换成DataTable然后配合SqlBulkCopy一次性写入数据库。在数据量上千行的时候这种方式比一条条拼Insert语句要快一个数量级。第一步是读取整个Sheet到一个DataTablepublic DataTable GetDataTableFromSheet(ISheet sheet) { DataTable dt new DataTable(); IRow headerRow sheet.GetRow(sheet.FirstRowNum); if (headerRow null) return dt; // 用表头行创建列 for (int i 0; i headerRow.LastCellNum; i) { dt.Columns.Add(GetCellValue(headerRow.GetCell(i))); } // 从第二行开始读数据 for (int row sheet.FirstRowNum 1; row sheet.LastRowNum; row) { IRow currentRow sheet.GetRow(row); if (currentRow null) continue; DataRow dr dt.NewRow(); for (int col 0; col currentRow.LastCellNum; col) { dr[col] GetCellValue(currentRow.GetCell(col)); } dt.Rows.Add(dr); } return dt; }拿到DataTable以后直接在SqlBulkCopy的WriteToServer里传进去即可。这个方案对上位机项目来说同样适用比如从Excel导入配方参数、导入工装夹具清单效率都很高。这里有个心得如果Excel里有一列是“物料编码”这种业务唯一键建议在导入前先用分组校验去重否则入库的时候很容易因为主键冲突导致整个事务回滚。可能有人会觉得数据库那边有约束就行但实际用户操作中他给你一张Excel之前根本不会关心数据是否重复我们做导入工具时必须在代码里先兜一层给出清晰的错误行号提示。3.3 模板填充批量生成合格证还有一种高频需求是用模板批量填充数据。比如质量部有一张标准格式的《产品合格证》Excel模板里面商品名称、批次号、检验员、检验日期都是占位符现在要从数据库里查100条记录生成100份合格证文件。不引入第三方报表工具的话NPOI是能直接干这个活的。思路是预先准备好一个模板文件打开它 → 找到要填充的单元格位置 → 写入数据 → 另存为新文件。模板里可以带好公司logo、边框、印章图片等静态元素这样生成的文件格式完全统一。这里的关键点是要知道每个占位符对应的单元格位置。我在实际操作中一般会在模板中用特殊的字符串比如{批次号}、{检验员}作为占位符在程序里遍历整张Sheet只要发现单元格的值和占位符匹配就替换成实际数据。这样模板设计人员不需要懂代码他们只需要在一个约定好颜色的单元格里填上占位符字符串程序就能自动找到并替换。public void FillTemplate(string templatePath, string outputPath, Dictionarystring, string data) { using (FileStream fs new FileStream(templatePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook WorkbookFactory.Create(fs); ISheet sheet workbook.GetSheetAt(0); for (int row 0; row sheet.LastRowNum; row) { IRow currentRow sheet.GetRow(row); if (currentRow null) continue; for (int col 0; col currentRow.LastCellNum; col) { ICell cell currentRow.GetCell(col); string cellValue GetCellValue(cell); if (cellValue.StartsWith({) cellValue.EndsWith(})) { string key cellValue.Trim({, }); if (data.ContainsKey(key)) { cell.SetCellValue(data[key]); } } } } using (FileStream outFs new FileStream(outputPath, FileMode.Create)) { workbook.Write(outFs); } } }4. 常见问题与避坑技巧NPOI用久了会发现坑其实就那么几个但每次踩都很痛。我总结了几个最常见的顺手也把解决方案附上。4.1 使用HSSFWorkbook生成.xls时行数超过65535会报错.xls格式本身最多支持65536行这是格式的硬限制。如果你要导出的数据量大别犹豫直接生成.xlsx。判断标准很简单行数超过5万、或者列数很多的一律用XSSFWorkbook。反过来如果是老系统要求必须.xls那就要分批生成多个文件或者做汇总Sheet。4.2 日期类型显示为数字这个问题非常典型。NPOI把日期当成数字存储如果不做处理读取或者写入的时候看到的是一串像44876.54321这种数字。在读取时要用DateUtil.IsCellDateFormatted(cell)判断在写入时可以用cell.SetCellValue(DateTime.Now)配合单元格样式里的日期格式或者直接cell.SetCellValue(DateTime.Now.ToString(yyyy-MM-dd HH:mm:ss))后者最粗暴也最不容易出错。4.3 数据量太大导致内存溢出NPOI是先把整个Workbook加载到内存所以超大Excel文件比如几十万行确实可能出现内存压力。这种情况下更好的选择是使用SXSSFWorkbook这是POI提供的流式写入版本但NPOI好像没有实现这个类。所以在NPOI体系内做大文件导出我通常按行分批、定期写入FileStream并重建Workbook或者干脆导出为CSV文件。CSV文件用Excel打开毫无压力大多数业务报表场景完全够用。4.4 列数太多时数字会被读取为科学计数法读取Excel身份证号、订单编号这类长数字经常遇到这个问题。处理方案有两种一是在Excel模板里把该列设置成文本格式确保录入时就是文本二是程序里用cell.ToString()或者Decimal.Parse(cell.NumericCellValue.ToString())转换格式化。不过在代码里处理始终是治标不治本最根本的还是从源头控制数据格式从模板和输入规则上避免长数字变成科学计数法。4.5 公式读取不到计算结果如果单元格是公式默认的GetCellValue读到的是CellFormula也就是SUM(A1:A10)这样的表达式而不是计算结果。如果需要结果必须用IFormulaEvaluatorIFormulaEvaluator evaluator workbook.GetCreationHelper().CreateFormulaEvaluator(); ICell formulaCell sheet.GetRow(0).GetCell(2); CellValue result evaluator.Evaluate(formulaCell); double value result.NumericValue;需要注意的是Evaluate只能在已经赋值完整的Workbook上执行也就是说如果你在生成Excel时写入公式想同时把计算结果也写进单元格NPOI默认不会帮你计算你需要手动调用公式求值器然后再把值覆盖写回去。否则Excel打开时会自己算但用程序读取不经过Excel引擎就永远是公式字符串。4.6 服务器上提示拒绝访问这个问题通常是Web应用碰到的最多的。即使不强依赖Office COM文件本身的写入权限还是要有的。确保运行应用的服务账号对临时目录和目标目录有写权限。另外配置了IIS的站点要注意应用程序池的“加载用户配置文件”设置为True否则某些情况下临时文件目录访问会出问题。5. 在项目里更高效的实践模式最后聊一点我个人的组织方式。对于NPOI这种操作我建议在项目里做一层独立的ExcelHelper类把获取Workbook、获取Sheet、读取单元格、设置样式这些基础动作全部封装成公共方法。业务层不应该知道HSSFWorkbook和XSSFWorkbook的区别也不要直接接触FileStream。底层的切换只在Helper内部根据文件后缀判断即可。这样真的能省很多事换库、升级、修Bug都只动一个文件。另外还有一个非常实用的小动作在导入Excel时先把文件二进制流传到本地临时目录再读取。尤其是Web端上传的场景客户端传来的数据流有时候是不可重复读取的直接传给NPOI一旦格式有问题再想重新读一次都不可能。存成临时文件以后出错可以用专门的修复模式再试一次排查问题也方便。从岗位技能角度看NPOI覆盖了至少80%的日常Excel处理需求。你把读写、样式、导入导出、模板填充这四件事做好以后遇到任何和Excel沾边的需求心里基本都有底。如果在项目里还遇到过什么奇葩的NPOI问题欢迎一起讨论也许你踩的坑我也踩过。本文还有配套的精品资源点击获取