NPOI read Excel help class, supports xls and xlsx, implements Formula Parsing, empty row processing, npoixlsx

NPOI read Excel help class, supports xls and xlsx, implements Formula Parsing, empty row processing, npoixlsx
NPOI reads Excel (2003 or 2010) and returns DataTable. Supports Formula Parsing and empty line processing.

1 /// <summary> Read excel 2 /// default first row header 3 /// </summary> 4 /// <param name = "strFileName"> excel document absolute path </param> 5 // <param name = "rowIndex"> content row offset, the first row header, where the content row starts from the second row is 1 </param> 6 /// <returns> </returns> 7 public static DataTable Import (string strFileName, int rowIndex) 8 {9 DataTable dt = new DataTable (); 10 11 IWorkbook hssfworkbook; 12 using (FileStream file = new FileStream (strFileName, FileMode. open, FileAccess. read) 13 {14 hssfworkbook = WorkbookFactory. create (file); 15} 16 ISheet sheet = hssfworkbook. getSheetAt (0); 17 18 IRow headRow = sheet. getRow (0); 19 if (headRow! = Null) 20 {21 int colCount = headRow. lastCellNum; 22 for (int I = 0; I <colCount; I ++) 23 {24 dt. columns. add ("COL _" + I); 25} 26} 27 28 for (int I = (sheet. firstRowNum + rowIndex); I <= sheet. lastRowNum; I ++) 29 {30 IRow row = sheet. getRow (I); 31 bool emptyRow = true; 32 object [] itemArray = null; 33 34 if (row! = Null) 35 {36 itemArray = new object [row. lastCellNum]; 37 38 for (int j = row. firstCellNum; j <row. lastCellNum; j ++) 39 {40 41 if (row. getCell (j )! = Null) 42 {43 44 switch (row. getCell (j ). cellType) 45 {46 case CellType. NUMERIC: 47 if (HSSFDateUtil. isCellDateFormatted (row. getCell (j) // Date type 48 {49 itemArray [j] = row. getCell (j ). dateCellValue. toString ("yyyy-MM-dd"); 50} 51 else // other numeric types 52 {53 itemArray [j] = row. getCell (j ). numericCellValue; 54} 55 break; 56 case CellType. BLANK: 57 itemArray [j] = string. empty; 58 break; 59 case CellType. FORM ULA: 60 if (Path. getExtension (strFileName ). toLower (). trim () = ". xlsx ") 61 {62 XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator (hssfworkbook); 63 if (eva. evaluate (row. getCell (j )). cellType = CellType. NUMERIC) 64 {65 itemArray [j] = eva. evaluate (row. getCell (j )). numberValue; 66} 67 else 68 {69 itemArray [j] = eva. evaluate (row. getCell (j )). stringValue; 70} 71} 72 else 73 {74 HSSFFormulaEvaluat Or eva = new HSSFFormulaEvaluator (hssfworkbook); 75 if (eva. evaluate (row. getCell (j )). cellType = CellType. NUMERIC) 76 {77 itemArray [j] = eva. evaluate (row. getCell (j )). numberValue; 78} 79 else 80 {81 itemArray [j] = eva. evaluate (row. getCell (j )). stringValue; 82} 83} 84 break; 85 default: 86 itemArray [j] = row. getCell (j ). stringCellValue; 87 break; 88 89} 90 91 if (itemArray [j]! = Null &&! String. isNullOrEmpty (itemArray [j]. toString (). trim () 92 {93 emptyRow = false; 94} 95} 96} 97} 98 99 // Add non-empty data rows to DataTable100 if (! EmptyRow) 101 {102 dt. Rows. Add (itemArray); 103} 104 return dt; 105}