我和NPOI那些事儿

写在前面

　　本文主要通过本人在实际工作中使用过的经验进行整理，主要针对Excel。

　　全部手打，例子全部做过测试，如果平常做项目用到这个组件本文应该可以解决大部分问题吧~

本文大纲

NPOI概述
NPOI常用操作对象及方法
NPOI常用用法及示例
NPOI问题收集
参考资料
其他开源组件记录

一、NPOI概述

　　1、什么是NPOI？

　　　　NPOI是一个.NET版本的开源第三方组件，通常用来读、写和处理Excel，Word等文档的开发工具。

　　2、为什么要使用NPOI？

　　　　微软的.NET平台已经有相应的操作Excel，Word等文档的组件，但是用过就知道：

　　　　① 对象方法参数繁杂冗长，不容易理解使用；

　　　　② 如果是在服务器端部署，还必须安装office才能提供支持；

　　　　③ 额外的工作处理，例如使用Excel组件时总会打开Excel.exe进程，我们需要考虑如何回收；

　　　　④ 讨厌的弹框提示处理，例如使用Excel组件时总会有一些莫名的弹框提示，我们也需要考虑处理；

　　　　相对的NPOI组件的优势：

　　　　① 操作的常见对象及对象包含的方法容易理解使用；

　　　　② 不用担心目标是否安装office，可以直接使用；

　　　　③ 不用额外考虑进程回收；

　　　　④ 没有特别的弹框提示；

　　3、NPOI的现状及劣势？

　　　　官方稳定版：V - 2.1.3.1；

　　　　官方并没有详细的说明文档，只找到一个繁体中文版（见参考资料）学习笔记应该是笔友的，

　　　　碰到问题全靠百度和在园子里提问；

　　　　所以劣势就显而易见了，使用免费开源的遇到问题只能靠自己疯狂百度和提问了，有时候刚好项目用到然后出现出人意料的问题真是急死人，

　　　　技术大牛直接研究源码解决问题的膜拜中~

二、NPOI常用操作对象及方法

　　A.　HSSFWorkbook

　　　　1、工作簿对象；

　　　　2、实例化时有8个重载方法，常用的有2个：

　　　　　　① HSSFWorkbook workbook = new HSSFWorkbook();

　　　　　　② // 文件路径

　　　　　　　string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\使用NPOI并根据业务需求合并单元格.xls";
　　　　　　 // 使用文件流，并创建HSSFWorkbook对象
　　　　　　　FileStream excle = new FileStream(sFile, FileMode.Open, FileAccess.Read);
　　　　　　　HSSFWorkbook workbook = new HSSFWorkbook(excle);

　　　　3、常用成员变量及方法：

　　　　　　① 获取工作簿的sheet页数：workbook.NumberOfSheets；

　　　　　　② 保存方法：workbook.Write(excle);（excle是上面定义的文件流变量）；

　　　　　　③ 创建ISheet对象，详见ISheet；

　　　　　　④ 获取ISheet对象，详见ISheet；

　　B.　ISheet

　　　　1、工作页，常说的Excel里面的sheet页；

　　　　2、实例化：

　　　　　　① 通过HSSFWorkbook创建：ISheet sheet = workbook.CreateSheet();

　　　　　　② 通过HSSFWorkbook获取：ISheet sheet = workbook.GetSheetAt(i);

　　　　3、常用成员变量及方法：

　　　　　　① FirstRowNum：sheet页的数据首行，这里指第一个包含数据的行；

　　　　　　② LastRowNum：sheet页的数据末行，从FirstRowNum开始一直到该行，该行后面的行都没有数据；

　　　　　　③ 创建IRow对象，详见IRow；

　　　　　　④ 获取IRow对象，详见IRow；

　　　　　　⑤ 为sheet页指定合并区域，详见CellRangeAddress；

　　　　　　⑥ 设置列宽，包含2个参数列的序号和宽度（宽度为0时隐藏）：sheet.SetColumnWidth(0, (int)((15 + 0.72) * 256))，

　　　　　　　这表示设置第0列的列宽是15，这里的15就是Excel中选中后可以设定的值，需要做这样的转化；

　　C.　IRow

　　　　1、行，sheet页里面的行对象；

　　　　2、实例化：

　　　　　　① 通过ISheet创建：IRow row = sheet.CreateRow(0)，索引从0开始；

　　　　　　② 通过ISheet获取：IRow row = sheet.GetRow(0)，索引从0开始；

　　　　3、常用成员变量及方法：

　　　　　　① 获取当前行索引：row.RowNum；　　　　　　

　　　　　　② 创建ICell对象，详见ICell；

　　　　　　③ 获取ICell对象，详见ICell；

　　D.　ICell

　　　　1、单元格，某行某列对应的单元格对象；

　　　　2、实例化：

　　　　　　① 通过IRow创建：ICell cell = row.CreateCell(0)，索引从0开始；

　　　　　　② 通过IRow获取：ICell cell = row.GetCell(0) 或者 row.Cells[0]，索引从0开始；

　　　　3、常用成员变量及方法：

　　　　　　① 获取当前单元格内的文本值：cell.ToString()；

　　　　　　② 给单元格赋值：cell.SetCellValue("我是值");

　　　　　　③ 给单元格设置样式：cell.CellStyle，详见ICellStyle；

　　E.　CellRangeAddress

　　　　1、合并区域对象，通常包含4个参数：起始行，截至行，起始列，截至列；

　　　　2、实例化：CellRangeAddress cellRange = new CellRangeAddress(firstrow, lastrow, firstcol, lastcol);

　　　　3、常用成员变量及方法：

　　　　　　① 在Sheet中指定合并区域：sheet.AddMergedRegion(cellRange)，cellRange指上面定义的对象变量；

　　F.　ICellStyle

　　　　1、单元格样式对象；

　　　　2、实例化：详见示例中 “设置单元格样式”；

　　　　3、常用单元格数据样式：

　　　　　　① 数字格式样式：　　　　　　

 1             // 文件路径
 2             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\数字格式展示" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
 3             // 实例化一个工作簿对象
 4             HSSFWorkbook workbook = new HSSFWorkbook();
 5             // 单元格样式
 6             IDataFormat dataformat = workbook.CreateDataFormat();
 7             // 创建sheet页
 8             ISheet sheet = workbook.CreateSheet();
 9             // 创建Cell
10             // 1、数字大写样式：
11             IRow row0 = sheet.CreateRow(0);
12             row0.CreateCell(0).SetCellValue(2017.26);
13             row0.CreateCell(1).SetCellValue("设置为大写");
14             ICellStyle cellstyle0 = workbook.CreateCellStyle();
15             cellstyle0.DataFormat = dataformat.GetFormat("[DbNum2][$-804]General");
16             row0.Cells[0].CellStyle = cellstyle0;
17             // 2、设置小数精度：
18             IRow row1 = sheet.CreateRow(1);
19             row1.CreateCell(0).SetCellValue(2017.26);
20             row1.CreateCell(1).SetCellValue("设置小数精度");
21             ICellStyle cellstyle1 = workbook.CreateCellStyle();
22             cellstyle1.DataFormat = dataformat.GetFormat("0.0");
23             row1.Cells[0].CellStyle = cellstyle1;
24             // 3、设置分位符：
25             IRow row2 = sheet.CreateRow(2);
26             row2.CreateCell(0).SetCellValue(12342017.26);
27             row2.CreateCell(1).SetCellValue("设置分位符");
28             ICellStyle cellstyle2 = workbook.CreateCellStyle();
29             cellstyle2.DataFormat = dataformat.GetFormat("#,##0.0");
30             row2.Cells[0].CellStyle = cellstyle2;
31             // 4、科学计数法：
32             IRow row3 = sheet.CreateRow(3);
33             row3.CreateCell(0).SetCellValue(12342017.26);
34             row3.CreateCell(1).SetCellValue("设置为科学计数法");
35             ICellStyle cellstyle3 = workbook.CreateCellStyle();
36             cellstyle3.DataFormat = dataformat.GetFormat("0.00E+00");
37             row3.Cells[0].CellStyle = cellstyle3;
38             // 5、负数标红：
39             IRow row4 = sheet.CreateRow(4);
40             row4.CreateCell(0).SetCellValue(-12342017.26);
41             row4.CreateCell(1).SetCellValue("设置负数标红");
42             ICellStyle cellstyle4 = workbook.CreateCellStyle();
43             cellstyle4.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00");
44             row4.Cells[0].CellStyle = cellstyle4;
45             // 6、百分数：
46             IRow row5 = sheet.CreateRow(5);
47             row5.CreateCell(0).SetCellValue(0.86);
48             row5.CreateCell(1).SetCellValue("设置显示为百分数");
49             ICellStyle cellstyle5 = workbook.CreateCellStyle();
50             cellstyle5.DataFormat = dataformat.GetFormat("0.00%");
51             row5.Cells[0].CellStyle = cellstyle5;
52             // 保存Excel并返回路径
53             FileStream file = new FileStream(sFile, FileMode.Create);
54             workbook.Write(file);
55             file.Close();

常用数字格式设置展示

　　　　　　效果图：

　　　　　　② 时间和日期格式样式：

 1             // 文件路径
 2             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\时间和日期格式展示" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
 3             // 实例化一个工作簿对象
 4             HSSFWorkbook workbook = new HSSFWorkbook();
 5             // 单元格样式
 6             IDataFormat dataformat = workbook.CreateDataFormat();
 7             // 创建sheet页
 8             ISheet sheet = workbook.CreateSheet();
 9             // 创建Cell
10             // 1、yyyy-MM-dd hh-mm-ss：
11             IRow row0 = sheet.CreateRow(0);
12             row0.CreateCell(0).SetCellValue(DateTime.Now);
13             row0.CreateCell(1).SetCellValue("yyyy-MM-dd hh-mm-ss");
14             ICellStyle cellstyle0 = workbook.CreateCellStyle();
15             cellstyle0.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh-mm-ss");
16             row0.Cells[0].CellStyle = cellstyle0;
17             // 2、yyyy:MM:dd hh:mm:ss：
18             IRow row1 = sheet.CreateRow(1);
19             row1.CreateCell(0).SetCellValue(DateTime.Now);
20             row1.CreateCell(1).SetCellValue("yyyy:MM:dd hh:mm:ss");
21             ICellStyle cellstyle1 = workbook.CreateCellStyle();
22             cellstyle1.DataFormat = dataformat.GetFormat("yyyy:MM:dd hh:mm:ss");
23             row1.Cells[0].CellStyle = cellstyle1;
24             // 3、yyyy年MM月dd日：
25             IRow row2 = sheet.CreateRow(2);
26             row2.CreateCell(0).SetCellValue(DateTime.Now);
27             row2.CreateCell(1).SetCellValue("yyyy年MM月dd日");
28             ICellStyle cellstyle2 = workbook.CreateCellStyle();
29             cellstyle2.DataFormat = dataformat.GetFormat("yyyy年MM月dd日");
30             row2.Cells[0].CellStyle = cellstyle2;
31             // 4、yyyy-MM-dd：
32             IRow row3 = sheet.CreateRow(3);
33             row3.CreateCell(0).SetCellValue(DateTime.Now);
34             row3.CreateCell(1).SetCellValue("yyyy-MM-dd");
35             ICellStyle cellstyle3 = workbook.CreateCellStyle();
36             cellstyle3.DataFormat = dataformat.GetFormat("yyyy-MM-dd");
37             row3.Cells[0].CellStyle = cellstyle3;
38             // 保存Excel并返回路径
39             FileStream file = new FileStream(sFile, FileMode.Create);
40             workbook.Write(file);
41             file.Close();

时间和日期格式展示

　　　　　　效果图：

　　　　　　③ 文本格式，通常用来处理身份证号等长数字类字符串：

 1             // 文件路径
 2             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\文本格式展示" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
 3             // 实例化一个工作簿对象
 4             HSSFWorkbook workbook = new HSSFWorkbook();
 5             // 单元格样式
 6             IDataFormat dataformat = workbook.CreateDataFormat();
 7             // 创建sheet页
 8             ISheet sheet = workbook.CreateSheet();
 9             // 创建Cell
10             // 1、文本格式：
11             IRow row0 = sheet.CreateRow(0);
12             row0.CreateCell(0).SetCellValue("1234567890258");
13             row0.CreateCell(1).SetCellValue("文本格式");
14             ICellStyle cellstyle0 = workbook.CreateCellStyle();
15             cellstyle0.DataFormat = dataformat.GetFormat("@");
16             row0.Cells[0].CellStyle = cellstyle0;
17             // 2、文本格式：
18             IRow row1 = sheet.CreateRow(1);
19             row1.CreateCell(0).SetCellValue("1234567890258");
20             row1.CreateCell(1).SetCellValue("文本格式");
21             ICellStyle cellstyle1 = workbook.CreateCellStyle();
22             cellstyle1.DataFormat = dataformat.GetFormat("text");
23             row1.Cells[0].CellStyle = cellstyle1;
24             // 保存Excel并返回路径
25             FileStream file = new FileStream(sFile, FileMode.Create);
26             workbook.Write(file);
27             file.Close();

文本格式展示

　　　　　　效果图：

三、NPOI常用用法及示例

　　需引用：NPOI.dll，NPOI.OOXML.dll，NPOI.OpenXml4Net.dll，NPOI.OpenXmlFormats.dll；

　　在类文件中做引用：

　　using System.IO;

　　using NPOI.HSSF.UserModel;

　　using NPOI.SS.UserModel;

　　1、使用NPOI导出数据至Excel：

 1         /// <summary>
 2         /// 自定义DataTable
 3         /// </summary>
 4         /// <returns></returns>
 5         private DataTable GetMyTable()
 6         {
 7             DataTable dt = new DataTable();
 8             // 列头信息
 9             dt.Columns.Add("id", typeof(string));
10             dt.Columns.Add("name", typeof(string));
11             dt.Columns.Add("age", typeof(string));
12             dt.Columns.Add("salary", typeof(string));
13             // 数据行
14             // 1
15             DataRow dr = dt.NewRow();
16             dr["id"] = "1";
17             dr["name"] = "赵大";
18             dr["age"] = "27";
19             dr["salary"] = "5456.1";
20             dt.Rows.Add(dr);
21             // 2
22             dr = dt.NewRow();
23             dr["id"] = "2";
24             dr["name"] = "钱二";
25             dr["age"] = "27";
26             dr["salary"] = "5456.2";
27             dt.Rows.Add(dr);
28             // 3
29             dr = dt.NewRow();
30             dr["id"] = "3";
31             dr["name"] = "孙三";
32             dr["age"] = "27";
33             dr["salary"] = "5456.3";
34             dt.Rows.Add(dr);
35             return dt;
36         }

自定义DataTable

 1         /// <summary>
 2         /// 将DataTable导出至Excel
 3         /// </summary>
 4         /// <param name="dt">DataTable</param>
 5         /// <param name="sFileName">Excel文件名</param>
 6         /// <returns></returns>
 7         private string ExportDataTable2Excle(DataTable dt, string sFileName)
 8         {
 9             // 实例化一个工作簿对象
10             HSSFWorkbook workbook = new HSSFWorkbook();
11             // 导出路径及文件名处理
12             string sExportFile = HttpContext.Current.Server.MapPath("~/OutputFile") + "\" + sFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
13             // sheet数处理
14             int iDataCount = dt.Rows.Count;
15             int iSheetPages = (int)Math.Ceiling(iDataCount / 65530.00);
16             // 插入数据
17             for (int i = 0; i < iSheetPages; i++)
18             {
19                 // 实例化一个工作表对象，并设置工作表名
20                 ISheet sheet = workbook.CreateSheet("第" + (i + 1).ToString() + "页");
21                 // 标题行，读取DataTable列名
22                 IRow headRow = sheet.CreateRow(0);
23                 foreach (DataColumn column in dt.Columns)
24                 {
25                     headRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
26                 }
27                 // 数据的起始和结束行处理
28                 int iRowIndex = 1;
29                 int iStartRow = 65530 * i;
30                 int iEndRow = 65530 * (i + 1);
31                 iEndRow = dt.Rows.Count < iEndRow ? iDataCount : iEndRow;
32                 // 插入数据
33                 for (int m = iStartRow; m < iEndRow; m++)
34                 {
35                     DataRow row = dt.Rows[m];
36                     IRow dataRow = sheet.CreateRow(iRowIndex);
37                     foreach (DataColumn column in dt.Columns)
38                     {
39                         dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
40                     }
41                     iRowIndex++;
42                 }
43             }
44             // 保存Excel并返回路径
45             FileStream file = new FileStream(sExportFile, FileMode.Create);
46             workbook.Write(file);
47             file.Close();
48             return sExportFile;
49         }

导出方法体

1         protected void btnNPOIExport_Click(object sender, EventArgs e)
2         {
3             DataTable dt = GetMyTable();
4             ExportDataTable2Excle(dt, "测试");
5         }

按钮事件调用

　　说明：

　　　　①涉及操作对象：HSSFWorkbook，ISheet，IRow；

　　　　②使用文件流进行读写；

　　　　③对Excel表单做预溢出处理，最大行数65536；

　　效果图：

　　2、使用NPOI读取Excel：　　

 1             // Excel文件路径
 2             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\测试20170206114119.xls";
 3             // 使用文件流，并创建HSSFWorkbook对象
 4             FileStream excle = new FileStream(sFile, FileMode.Open, FileAccess.Read);
 5             HSSFWorkbook workbook = new HSSFWorkbook(excle);
 6             // 获取Excel sheet页数
 7             int iSheetPages = workbook.NumberOfSheets;
 8             // 遍历数据
 9             for (int i = 0; i < iSheetPages; i++)
10             {
11                 // 获取当前sheet页
12                 ISheet sheet = workbook.GetSheetAt(i);
13                 if (sheet != null)
14                 {
15                     for (int iRow = sheet.FirstRowNum; i < sheet.LastRowNum; iRow++)
16                     {
17                         // 获取当前行
18                         IRow row = sheet.GetRow(iRow);
19                         if (row != null)
20                         {
21                             for (int iCol = 0; iCol < row.LastCellNum; iCol++)
22                             {
23                                 // 获取当前单元格
24                                 ICell cell = row.GetCell(iCol);
25                                 if (cell != null)
26                                 {
27                                     Console.Write(cell.ToString() + "     ");
28                                 }
29                             }
30                             Console.WriteLine();
31                         }
32                     }
33                 }
34             }
35             Console.ReadLine();

使用NPOI读取Excel

　　说明：

　　①涉及操作对象：HSSFWorkbook，ISheet，IRow，ICell

　　效果图：

　　3、使用NPOI合并单元格：

 1             // 文件路径
 2             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\测试" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
 3             // 实例化一个工作簿对象
 4             HSSFWorkbook workbook = new HSSFWorkbook();
 5             // 创建sheet页
 6             ISheet sheet = workbook.CreateSheet();
 7             // 给第一个单元格赋值
 8             sheet.CreateRow(0).CreateCell(0).SetCellValue("我是合并单元格");
 9             // 给sheet页添加一个合并区域
10             sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 3, 0, 3));
11             // 保存Excel并返回路径
12             FileStream file = new FileStream(sFile, FileMode.Create);
13             workbook.Write(file);
14             file.Close();

使用NPOI合并单元格

　　说明：

　　①涉及操作对象：HSSFWorkbook，ISheet，CellRangeAddress

　　效果图：

　　4、使用NPOI并根据业务需求合并单元格：　　

 1             // 模拟需求：
 2             // 这里认为相同的name下有相同的salary，根据name合并salary
 3             // 可能需合并的数据列
 4             int mergeSalaryCol = 3;
 5             // 合并的初始行号
 6             int iMergeRow = 1;
 7             // 文件路径
 8             string sFile = "D:\2012Project\winFormTest\WebApplication1\OutputFile\使用NPOI并根据业务需求合并单元格.xls";
 9             // 使用文件流，并创建HSSFWorkbook对象
10             FileStream excle = new FileStream(sFile, FileMode.Open, FileAccess.Read);
11             HSSFWorkbook workbook = new HSSFWorkbook(excle);
12             // 获取Excel sheet页数
13             int iSheetPages = workbook.NumberOfSheets;
14             // 遍历数据并合并单元格
15             for (int i = 0; i < iSheetPages; i++)
16             {
17                 // 获取当前sheet页
18                 ISheet sheet = workbook.GetSheetAt(i);
19                 if (sheet != null)
20                 {
21                     // 跳过标题行
22                     for (int iRow = sheet.FirstRowNum + 1; iRow <= sheet.LastRowNum; iRow++)
23                     {
24                         // 获取当前行name列值
25                         string sCurrentName = sheet.GetRow(iRow).Cells[1].ToString();
26                         // 获取下一行name列值，当到达末行时，给个不会出现的数据方便比对
27                         string sNextName = iRow == sheet.LastRowNum ? "无关紧要的比对数据" : sheet.GetRow(iRow + 1).Cells[1].ToString();
28                         if (sCurrentName != sNextName)
29                         {
30                             // 设置合并区域
31                             CellRangeAddress cellRange = new CellRangeAddress(iMergeRow, iRow, mergeSalaryCol, mergeSalaryCol);
32                             sheet.AddMergedRegion(cellRange);
33                             // 合并行号更新
34                             iMergeRow = iRow + 1;
35                         }
36                     }
37                 }
38             }
39             // 更新Excel文件并保存
40             excle = new FileStream(sFile, FileMode.Create);
41             workbook.Write(excle);
42             excle.Close();

使用NPOI并根据业务需求合并单元格

　　说明：

　　①涉及操作对象：HSSFWorkbook，ISheet，CellRangeAddress；

　　②注意效果图红色框框部分；

　　效果图：

　　　　-- 合并前

　　　　-- 合并后

　　5、使用NPOI时设置单元格样式：　　

 1         /// <summary>
 2         /// 设置金额列单元格格式
 3         /// </summary>
 4         /// <param name="workbook"></param>
 5         /// <returns></returns>
 6         private static ICellStyle SetMoneyCellStyle(HSSFWorkbook workbook)
 7         {
 8             ICellStyle cellStyle = workbook.CreateCellStyle();
 9             cellStyle.BorderLeft = BorderStyle.Thin;
10             cellStyle.BorderRight = BorderStyle.Thin;
11             cellStyle.BorderTop = BorderStyle.Thin;
12             cellStyle.BorderBottom = BorderStyle.Thin;
13             IDataFormat format = workbook.CreateDataFormat();
14             cellStyle.DataFormat = format.GetFormat("#,##0.00");
15             return cellStyle;
16         }

设置金额列单元格格式

1 ICellStyle moneyCellStyel = SetMoneyCellStyle(workbook);
2 dataRow.Cells[0].CellStyle = moneyCellStyel;

设置单元格样式调用

　　说明：

　　几种常见单元格样式设置见第二点对象及方法。

四、NPOI问题收集

　　1、我自己发的博问：https://q.cnblogs.com/q/89090/；

五、参考资料

百度百科
百度搜索
CSDN 博文参考
博客园博文参考
官网：http://npoi.codeplex.com/
繁体版使用文档说明：https://dotblogs.com.tw/killysss/archive/2010/01/27/13344.aspx
其他

六、其他开源组件记录

　　1、MyXls(http://sourceforge.net/projects/myxls/)
　　2、Koogra(http://sourceforge.net/projects/koogra/)
　　3、ExcelLibrary(http://code.google.com/p/excellibrary/)
　　4、ExcelPackage(http://excelpackage.codeplex.com/)
　　5、EPPlus(http://epplus.codeplex.com/)
　　6、LinqToExcel(http://code.google.com/p/linqtoexcel/)
　　7、NetOffice(http://netoffice.codeplex.com/) 需安装Office Excel

　　转自网友收集：https://my.oschina.net/Yamazaki/blog/486508