2 什么是 NPOI

NPOI 是指构建在 POI 3.x 版本之上的一个程序，NPOI 可以在没有安装 Office 的情况下对 Word 或 Excel 文档进行读写操作。

NPOI 是一个开源的 C# 读写 Excel、WORD 等微软 OLE2 组件文档的项目。

3 为什么使用 NPOI

比较常用的操作 Excel 的方法如下所示：

1 OLEDB

这种方式是把整个 Excel 文件当做一个数据源来进行数据的读取操作。

优点：实现方式简单，读取速度快。

缺点：读取 Excel 数据的过程不太灵活，对内存的占用比较高，当数据量变的很大时，容易由于内存空间不足导致内存溢出异常。

2 COM 组件

这种方式是通过 COM 组件 Microsoft.Office.Interop.Excel.dll 实现 Excel 文件的操作。

优点：读取 Excel 数据非常灵活，可以实现 Excel 具有的各种数据处理功能。

缺点：对数据的访问时基于单元格方式实现的，所以读写数据较慢，特别是当数据量较大时，访问效率问题更为突出。另一点是要求本机安装了 Office 组件。

3 NPOI

这种方式是通过 NPOI 库实现 Excel 文件操作，可以在没有安装微软 Office 的情况下使用。详情可查看官网。

优点：读取Excel数据速度较快，操作方式灵活。

缺点：官方文档较少。

4 ASPOSE

Aspose 公司旗下的最全的一套 office 文档管理方案。支持 Word, Excel, PowerPoint, Project 等 office 文档以及 PDF 文档读写操作。

优点：因商业驱动所以会有详细的使用文档和技术支持。

缺点：需要收费使用。

这里推荐使用 NPOI，因为 NPOI 不用额外安装 Office 组件，而且完全免费。接下来介绍如何使用 NPOI 。

4 如何使用 NPOI

4.1 安装

使用 nuget 方式安装，命令如下：

Install-Package NPOI

其他命令参考 NuGet Gallery | NPOI 。

4.2 名词解释

4.3 代码示例

下面列举常用使用场景，更多的代码示例可以参考 官方源码 exmaples 目录。

示例功能列表如下：

1. 创建工作薄和保存文件

2. 添加自定义属性

3. 创建工作表、行和列

4. 文件保护

5. 给单元格添加链接

6. 设置单元格字体

7. 设置单元格边框样式

8. 设置单元格数据格式

9. 设置列宽和行高

10. 合并单元格

11. 循环赋值

12. 下载文件

13. 冻结与分隔

14. 使用简单公式

15. 插入图片

4.3.1 创建工作薄和保存文件

// 创建工作薄IWorkbook wb = new XSSFWorkbook();// 保存文件FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();

约定：下文代码不再重新创建变量 wb，默认引用此处声明，除非特殊声明。

4.3.2 添加自定义属性

POIXMLProperties props = wb.GetProperties();
props.CoreProperties.Creator = "NPOI 2.0.5";
props.CoreProperties.Created = DateTime.Now;if (!props.CustomProperties.Contains("NPOI Team"))
{
    props.CustomProperties.AddProperty("NPOI Team", "Hello World!");
}

4.3.3 创建工作表、行和列

// 创建工作表ISheet sheet = wb.CreateSheet("Hyperlinks");// 创建第一行IRow row = sheet.CreateRow(0);// 创建第一列ICell cell = row.CreateCell(0);

注意：工作表的行和列在 NPOI 中都是以 0 开始计数。

约定：下文代码不不再重新创建变量 sheet 和 cell，默认引用此处声明，除非特殊声明。

4.3.4 文件保护

sheet.LockFormatRows();sheet.LockFormatCells();sheet.LockFormatColumns();sheet.LockDeleteColumns();sheet.LockDeleteRows();sheet.LockInsertHyperlinks();sheet.LockInsertColumns();sheet.LockInsertRows();sheet.ProtectSheet("password");

4.3.5 给单元格添加链接

ISheet sheet = wb.CreateSheet("Hyperlinks");// URLICell cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = "http://poi.apache.org/";
cell.Hyperlink = link;
cell.CellStyle = hlink_style;

除此之外，HyperlinkType 枚举类型还可以是： HyperlinkType.File（文件路径）、HyperlinkType.Email（电子邮件地址）、HyperlinkType.Document（内部文档跳转）。

4.3.6 设置单元格字体

// 创建字体样式：underlined, italic, red color, fontsize=20IFont font = workbook.CreateFont();
font.Color = IndexedColors.Red.Index;
font.IsItalic = true;
font.Underline = FontUnderlineType.Double;
font.FontHeightInPoints = 20;// 绑定字体样式到样式对象上ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font);// 绑定样式对象到单元格上cell1.CellStyle = style1;

4.3.7 设置单元格边框样式

// 创建样式ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BottomBorderColor = IndexedColors.Black.Index;
style.BorderLeft = BorderStyle.DashDotDot;
style.LeftBorderColor = IndexedColors.Green.Index;
style.BorderRight = BorderStyle.Hair;
style.RightBorderColor = IndexedColors.Blue.Index;
style.BorderTop = BorderStyle.MediumDashed;
style.TopBorderColor = IndexedColors.Orange.Index;// 设置边框对角线样式style.BorderDiagonalLineStyle = BorderStyle.Medium; // BorderDiagonalLineStyle 属性必须在  BorderDiagonal 和 BorderDiagonalColor 之前设置style.BorderDiagonal = BorderDiagonal.Forward;
style.BorderDiagonalColor = IndexedColors.Gold.Index;// 绑定样式cell.CellStyle = style;

4.3.8 设置单元格数据格式

IDataFormat format = wb.CreateDataFormat();

ICell cell = sheet.CreateRow(0).CreateCell(0);// 小数保留两位 - "1.20"cell.SetCellValue(1.2);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cell.CellStyle = cellStyle;// 带逗号分隔的人民币格式 - "¥20,000"ICell cell2 = sheet.CreateRow(1).CreateCell(0);
cell2.SetCellValue(20000);
ICellStyle cellStyle2 = wb.CreateCellStyle();
cellStyle2.DataFormat =  format.GetFormat("¥#,##0");
cell2.CellStyle = cellStyle2;// 科学计数法格式 - "3.15E+00"ICell cell3 = sheet.CreateRow(2).CreateCell(0);
cell3.SetCellValue(3.151234);
ICellStyle cellStyle3 = wb.CreateCellStyle();
cellStyle3.DataFormat =  format.GetFormat("0.00E+00");
cell3.CellStyle = cellStyle3;// 百分比格式，小数点后2位数 - "99.33%"ICell cell4 = sheet.CreateRow(3).CreateCell(0);
cell4.SetCellValue(0.99333);
ICellStyle cellStyle4 = wb.CreateCellStyle();
cellStyle4.DataFormat =  format.GetFormat("0.00%");
cell4.CellStyle = cellStyle4;// 电话号码格式 - "021-65881234"ICell cell5 = sheet.CreateRow(4).CreateCell(0);
cell5.SetCellValue(02165881234);
ICellStyle cellStyle5 = wb.CreateCellStyle();
cellStyle5.DataFormat =  format.GetFormat("000-00000000");
cell5.CellStyle = cellStyle5;// 中文大写字符数 - 壹贰叁 元
ICell cell6 = sheet.CreateRow(5).CreateCell(0);
cell6.SetCellValue(02165881234);
ICellStyle cellStyle6 = wb.CreateCellStyle();
cellStyle6.DataFormat =  format.GetFormat("[DbNum2][$-804]0 元");
cell6.CellStyle = cellStyle6;// 中文日期格式
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
cell7.SetCellValue(02165881234);
ICellStyle cellStyle7 = wb.CreateCellStyle();
cellStyle7.DataFormat =  format.GetFormat("[DbNum2][$-804]0 元");
cell7.CellStyle = cellStyle7;// 具有日期时间样式的公式值 
ICell cell8 = sheet.CreateRow(7).CreateCell(0);
cell8.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
ICellStyle cellStyle8 = workbook.CreateCellStyle();
cellStyle8.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
cell8.CellStyle = cellStyle8;// 显示当前时间
ICell cell9 = sheet.CreateRow(8).CreateCell(0);
cell9.SetCellValue(02165881234);
ICellStyle cellStyle9 = DateTime.Now;
cellStyle9.DataFormat = format.GetFormat("[$-409]h:mm:ss AM/PM;@");
cell9.CellStyle = cellStyle9;

4.3.9 设置列宽和行高

// 设置列宽，第一个参数为第几列（从 0 开始计数），第二个参数为宽度值，注意值为 256 的倍数sheet.SetColumnWidth(0, 50 * 256);
sheet.SetColumnWidth(1, 100 * 256);
sheet.SetColumnWidth(2, 150 * 256);// 设置行高，注意值为 20 的倍数sheet.CreateRow(0).Height = 100 * 20;
sheet.CreateRow(1).Height = 200 * 20;
sheet.CreateRow(2).Height = 300 * 20;

4.3.10 合并单元格

IRow row = sheet.CreateRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue(new XSSFRichTextString("This is a test of merging"));// 参数格式：new CellRangeAddress(起始第几行，结束第几行，起始第几列，结束第几列)// 合并 A1 和 B1 两个单元格sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));

4.3.11 循环赋值

sheet.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");int x = 1;// 循环填充 15*15 矩阵内的数据for (int i = 1; i <= 15; i++)
{
    IRow row = sheet.CreateRow(i);    for (int j = 0; j < 15; j++)
    {
        row.CreateCell(j).SetCellValue(x++);
    }
}

4.3.12 下载文件

// 清理脏数据Response.Clear();// 设置响应头Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";string filename = "test.xlsx";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));// 创建工作簿和工作表XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");// 填充数据sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");// 写入响应报文中using (var f = File.Create(@"c:\test.xlsx"))
{
    workbook.Write(f);
}// workbook.Write(Response.OutputStream); 不能使用 // 根本原因：Response.OutputStream 将不必要的字节插入响应字节。// 参考链接：http://social.msdn.microsoft.com/Forums/en-US/3a7bdd79-f926-4a5e-bcb0-ef81b6c09dcf/responseoutputstreamwrite-writes-all-but-insetrs-a-char-every-64k?forum=nclResponse.WriteFile(@"c:\test.xlsx");// 刷新缓存和终止报文写入Response.Flush();
Response.End();

4.3.13 冻结与分隔

ISheet sheet1 = workbook.CreateSheet("new sheet");
ISheet sheet2 = workbook.CreateSheet("second sheet");
ISheet sheet3 = workbook.CreateSheet("third sheet");
ISheet sheet4 = workbook.CreateSheet("fourth sheet");// CreateFreezePane 方法参数说明：// 第一个参数表示要冻结的列数，从 1 开始计数，如果不需要冻结设为 0。// 第二个参数表示要冻结的行数，从 1 开始计数，如果不需要冻结设为 0。// 第三个参数表示右边区域可见的首列序号，从 1 开始计数，如果不需要设置则设为 0。// 第四个参数表示下边区域可见的首行序号，从 1 开始计数，如果不需要设置则设为 0。// 冻结第一行sheet1.CreateFreezePane(0, 1, 0, 1);// 冻结第一列sheet2.CreateFreezePane(1, 0, 1, 0);// 冻结列和行（忽略右下象限的滚动位置）sheet3.CreateFreezePane(2, 2);// 创建一个左下角为活动象限的分割sheet4.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);

4.3.14 使用简单公式

ISheet s1=workbook.CreateSheet("Sheet1");// 设置 A2s1.CreateRow(1).CreateCell(0).SetCellValue(-5);// 设置 B2s1.GetRow(1).CreateCell(1).SetCellValue(1111);// 设置 C2s1.GetRow(1).CreateCell(2).SetCellValue(7.623);// 设置 A3s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);// 设置 A4=A2+A3s1.CreateRow(3).CreateCell(0).CellFormula = "A2+A3";// 设置 D2=SUM(A2:C2);s1.GetRow(1).CreateCell(3).CellFormula = "SUM(A2:C2)";// 设置 A5=cos(5)+sin(10)s1.CreateRow(4).CreateCell(0).CellFormula="cos(5)+sin(10)";

ISheet s2 = workbook.CreateSheet("Sheet2");// 使用跨工作表引用s2.CreateRow(0).CreateCell(0).CellFormula = "Sheet1!A2+Sheet1!A3";

4.3.15 插入图片

// 创建绘图工具对象IDrawing patriarch = sheet.CreateDrawingPatriarch();// 创建锚点// XSSFClientAnchor 参数说明：// XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)// dx1：第一个单元格中的x坐标// dy1：第一个单元格中的y坐标// dx2：第二个单元格中的x坐标// dy2：第二个单元格中的y坐标// col1: 第一个单元格的列（从 0 开始计数）。// row1: 第一个单元格的行（从 0 开始计数）。// col1: 第一个单元格的列（从 0 开始计数，个人实践需加 1）。// row2: 第二个单元格的行（从 0 开始计数，个人实践需加 1）。XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);// 设置锚点类型，这里 MoveDontResize 表明移动工作表行和列时，图片不会自动伸缩。anchor.AnchorType = AnchorType.MoveDontResize;// 添加图片资源并获得图片在当前工作簿中唯一序列号FileStream file = new FileStream(@"d:\your_image.jpg", FileMode.Open, FileAccess.Read);byte[] buffer = new byte[file.Length];
file.Read(buffer, 0, (int)file.Length);int imageId = wb.AddPicture(buffer, PictureType.JPEG);// 绘制图片XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);// 调用 Reset 将会重新设置图片尺寸为原图片尺寸，这里会覆盖 调用 XSSFClientAnchor 时的设置。//picture.Resize();// 设置图片边框样式picture.LineStyle = LineStyle.DashDotGel;

5 总结

操作 Excel 是大多数系统常用功能之一，所以熟练掌握它将会很大程度上提升你的效率。我发现 NPOI 官方文档不是很详尽，好在源码有例子比较全面，但是依然不方便快速查询，所以此文把一些常用操作总结和列举出来作为备忘，希望能对你有帮助！其实还有个另辟蹊径的方法学习，那就是查看 POI 文档，里面有很多概念和类名大多数和 NPOI 相同或相似，因为 NPOI 就是从 POI 改版而来，NPOI 是 POI 的 .NET 版本。

作者：银冰雪千载
链接：https://www.jianshu.com/p/d352a7f09228