NPOI (自動保存的).doc

NPOI1. 三種EXCEL抽取方式介紹在對EXCEL進行數據抽取時，現在主流的技術方法是：oledb,com組件，NPOI1.采用oledb讀取excel文件，用這種方法讀取Excel速度還是非常的快的，但這種方式讀取數據的時候不太靈活，不過可以在 DataTable 中對數據進行一些刪減修改。這種方式將Excel作為一個數據源，直接用Sql語句獲取數據了。所以讀取之前要知道此次要讀取的Sheet（當然也可以用序號，類似dt.Row00。這樣倒是不需要知道Sheet）優(yōu)點：讀取方式簡單、讀取速度快，可在不關閉當前Excel的情況下對該EXCEL進行sql獲取數據的操作。缺點：除了讀取過程不太靈活之外，這種讀取方式還有個弊端就是，當Excel數據量很大時。會非常占用內存，當內存不夠時會拋出內存溢出的異常。個人意見：oledb還是比較好用的，內存不夠的情況至今也沒見過。2. Com組件的方式讀取Excel這種方式需要先引用Microsoft.Office.Interop.Excel 。首選說下這種方式的優(yōu)缺點優(yōu)點：可以非常靈活的讀取Excel中的數據缺點：如果是Web站點部署在IIS上時，還需要服務器機子已安裝了Excel，有時候還需要為配置IIS權限。最重要的一點因為是基于單元格方式讀取的，所以數據很慢。個人意見：不建議用這種方法，基于單元格的讀取方式很顯然是落后低效的。3. NPOI方式讀取Excel，NPOI是一組開源的組件，類似Java的 POI。包括：NPOI、NPOI.HPSF、NPOI.HSSF、NPOI.HSSF.UserModel、NPOI.POIFS、NPOI.Util，下載的時候別只下一個噢優(yōu)點：讀取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的操作主要分為兩類：讀取和創(chuàng)建。一個是對已有的EXCEL進行操作，一個是創(chuàng)建新的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 創(chuàng)建EXCEL1.2.1 工作薄和工作表/創(chuàng)建一個新的工作薄IWorkbook workbook = new HSSFWorkbook(); /03版IWorkbook workbook = new XSSFWorkbook(); /07版/創(chuàng)建一個名稱為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ū)別，前者對應讀取EXCEL，后者對應創(chuàng)建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等。但如果想要將單元格內容設置成超鏈接，或是輸入公式，改變字體，則需要了解后面幾章節(jié)中的內容。4 EXCEL與DataTable之間的轉換EXCEL與datatable之間的交互一直是EXCEL二次開發(fā)中永恒的話題，首先我們要了解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有列標題列屬性這一概念，如果你沒有設置列標題和列屬性，則無法創(chuàng)建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 讀取、創(chuàng)建、保存workbook在之前的章節(jié)中介紹過Workbook分為兩種版本，03版和07版，分別用HSSFWorkbook()和XSSFWorkbook()創(chuàng)建。同時讀取工作簿和創(chuàng)建工作簿的方法也有著細微的不同，所以我創(chuàng)建了三個方法方便大家快速讀取、創(chuàng)建、保存workbook。1. private static IWorkbook CreateWorkBook (string excelMode)該方法返回的是設置好版本的IWorkbook，主要用于創(chuàng)建excel。excelMode:可以寫創(chuàng)建的EXCEL路徑，也可以用“03”，“07”這種字符串來直接設定版本。當然你要是熟知HSSFWORK的用法話，可以直接這樣寫：IWorkbook workbook = new HSSFWorkbook(); /創(chuàng)建03版2. private static IWorkbook GetWorkBook (string filePath)該方法返回的是設置好版本的IWorkbook，主要用于讀取excel。filePath：讀取的EXCEL路徑，方法中將自動搜索filePath路徑，并創(chuàng)建filestream進行EXCEL的讀取。3. private static void SaveWorkBook(string filePath, IWorkbook workbook)執(zhí)行該方法后會將修改好的workbook保存在指定路徑下的EXCEL中。filePath：excel的保存路徑；Workbook：你創(chuàng)建或修改后的workbook；例子1：創(chuàng)建一個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的區(qū)域。直接舉個例子，將A1到B3的單元格進行合并，那么該區(qū)域的邊界是左邊第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一致；同理，如果想對已經合并的單元格設置內容，也只能對左上角單元格添加修改內容，對其他區(qū)域內的單元格進行不了操作。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