npoiUse when reading/writing Excel (.xls/.xlsx) or Word (.doc/.docx) files in .NET without Microsoft Office — data import/export, report generation, template filling. NPOI: .NET port of Apache POI for Office file I/O in server environments.
Independent GitHub snapshot · Automated classification · Original documentation governs
name: npoi description: "Use when reading/writing Excel (.xls/.xlsx) or Word (.doc/.docx) files in .NET without Microsoft Office — data import/export, report generation, template filling. NPOI: .NET port of Apache POI for Office file I/O in server environments." tags:
项目地址： https://github.com/nissl-lab/npoi
NuGet：
NPOI最新版本： 2.8.1（截至2026-09，NuGet）
官方文档： https://github.com/nissl-lab/npoi/wiki
许可证： Apache-2.0
注意： 自 NPOI 2.8.0（2026-04）起，产生收入的商业用户需通过 GitHub Sponsors 支付月度维护费用。详见 NPOI GitHub。
NPOI 主要能力：
FormulaEvaluator.doc（HWPF） / Word .docx（XWPF）.ppt/.pptx大文件写入推荐 SXSSF（流式），可处理百万行 Excel；大文件读取推荐 EventModel（XSSF SAX）。
dotnet add package NPOI
主要命名空间：
NPOI.HSSF.UserModel — xlsNPOI.XSSF.UserModel — xlsxNPOI.XSSF.Streaming — 流式 xlsxNPOI.SS.UserModel — 通用接口（IWorkbook / ISheet / IRow / ICell）NPOI.SS.Util — CellRangeAddress、WorkbookUtilusing NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
IWorkbook wb = new XSSFWorkbook();
ISheet sh = wb.CreateSheet("Sheet1");
// 标题样式
ICellStyle headStyle = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold; font.FontHeightInPoints = 12;
headStyle.SetFont(font);
headStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.Alignment = HorizontalAlignment.Center;
string[] head = { "ID", "Name", "Score", "Time" };
IRow row = sh.CreateRow(0);
for (int i = 0; i < head.Length; i++) {
var c = row.CreateCell(i);
c.SetCellValue(head[i]);
c.CellStyle = headStyle;
}
// 数据
for (int r = 1; r <= 10; r++) {
var dr = sh.CreateRow(r);
dr.CreateCell(0).SetCellValue(r);
dr.CreateCell(1).SetCellValue("User" + r);
dr.CreateCell(2).SetCellValue(80 + r);
dr.CreateCell(3).SetCellValue(DateTime.Now); // 注意要设置日期样式
}
// 列宽
sh.AutoSizeColumn(1);
using var fs = new FileStream("out.xlsx", FileMode.Create);
wb.Write(fs, leaveOpen: false);
using var fs = new FileStream("in.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook wb = WorkbookFactory.Create(fs); // 自动识别 xls/xlsx
ISheet sh = wb.GetSheetAt(0);
for (int r = sh.FirstRowNum; r <= sh.LastRowNum; r++) {
IRow row = sh.GetRow(r); if (row == null) continue;
for (int c = row.FirstCellNum; c < row.LastCellNum; c++) {
ICell cell = row.GetCell(c);
Console.Write(GetValue(cell) + "\t");
}
Console.WriteLine();
}
static object GetValue(ICell? cell) => cell?.CellType switch {
CellType.Numeric => DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue : cell.NumericCellValue,
CellType.String => cell.StringCellValue,
CellType.Boolean => cell.BooleanCellValue,
CellType.Formula => cell.CachedFormulaResultType switch {
CellType.Numeric => cell.NumericCellValue,
CellType.String => cell.StringCellValue,
_ => ""
},
_ => ""
};
ICellStyle dateStyle = wb.CreateCellStyle();
dateStyle.DataFormat = wb.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
ICellStyle pctStyle = wb.CreateCellStyle();
pctStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
sh.CreateRow(11).CreateCell(2).SetCellFormula("SUM(C2:C11)");
// 计算公式（写出前）
var ev = wb.GetCreationHelper().CreateFormulaEvaluator();
ev.EvaluateAll();
using NPOI.XSSF.Streaming;
using SXSSFWorkbook wb = new SXSSFWorkbook(rowAccessWindowSize: 1000);
ISheet sh = wb.CreateSheet("big");
for (int r = 0; r < 1_000_000; r++) {
var row = sh.CreateRow(r);
for (int c = 0; c < 10; c++)
row.CreateCell(c).SetCellValue(r * 10 + c);
}
using var fs = File.Create("big.xlsx");
wb.Write(fs);
wb.Dispose(); // 清理临时文件
sh.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3)); // 合并第一行 A:D
sh.CreateFreezePane(0, 1); // 冻结首行
sh.SetAutoFilter(new CellRangeAddress(0, sh.LastRowNum, 0, 3));
byte[] img = File.ReadAllBytes("logo.png");
int picIdx = wb.AddPicture(img, PictureType.PNG);
IDrawing patriarch = sh.CreateDrawingPatriarch();
var anchor = wb.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = 5; anchor.Row1 = 1;
var pic = patriarch.CreatePicture(anchor, picIdx);
pic.Resize();
var cf = (XSSFConditionalFormatting)sh.SheetConditionalFormatting;
var rule = cf.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "60");
var pattern = rule.CreatePatternFormatting();
pattern.FillBackgroundColor = IndexedColors.Yellow.Index;
cf.AddConditionalFormatting(new[] { CellRangeAddress.ValueOf("C2:C11") }, rule);
using NPOI.XWPF.UserModel;
XWPFDocument doc = new();
XWPFParagraph p = doc.CreateParagraph();
XWPFRun r = p.CreateRun();
r.FontSize = 16; r.IsBold = true; r.SetText("Hello NPOI");
using var fs = File.Create("out.docx");
doc.Write(fs);
Dispose() 清理 tmp 文件ICellStyle 实例XSSFEventModel（SAX）以减少内存wb.CreateCellStyle().xls 用 HSSFWorkbook，.xlsx 用 XSSFWorkbook，不确定用 WorkbookFactory.Create() 自动识别FileStream → WorkbookFactory.Create() → GetSheetAt(0) → 遍历 Row/Cell → 根据 CellType 取值new XSSFWorkbook() → CreateSheet() → CreateRow() → CreateCell() → SetCellValue() → FileStream 写出ICellStyle 实例，避免 64000 限制）ICellStyle，否则显示为数字ICellStyle 实例总数不能超过 64000，必须在循环外 CreateCellStyle() 并复用wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll() 在写出前调用Dispose() 清理临时文件| 场景 | 推荐方案 |
|---|---|
| 简单导入导出（< 10 万行） | NPOI XSSFWorkbook |
| 百万级写入 | NPOI SXSSFWorkbook |
| 百万级读取 | NPOI EventModel（SAX） |
| WinForms/WPF 内嵌表格 | ReoGrid（底层用 NPOI 读写文件） |
| 纯数据交换（无格式） | MiniExcel / CsvHelper |
| 问题 | 解决 |
|---|---|
| 中文乱码 | 文件流默认 UTF-8；xls 打开 Excel 自动识别 |
| 日期变数字 | 给 Cell 设置带日期 DataFormat 的 ICellStyle |
| 出现「This Workbook contains an invalid」 | 风格数超限或公式错误，减少样式 / 修正公式 |
| 内存溢出 | 改用 SXSSF / EventModel |
| Office 打开提示损坏 | 写完后 wb.Write(fs) 必须在文件流之前不要 Close 流 |