NPOI (自动保存的)

NPOI

1. 三种EXCEL抽取方式介绍

在对EXCEL进行数据抽取时，现在主流的技术方法是：oledb,com组件，NPOI

1.采用oledb读取excel文件，用这种方法读取Excel速度还是非常的快的，但这种方式读取数据的时候不太灵活，不过可以在 DataTable 中对数据进行一些删减修改。

这种方式将Excel作为一个数据源，直接用Sql语句获取数据了。所以读取之前要知道此次要读取的Sheet（当然也可以用序号，类似dt.Row[0][0]。这样倒是不需要知道Sheet）

优点：读取方式简单、读取速度快，可在不关闭当前Excel的情况下对该EXCEL进行sql获取数据的操作。

缺点：除了读取过程不太灵活之外，这种读取方式还有个弊端就是，当Excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。

个人意见：oledb还是比较好用的，内存不够的情况至今也没见过。

2. Com组件的方式读取Excel

这种方式需要先引用 Microsoft.Office.Interop.Excel 。首选说下这种方式的优缺点

优点：可以非常灵活的读取Excel中的数据

缺点：如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。最重要的一点因为是基于单元格方式读取的，所以数据很慢。

个人意见：不建议用这种方法，基于单元格的读取方式很显然是落后低效的。

3. NPOI方式读取Excel，NPOI是一组开源的组件，类似Java的 POI。包括：NPOI、NPOI.HPSF、NPOI.HSSF、NPOI.HSSF.UserModel、NPOI.POIFS、NPOI.Util，下载的时候别只下一个噢

优点：读取Excel速度较快，读取方式操作灵活性，03版本用HSSFWorkbook，07版本用XSSFWorkbook。可解决几乎所有的EXCEL问题，如单元格合并，插入公式，单元格颜色等等。

缺点：不能对已打开的当前EXCEL文件进行操作。需要导入多个dll，而且要注意dll所对应的net framework版本是2.0还是4.0。功能虽然十分强大，但由于代码不开源导致dll里面的函数方法需要自己琢磨或者上网查询。

2. 关于NPOI的dll和using

主要包含以下4个dll：

NPOI.dll,

NPOI.OOXML.DLL,

NPOI.OpenXml4Net.dll,

NPOI.OpenXmlFormats.dll

值得注意的是以上dll分为.net framework2.0版以及.net framework4.0版，引用的时候需要注意下。

在程序中引用以上四个dll后，需在程序开头写上以下代码（方便使用）：

using NPOI.SS.UserModel;

using NPOI.SS.Util;

using NPOI.XSSF.UserModel;

using NPOI.HSSF.UserModel;

其中excel03版本用的是HSSFWorkbook，excel07版本用的是XSSFWorkbook

3. NPOI基本操作

在利用NPOI对EXCEL进行基本操作前，需要熟悉以下几个概念：

整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；列：Column；单元格Cell。Cell是excel中最基本的单位，当然也可以用range来选择多个单元格范围。

知道上面这几个概念后我们就可以进行下一步操作了。首先NPOI对EXCEL的操作主要分为两类：读取和创建。一个是对已有的EXCEL进行操作，一个是创建新的EXCEL，这两种情况对应的代码有着明显的不同。

注意：这里贴出来的是源代码，方便大家理解，在第5章中我对下列代码进行了封装。

1.1 读取EXCEL

1.1.1 工作薄和工作表

//获取指定excel的工作薄

FileStream fs = new FileStream(@d:\\123.xls, FileMode.Open, FileAccess.Read);

IWorkbook workbook = new HSSFWorkbook(fs); //03版

IWorkbook workbook = new XSSFWorkbook(fs); //07版

//获取名称为mySheet的表

ISheet sheet = workbook.GetSheet(\"mySheet\");

//获取第i个表(从1开始计数，排列并不一定符合EXCEL中显示的SHEET顺序)，及第i个表的名称

ISheet sheet = workbook.GetSheetAt(i);

String sheetName = sheet.SheetName;

//保存（另存为）工作簿

using (FileStream ffs = new FileStream(@\"d:\est.xls\FileAccess.Write))

{

workbook.Write(ffs);

}

1.1.2 行列，单元格

//获取第i行，第j列的单元格

IRow row = sheet.GetRow(i-1);

ICell cell = row.GetCell(j-1);

或者 ICell cell = sheet.GetRow(i-1).GetCell(j-1);

注意：可以选择某一行，或者某个单元格，但并不能选择某一列。

单元格cell下有很多方法，如cell.CellFormula可以写入公式，cell.CellType可以写入单元格类型等等。

1.2 创建EXCEL

1.2.1 工作薄和工作表

//创建一个新的工作薄

IWorkbook workbook = new HSSFWorkbook(); //03版

IWorkbook workbook = new XSSFWorkbook(); //07版

//创建一个名称为mySheet的表

ISheet sheet = workbook.CreateSheet(\"mySheet\");

//保存工作簿

using (FileStream fs = new FileStream(@\"d:\est.xls\", FileMode.Create, FileAccess.Write))

{

workbook.Write(fs);

}

1.2.2 行列，单元格

//创建第i行

IRow row = sheet.CreateRow(i-1);

//获取新创建sheet中的第i行，第j列的单元格

ICell cell = sheet.CreateRow(i-1).CreateCell(j-1);

String cellValue =

一定要注意GetRow(i)和CreateRow(i)的区别，前者对应读取EXCEL，后者对应创建EXCEL。

1.3 读写单元格

1.3.1 读取单元格中内容

switch (cell.CellType)

{

case CellType.Blank: //BLANK:

return null;

case CellType.Boolean: //BOOLEAN:

return cell.BooleanCellValue;

case CellType.Numeric: //NUMERIC:

return cell.NumericCellValue;

case CellType.String: //STRING:

return cell.StringCellValue;

case CellType.Error: //ERROR:

return cell.ErrorCellValue;

case CellType.Formula: //FORMULA:

return \"=\" + cell.CellFormula;

}

通过CellType来返回单元格中不同数值类型的内容，该方法命名为

private static object GetCellValueType(ICell cell)

但有个问题，CellType中并有没datetime属性，如果单元格中数值类型是datetime，可以用cell.DateCellValue返回。

1.3.2 写入单元格

cell.SetValue()可以满足string, double，bool，Datetime, IRichTextString这5个数值类型的写入。（不包括Formula）

如cell.SetValue(3)表示将double类型的数字3写入单元格中。

如果cell.CellType = CellType.Formula,则可以按照下面这种方式写入。

Cell.SetCellFormula(string formula);

1.4 总结

只要知道以上EXCEL的基本操作方法，就可以完成些最基本的操作，如修改单元格内容，找到名称为“037”的sheet等。

但如果想要将单元格内容设置成超链接，或是输入公式，改变字体，则需要了解后面几章节中的内容。

4 EXCEL与DataTable之间的转换

EXCEL与datatable之间的交互一直是EXCEL二次开发中永恒的话题，首先我们要了解EXCEL与datatable有何不同。

4.1 两者的构成

4.1.1 EXCEL的构成

一个EXCEL是由一个工作簿workbook构成，一个workbook中可以有好几个sheet页，每个sheet页中有无数个单元格cell，每个单元格都是且唯一的。所谓指的是每个cell可以有各自不同的数值类型（如string，int，datetime，formula等），不受行列的影响，准确来说EXCEL也没有列标题列属性这一概念，它只是由无数个cell组成的表格。

4.1.2 DataTable的构成

Datatable是由行列组成，行列的交叉为dt.Rows[i][j]，即EXCEL中的单元格。但这之间有细微的不同，Datatable有列标题列属性这一概念，如果你没有设置列标题和列属性，则无法创建Datatable或是给dt.Rows[i][j]赋值。列标题按如下代码设置

DataColumn column = new DataColumn(colName);

Column.DataType = typeof(System.int);

dt.Columns.Add(column);

列标题名称colName必须为string，该列的数据属性DataType可以人为设置，如int，string，double等等（默认为string），但一旦设置好了DataType=int类型，就不能在该列下填写非int类型的数据。这点和EXCEL不一样，EXCEL没有列的条件，单元格是的。

datatable设置好列标题后，得按整行为单位来添加数据。

DataRow dr = dt.NewRow();

dr[j]=? (j=0,1,2….)

dt.Rows.add(dr);

这样才算新建好了一个datatable。

4.2 EXCEL导出DataTable

下面的方法返回的是一个datatable。

private DataTable ExcelToDataTable(string fileName,object sheet_identifi = null,int startRow =0)

fileName指的是文件路径；

sheet_identifi指代sheet的标识，可以是sheet的名称“Sheet1”，也可以是sheet所在的位置第i个（i从0开始计数），可以省略，省略后默认读取第一个sheet页；

startRow表示从第几行开始读（从0开始计数），可以省略，默认从第0行开始读取到最后；

以上只是一个方法，还没有重载，以后会按照大家的需求重载些方法。

4.3 DataTable导入Excel

方法如下：

1. private static void DataTableToExcel(DataTable dt, string filePath,string sheetName=\"Sheet1\")

2. private static void DataTableToExcel(DataTable[] dt, string filePath, string[] sheetName = null)

该方法有+1重载，其中方法1是导入一个datatable到一个sheet页，方法2是导入多个datatable到多个sheet页（要求两者数组顺序一一对应）

编号1方法 dt：需要导入到Excel中的DataTable；

filePath：Excel的存储地址（@”d:\\123.xls”）；

sheetName：Excel中新建的sheet名称，可忽略，默认为“Sheet1”；

编号2方法 dt[]:datatable数组；

filePath：Excel的存储地址（@”d:\\123.xls”）；

sheetName：sheet名称数组，顺序与dt[]对应，可忽略，默认名称为“Sheet1”，“Sheet2”，“Sheet3”，“Sheet4”。。。

5 NPOI中的小技巧

5.1 读取、创建、保存workbook

在之前的章节中介绍过Workbook分为两种版本，03版和07版，分别用

HSSFWorkbook()和XSSFWorkbook()创建。同时读取工作簿和创建工作簿的方法也有着细微的不同，所以我创建了三个方法方便大家快速读取、创建、保存workbook。

1. private static IWorkbook CreateWorkBook (string excelMode)

该方法返回的是设置好版本的IWorkbook，主要用于创建excel。

excelMode:可以写创建的EXCEL路径，也可以用“03”，“07”这种字符串来直接设

定版本。

当然你要是熟知HSSFWORK的用法话，可以直接这样写：

IWorkbook workbook = new HSSFWorkbook(); //创建03版

2. private static IWorkbook GetWorkBook (string filePath)

该方法返回的是设置好版本的IWorkbook，主要用于读取excel。

filePath：读取的EXCEL路径，方法中将自动搜索filePath路径，并创建filestream进

行EXCEL的读取。

3. private static void SaveWorkBook(string filePath, IWorkbook workbook)

执行该方法后会将修改好的workbook保存在指定路径下的EXCEL中。

filePath：excel的保存路径；

Workbook：你创建或修改后的workbook；

例子1：创建一个03版本的excel，添加内容后保存。

IWorkbook workbook;

workbook = CreateWorkBook(“03”);

//*************************

//对workbook进行添加修改

//*************************

SaveWorkBook(@”d:\\333.xls”, workbook);

例子2：打开d:\\123.xls文件并修改，最后另存为E:\\123.xls

IWorkbook workbook = GetWorkBook(@\"d:\\123.xls\");

//*************************

//对workbook进行添加修改

//*************************

SaveWorkBook(@\"E:\\123.xls\",workbook);

5.2 合并单元格

ISheet中有合并单元格方法：

AddMergedRegion(new CellRangeAddress(int firstRow,int lastRow,int firstCol,int lastCol))；

它是通过上下左右来描述CellRangeAddress的区域。直接举个例子，将A1到B3的单元格进行合并，那么该区域的边界是左边第0列，右边第2列，上面第0行，下面第1行。（这里的计数从0开始计数）

IWorkbook workbook = new HSSFWorkbook();

ISheet sheet = workbook.CreateSheet(\"Sheet1\");

// ICell cell = sheet.CreateRow(0).CreateCell(0);

//cell.SetCellValue(2);这两句话也可以放在合并单元格之后

sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));

如果合并的单元格里之前有内容，只会保存左上角单元格里的内容，这和EXCEL一致；同理，如果想对已经合并的单元格设置内容，也只能对左上角单元格添加修改内容，对其他区域内的单元格进行不了操作。

5.3 添加、获取单元格公式

5.3.1 添加公式

假使我们已经知道了某空白单元格ICell cell，现要在该单元格里添加公式，可以直接这么写。

cell.CellFormula = \"A2+A3\";

注意：字符串里没有“=”。

5.3.2 获取公式

假设我们知道A1单元格里内容是“=A2+A3”，现要提取公式。

ICell cell = sheet.GetRow(0).GetCell(0);

string tt = cell.CellFormula;

返回的是string，同样没有“=”符号。

5.4 设置，获取超链接

5.4.1 设置超链接

//设置显示文本

ICell cell = sheet.CreateRow(0).CreateCell(0);

cell.SetCellValue(“点击获得百度全家桶”);

//设置HSSFHyperlink，HyperlinkType有Url，Document，file等。同理如果是07版本，则用XSSFHyperlink创建link

HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);

link.Address = \"http://www.baidu.com\";

//给cell的Hyperlink属性赋值

cell.Hyperlink = link;

5.4.2 获取超链接

string linkAddress = cell.Hyperlink.Address;

返回的是string类型

5.4.3 移除超链接

cell.RemoveHyperlink();

5.5 设置字体和边框（大小，颜色）

IWorkbook workbook；

1.先设置字体IFont颜色大小

IFont font = workbook.CreateFont();//创建字体样式

font.Color = HSSFColor.BLUE.index;//设置字体颜色

font.Boldweight = (short)FontBoldWeight.Bold;//设置Bold属性

font.FontName = \"黑体\";//设置字体

2.再创建单元格样式ICellStyle

ICellStyle style = hssfworkbook.CreateCellStyle();//创建单元格样式

style.Alignment = HorizontalAlignment.Center;//显示居中

style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;//设置底框的线条

style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;//设置单元格背景色

style.SetFont(font);//设置单元格样式中的字体样式

3.设置单元格显示样式CellStyle

cell.CellStyle = style;//为单元格设置显示样式