将DataTable中的数据保存到Excel （二） 使用NPOI

背景

前面写过一篇DataTable导出到Excel的文章，使用的是Office COM组件进行导入导出，用户体验感有点差，对环境依赖性太强（比如“检索COM类工厂…”错误），且需要通过Marshal.ReleaseComObject方法释放资源，但依然避免不了性能差。

之后一直寻找新的方法，发现了NPOI，一个干净又强大且免费使用的开源控件。

1 NPOI 简介

NPOI是一个开源的.NET组件，可以读写Excel文件，支持Office97-2007格式，功能强大。且不需要安装Microsoft Ofifce.

主要优势：
1 免费使用，不涉及任何版权问题
2 支持对标准Excel的读写（单元格格式、数据格式、公式等等）
3 基于.net 2.0 也支持xlsx 和docx格式（当然更高框架的4.0也支持）
4 比起使用Office 的API更加方便，更人性化，易于上手
5 性能优异（相对于前面的方法）

GitHub开源地址: https://github.com/nissl-lab/npoi
使用案例(Demo)地址： https://github.com/nissl-lab/npoi-examples

2 使用NPOI

可以通过Nuget安装包管理下载安装。

文章目前用的是NPOI的最新版本2.0.6.0 ，使用到的程序集有 NPOI.dll、 NPOI.OOXML.dll、NPOI.OpenXmlFormats.dll直接添加到项目引用即可。

最常用的对象主要位于NPOI.HSSF.UserMode空间下，主要有以下对象，对应的接口位于NPOI.SS.UserMode空间下

2.1 创建一个简单的工作簿

创建一个Workbook对象，添加一个工作表，在工作表中添加行和列，并设置单元格的内容。

using System.IO;using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

 void CreateSheet(){//创建workbook对象IWorkbook workbook = new HSSFWorkbook();//创建工作表ISheet sheet = workbook.CreateSheet("testSheet1");workbook.CreateSheet("testSheet2");workbook.CreateSheet("testSheet3");//在工作表1中添加行IRow row = sheet.CreateRow(0);sheet.CreateRow(1);//在行中添加一列ICell cell = row.CreateCell(0);//设置单元格的内容cell.SetCellValue("Hello Test");//继续添加列cell = row.CreateCell(1);//设置单元格的内容cell.SetCellValue("HHH");//写入文件中FileStream file = new FileStream(@"test.xls", FileMode.Create);workbook.Write(file);file.Close();}

效果图如下：

2.2 简单的读取内容

使用FileStream读取上一小节生成的Excel的内容

  void ReadExcel(){//文件路径string path = @"test.xls";//创建workbook对象IWorkbook workBook = null;using(FileStream fs = File.OpenRead(path)){//文件流读取workBook = new HSSFWorkbook(fs);//读取工作簿ISheet sheet = workBook.GetSheetAt(0);if(sheet == null){return;}//读取行IRow row = sheet.GetRow(0);if(row == null){return;}//获取单元格ICell cell = row.GetCell(0);if(cell == null){return;}//获取单元格内容string value = cell.ToString();//打印测试一下读取到的值Console.WriteLine(value);cell = row.GetCell(1);if (cell == null){return;}//获取单元格内容value = cell.ToString();//打印测试一下读取到的值Console.WriteLine(value);}}

效果图

2.3 将DataTable数据导出到Excel(NPOI)

将DataTable中的数据导出到本地，使用Excel保存。

     private DataTable dataTable;/// <summary>/// 初始化DataTable数据/// </summary>private void InitTable(){dataTable = new DataTable();dataTable.Columns.Add("姓名", typeof(string));dataTable.Columns.Add("年龄", typeof(string));dataTable.Columns.Add("城市", typeof(string));List<string> citys = new List<string>{"广州","深圳","佛山","东莞","中山", "珠海","江门","肇庆","惠州"};Random random = new Random();for(int i=0; i<500; i++){DataRow row = dataTable.NewRow();//姓名row[0] = "小" + i.ToString("d3");//年龄row[1] = random.Next(18, 36).ToString();//城市row[2] = citys[i % 9];dataTable.Rows.Add(row);}}/// <summary>/// DataTable导出到Excel/// </summary>void DataTableToExcel(){//初始化DataTable的数据InitTable();//创建工作簿对象IWorkbook workBook = new HSSFWorkbook();//创建一个sheetISheet sheet = workBook.CreateSheet("DataTable");//创建标题行IRow headerRow = sheet.CreateRow(0);ICell cell;for(int i=0; i<dataTable.Columns.Count; i++){cell = headerRow.CreateCell(i);cell.SetCellValue(dataTable.Columns[i].Caption);}//将内容写入for(int i=0; i<dataTable.Rows.Count; i++){//创建新行headerRow = sheet.CreateRow(i + 1);for (int j = 0; j < dataTable.Columns.Count; j++){//创建单元格并设置内容cell = headerRow.CreateCell(j);cell.SetCellValue(dataTable.Rows[i][j].ToString());}}//写入文件using(FileStream fs = File.OpenWrite(@"DataTable.xls")){workBook.Write(fs);fs.Close();}}

效果图：导出速度还是很快的

2.4 Excel(NPOI)导入到DataTable

读取Excel数据，并存到DataTable中

/// <summary>/// 读取Excel数据，存储到DataTable/// </summary>DataTable ExcelToDataTable(){// 初始化DataTableDataTable table = new DataTable();string path = @"DataTable.xls";using(FileStream fs = File.OpenRead(path)){//获取文件IWorkbook workBook = new HSSFWorkbook(fs);if(workBook == null){return table;}//获取SheetISheet sheet = workBook.GetSheetAt(0);if (sheet == null){return table;}//获取标题行IRow headerRow = sheet.GetRow(0);if(headerRow == null){return table;}//获取列和行的数量int cellCount = headerRow.LastCellNum;int rowCount = sheet.LastRowNum;for(int i=headerRow.FirstCellNum; i < cellCount; i++){//设置列标题DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);table.Columns.Add(column);}for(int i= (sheet.FirstRowNum + 1); i<=rowCount; i++){//行DataRow dataRow = table.NewRow();IRow row = sheet.GetRow(i);if(row == null){continue;}for(int j= row.FirstCellNum; j < cellCount; j++){//列if(row.GetCell(j) != null){dataRow[j] = row.GetCell(j).ToString();}}//将行添加到DataTabletable.Rows.Add(dataRow);}}//Console.WriteLine(table.Rows[0][0].ToString());//Console.WriteLine(table.Rows[0][1].ToString());//Console.WriteLine(table.Rows[0][2].ToString());return table;}

效果图：验证一下，存入Table中的数据是对的

3 NPOI 总结

1 开发中主要用到的对象是在 NPOI.HSSF.UserMode命名空间下

2 对应的接口位于 NPOI.SS.UserMode命名空间下

3 主要对象 HSSFWorkbook（IWorkbook）、 HSSFSheet（ISheet） 、 HSSFRow (IRow)、HSSFCell (ICell)