1 官方资源
- github：可以看到最新源代码和例子，推荐查看。
- nuget：可下载最新的发布文件，推荐查看。
- codeplex：由于微软代码托管平台 CodePlex 已经关闭，上面也没有啥东西值得看了，或许可以让你回顾下历史吧。
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 名词解释
| 名词 | 解释 |
|---|---|
| Workbook | 工作薄，一个文件包含一个工作薄，一个工作薄可以包含若干个工作表。 |
| Sheet | 工作表 |
| HSSFWorkbook | 处理 xls 文件类型的 NPOI 操作类，继承 IWorkbook 。 |
| XSSFWorkbook | 处理 xlsx 文件类型的 NPOI 操作类，继承 IWorkbook 。 |
4.3 代码示例
下面列举常用使用场景，更多的代码示例可以参考 官方源码 exmaples 目录。
示例功能列表如下：
- 创建工作薄和保存文件
- 添加自定义属性
- 创建工作表、行和列
- 文件保护
- 给单元格添加链接
- 设置单元格字体
- 设置单元格边框样式
- 设置单元格数据格式
- 设置列宽和行高
- 合并单元格
- 循环赋值
- 下载文件
- 冻结与分隔
- 使用简单公式
- 插入图片
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");
// URL
ICell 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=20
IFont 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=ncl
Response.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");
// 设置 A2
s1.CreateRow(1).CreateCell(0).SetCellValue(-5);
// 设置 B2
s1.GetRow(1).CreateCell(1).SetCellValue(1111);
// 设置 C2
s1.GetRow(1).CreateCell(2).SetCellValue(7.623);
// 设置 A3
s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);
// 设置 A4=A2+A3
s1.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 版本。
网友评论