NPOI (自動(dòng)保存的).doc

NPOI1. 三種EXCEL抽取方式介紹在對(duì)EXCEL進(jìn)行數(shù)據(jù)抽取時(shí)，現(xiàn)在主流的技術(shù)方法是：oledb,com組件，NPOI1.采用oledb讀取excel文件，用這種方法讀取Excel速度還是非常的快的，但這種方式讀取數(shù)據(jù)的時(shí)候不太靈活，不過(guò)可以在 DataTable 中對(duì)數(shù)據(jù)進(jìn)行一些刪減修改。這種方式將Excel作為一個(gè)數(shù)據(jù)源，直接用Sql語(yǔ)句獲取數(shù)據(jù)了。所以讀取之前要知道此次要讀取的Sheet（當(dāng)然也可以用序號(hào)，類似dt.Row00。這樣倒是不需要知道Sheet）優(yōu)點(diǎn)：讀取方式簡(jiǎn)單、讀取速度快，可在不關(guān)閉當(dāng)前Excel的情況下對(duì)該EXCEL進(jìn)行sql獲取數(shù)據(jù)的操作。缺點(diǎn)：除了讀取過(guò)程不太靈活之外，這種讀取方式還有個(gè)弊端就是，當(dāng)Excel數(shù)據(jù)量很大時(shí)。會(huì)非常占用內(nèi)存，當(dāng)內(nèi)存不夠時(shí)會(huì)拋出內(nèi)存溢出的異常。個(gè)人意見(jiàn)：oledb還是比較好用的，內(nèi)存不夠的情況至今也沒(méi)見(jiàn)過(guò)。2. Com組件的方式讀取Excel這種方式需要先引用Microsoft.Office.Interop.Excel 。首選說(shuō)下這種方式的優(yōu)缺點(diǎn)優(yōu)點(diǎn)：可以非常靈活的讀取Excel中的數(shù)據(jù)缺點(diǎn)：如果是Web站點(diǎn)部署在IIS上時(shí)，還需要服務(wù)器機(jī)子已安裝了Excel，有時(shí)候還需要為配置IIS權(quán)限。最重要的一點(diǎn)因?yàn)槭腔趩卧穹绞阶x取的，所以數(shù)據(jù)很慢。個(gè)人意見(jiàn)：不建議用這種方法，基于單元格的讀取方式很顯然是落后低效的。3. NPOI方式讀取Excel，NPOI是一組開(kāi)源的組件，類似Java的 POI。包括：NPOI、NPOI.HPSF、NPOI.HSSF、NPOI.HSSF.UserModel、NPOI.POIFS、NPOI.Util，下載的時(shí)候別只下一個(gè)噢優(yōu)點(diǎn)：讀取Excel速度較快，讀取方式操作靈活性，03版本用HSSFWorkbook，07版本用XSSFWorkbook?？山鉀Q幾乎所有的EXCEL問(wèn)題，如單元格合并，插入公式，單元格顏色等等。缺點(diǎn)：不能對(duì)已打開(kāi)的當(dāng)前EXCEL文件進(jìn)行操作。需要導(dǎo)入多個(gè)dll，而且要注意dll所對(duì)應(yīng)的net framework版本是2.0還是4.0。功能雖然十分強(qiáng)大，但由于代碼不開(kāi)源導(dǎo)致dll里面的函數(shù)方法需要自己琢磨或者上網(wǎng)查詢。2. 關(guān)于NPOI的dll和using主要包含以下4個(gè)dll：NPOI.dll,NPOI.OOXML.DLL,NPOI.OpenXml4Net.dll,NPOI.OpenXmlFormats.dll值得注意的是以上dll分為.net framework2.0版以及.net framework4.0版，引用的時(shí)候需要注意下。在程序中引用以上四個(gè)dll后，需在程序開(kāi)頭寫上以下代碼（方便使用）：using NPOI.SS.UserModel;using NPOI.SS.Util;using NPOI.XSSF.UserModel;using NPOI.HSSF.UserModel;其中excel03版本用的是HSSFWorkbook，excel07版本用的是XSSFWorkbook3. NPOI基本操作 在利用NPOI對(duì)EXCEL進(jìn)行基本操作前，需要熟悉以下幾個(gè)概念：整個(gè)Excel表格叫做工作表：WorkBook（工作?。慕许?yè)（工作表）：Sheet；行：Row；列：Column；單元格Cell。Cell是excel中最基本的單位，當(dāng)然也可以用range來(lái)選擇多個(gè)單元格范圍。知道上面這幾個(gè)概念后我們就可以進(jìn)行下一步操作了。首先NPOI對(duì)EXCEL的操作主要分為兩類：讀取和創(chuàng)建。一個(gè)是對(duì)已有的EXCEL進(jìn)行操作，一個(gè)是創(chuàng)建新的EXCEL，這兩種情況對(duì)應(yīng)的代碼有著明顯的不同。注意：這里貼出來(lái)的是源代碼，方便大家理解，在第5章中我對(duì)下列代碼進(jìn)行了封裝。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個(gè)表(從1開(kāi)始計(jì)數(shù)，排列并不一定符合EXCEL中顯示的SHEET順序)，及第i個(gè)表的名稱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);注意：可以選擇某一行，或者某個(gè)單元格，但并不能選擇某一列。單元格cell下有很多方法，如cell.CellFormula可以寫入公式，cell.CellType可以寫入單元格類型等等。1.2 創(chuàng)建EXCEL1.2.1 工作薄和工作表/創(chuàng)建一個(gè)新的工作薄IWorkbook workbook = new HSSFWorkbook(); /03版IWorkbook workbook = new XSSFWorkbook(); /07版/創(chuàng)建一個(gè)名稱為mySheet的表 ISheet sheet = workbook.CreateSheet(mySheet);/保存工作簿using (FileStream fs = new FileStream(d:test.xls, FileMode.Create, FileAccess.Write) workbook.Write(fs); 1.2.2 行列，單元格/創(chuàng)建第i行IRow row = sheet.CreateRow(i-1);/獲取新創(chuàng)建sheet中的第i行，第j列的單元格 ICell cell = sheet.CreateRow(i-1).CreateCell(j-1); String cellValue = 一定要注意GetRow(i)和CreateRow(i)的區(qū)別，前者對(duì)應(yīng)讀取EXCEL，后者對(duì)應(yīng)創(chuàng)建EXCEL。1.3 讀寫單元格1.3.1 讀取單元格中內(nèi)容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;通過(guò)CellType來(lái)返回單元格中不同數(shù)值類型的內(nèi)容，該方法命名為private static object GetCellValueType(ICell cell)但有個(gè)問(wèn)題，CellType中并有沒(méi)datetime屬性，如果單元格中數(shù)值類型是datetime，可以用cell.DateCellValue返回。 1.3.2 寫入單元格 cell.SetValue()可以滿足string, double，bool，Datetime, IRichTextString這5個(gè)數(shù)值類型的寫入。（不包括Formula）如cell.SetValue(3)表示將double類型的數(shù)字3寫入單元格中。如果cell.CellType = CellType.Formula,則可以按照下面這種方式寫入。Cell.SetCellFormula(string formula);1.4 總結(jié)只要知道以上EXCEL的基本操作方法，就可以完成些最基本的操作，如修改單元格內(nèi)容，找到名稱為“037”的sheet等。但如果想要將單元格內(nèi)容設(shè)置成超鏈接，或是輸入公式，改變字體，則需要了解后面幾章節(jié)中的內(nèi)容。4 EXCEL與DataTable之間的轉(zhuǎn)換EXCEL與datatable之間的交互一直是EXCEL二次開(kāi)發(fā)中永恒的話題，首先我們要了解EXCEL與datatable有何不同。4.1 兩者的構(gòu)成4.1.1 EXCEL的構(gòu)成一個(gè)EXCEL是由一個(gè)工作簿workbook構(gòu)成，一個(gè)workbook中可以有好幾個(gè)sheet頁(yè)，每個(gè)sheet頁(yè)中有無(wú)數(shù)個(gè)單元格cell，每個(gè)單元格都是獨(dú)立且唯一的。所謂獨(dú)立指的是每個(gè)cell可以有各自不同的數(shù)值類型（如string，int，datetime，formula等），不受行列的影響，準(zhǔn)確來(lái)說(shuō)EXCEL也沒(méi)有列標(biāo)題列屬性這一概念，它只是由無(wú)數(shù)個(gè)cell組成的表格。4.1.2 DataTable的構(gòu)成Datatable是由行列組成，行列的交叉為dt.Rowsij，即EXCEL中的單元格。但這之間有細(xì)微的不同，Datatable有列標(biāo)題列屬性這一概念，如果你沒(méi)有設(shè)置列標(biāo)題和列屬性，則無(wú)法創(chuàng)建Datatable或是給dt.Rowsij賦值。列標(biāo)題按如下代碼設(shè)置DataColumn column = new DataColumn(colName); Column.DataType = typeof(S); dt.Columns.Add(column);列標(biāo)題名稱colName必須為string，該列的數(shù)據(jù)屬性DataType可以人為設(shè)置，如int，string，double等等（默認(rèn)為string），但一旦設(shè)置好了DataType=int類型，就不能在該列下填寫非int類型的數(shù)據(jù)。這點(diǎn)和EXCEL不一樣，EXCEL沒(méi)有列的限制條件，單元格是獨(dú)立的。datatable設(shè)置好列標(biāo)題后，得按整行為單位來(lái)添加數(shù)據(jù)。DataRow dr = dt.NewRow();drj=?(j=0,1,2.)dt.Rows.add(dr);這樣才算新建好了一個(gè)datatable。4.2 EXCEL導(dǎo)出DataTable下面的方法返回的是一個(gè)datatable。private DataTable ExcelToDataTable(string fileName,object sheet_identifi = null,int startRow =0)fileName指的是文件路徑；sheet_identifi指代sheet的標(biāo)識(shí)，可以是sheet的名稱“Sheet1”，也可以是sheet所在的位置第i個(gè)（i從0開(kāi)始計(jì)數(shù)），可以省略，省略后默認(rèn)讀取第一個(gè)sheet頁(yè)；startRow表示從第幾行開(kāi)始讀（從0開(kāi)始計(jì)數(shù)），可以省略，默認(rèn)從第0行開(kāi)始讀取到最后；以上只是一個(gè)方法，還沒(méi)有重載，以后會(huì)按照大家的需求重載些方法。4.3 DataTable導(dǎo)入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是導(dǎo)入一個(gè)datatable到一個(gè)sheet頁(yè)，方法2是導(dǎo)入多個(gè)datatable到多個(gè)sheet頁(yè)（要求兩者數(shù)組順序一一對(duì)應(yīng)）編號(hào)1方法dt：需要導(dǎo)入到Excel中的DataTable；filePath：Excel的存儲(chǔ)地址（”d:123.xls”）；sheetName：Excel中新建的sheet名稱，可忽略，默認(rèn)為“Sheet1”；編號(hào)2方法dt:datatable數(shù)組；filePath：Excel的存儲(chǔ)地址（”d:123.xls”）；sheetName：sheet名稱數(shù)組，順序與dt對(duì)應(yīng)，可忽略，默認(rèn)名稱為“Sheet1”，“Sheet2”，“Sheet3”，“Sheet4”。5 NPOI中的小技巧5.1 讀取、創(chuàng)建、保存workbook在之前的章節(jié)中介紹過(guò)Workbook分為兩種版本，03版和07版，分別用HSSFWorkbook()和XSSFWorkbook()創(chuàng)建。同時(shí)讀取工作簿和創(chuàng)建工作簿的方法也有著細(xì)微的不同，所以我創(chuàng)建了三個(gè)方法方便大家快速讀取、創(chuàng)建、保存workbook。1. private static IWorkbook CreateWorkBook (string excelMode)該方法返回的是設(shè)置好版本的IWorkbook，主要用于創(chuàng)建excel。excelMode:可以寫創(chuàng)建的EXCEL路徑，也可以用“03”，“07”這種字符串來(lái)直接設(shè)定版本。當(dāng)然你要是熟知HSSFWORK的用法話，可以直接這樣寫：IWorkbook workbook = new HSSFWorkbook(); /創(chuàng)建03版2. private static IWorkbook GetWorkBook (string filePath)該方法返回的是設(shè)置好版本的IWorkbook，主要用于讀取excel。filePath：讀取的EXCEL路徑，方法中將自動(dòng)搜索filePath路徑，并創(chuàng)建filestream進(jìn)行EXCEL的讀取。3. private static void SaveWorkBook(string filePath, IWorkbook workbook)執(zhí)行該方法后會(huì)將修改好的workbook保存在指定路徑下的EXCEL中。filePath：excel的保存路徑；Workbook：你創(chuàng)建或修改后的workbook；例子1：創(chuàng)建一個(gè)03版本的excel，添加內(nèi)容后保存。IWorkbook workbook; workbook = CreateWorkBook(“03”); /* /對(duì)workbook進(jìn)行添加修改 /* SaveWorkBook(”d:333.xls”, workbook);例子2：打開(kāi)d:123.xls文件并修改，最后另存為E:123.xlsIWorkbook workbook = GetWorkBook(d:123.xls);/* /對(duì)workbook進(jìn)行添加修改 /* SaveWorkBook(E:123.xls,workbook);5.2 合并單元格ISheet中有合并單元格方法：AddMergedRegion(new CellRangeAddress(int firstRow,int lastRow,int firstCol,int lastCol)； 它是通過(guò)上下左右來(lái)描述CellRangeAddress的區(qū)域。直接舉個(gè)例子，將A1到B3的單元格進(jìn)行合并，那么該區(qū)域的邊界是左邊第0列，右邊第2列，上面第0行，下面第1行。（這里的計(jì)數(shù)從0開(kāi)始計(jì)數(shù)）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);如果合并的單元格里之前有內(nèi)容，只會(huì)保存左上角單元格里的內(nèi)容，這和EXCEL一致；同理，如果想對(duì)已經(jīng)合并的單元格設(shè)置內(nèi)容，也只能對(duì)左上角單元格添加修改內(nèi)容，對(duì)其他區(qū)域內(nèi)的單元格進(jìn)行不了操作。5.3 添加、獲取單元格公式5.3.1 添加公式假使我們已經(jīng)知道了某空白單元格ICell cell，現(xiàn)要在該單元格里添加公式，可以直接這么寫。cell.CellFormula = A2+A3;注意：字符串里沒(méi)有“=”。5.3.2 獲取公式假設(shè)我們知道A1單元格里內(nèi)容是“=A2+A3”，現(xiàn)要提取公式。ICell cell = sheet.GetRow(0).GetCell(0);string tt = cell.CellFormula;返回的是string，同樣沒(méi)有“=”符號(hào)。5.4 設(shè)置，獲取超鏈接5.4.1 設(shè)置超鏈接/設(shè)置顯示文本ICell cell = sheet.CreateRow(0).CreateCell(0); cell.SetCellValue(“點(diǎn)擊獲得百度全家桶”); /設(shè)置HSSFHyperlink，HyperlinkType有Url，Document，file等。同理如果是07版本，則用XSSFHyperl