NPOI (自动保存的).doc

NPOI1. 三种EXCEL抽取方式介绍在对EXCEL进行数据抽取时，现在主流的技术方法是：oledb,com组件，NPOI1.采用oledb读取excel文件，用这种方法读取Excel速度还是非常的快的，但这种方式读取数据的时候不太灵活，不过可以在 DataTable 中对数据进行一些删减修改。这种方式将Excel作为一个数据源，直接用Sql语句获取数据了。所以读取之前要知道此次要读取的Sheet（当然也可以用序号，类似dt.Row00。这样倒是不需要知道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版本用的是XSSFWorkbook3. NPOI基本操作 在利用NPOI对EXCEL进行基本操作前，需要熟悉以下几个概念：整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；列：Column；单元格Cell。Cell是excel中最基本的单位，当然也可以用range来选择多个单元格范围。知道上面这几个概念后我们就可以进行下一步操作了。首先NPOI对EXCEL的操作主要分为两类：读取和创建。一个是对已有的EXCEL进行操作，一个是创建新的EXCEL，这两种情况对应的代码有着明显的不同。注意：这里贴出来的是源代码，方便大家理解，在第5章中我对下列代码进行了封装。1.1 读取EXCEL1.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:test.xls, FileMode.Create, 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 创建EXCEL1.2.1 工作薄和工作表/创建一个新的工作薄IWorkbook workbook = new HSSFWorkbook(); /03版IWorkbook workbook = new XSSFWorkbook(); /07版/创建一个名称为mySheet的表 ISheet sheet = workbook.CreateSheet(mySheet);/保存工作簿using (FileStream fs = new FileStream(d:test.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.Rowsij，即EXCEL中的单元格。但这之间有细微的不同，Datatable有列标题列属性这一概念，如果你没有设置列标题和列属性，则无法创建Datatable或是给dt.Rowsij赋值。列标题按如下代码设置DataColumn column = new DataColumn(colName); Column.DataType = typeof(S); dt.Columns.Add(column);列标题名称colName必须为string，该列的数据属性DataType可以人为设置，如int，string，double等等（默认为string），但一旦设置好了DataType=int类型，就不能在该列下填写非int类型的数据。这点和EXCEL不一样，EXCEL没有列的限制条件，单元格是独立的。datatable设置好列标题后，得按整行为单位来添加数据。DataRow dr = dt.NewRow();drj=?(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.xlsIWorkbook 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版本，则用XSSFHyperl