Use NPOI to convert Excel Data to DataTable and npoidatatable

Use NPOI to convert Excel Data to DataTable and npoidatatable

1 /// <summary> 2 /// convert the Excel Data to able 3 /// </summary> 4 /// <param name = "xlsxFile"> </param> 5 // <returns> </returns> 6 private List <DataTable> GetDataTablesFromExcel (string xlsxFile) 7 {8 if (! File. exists (xlsxFile) 9 throw new FileNotFoundException ("file does not exist"); 10 List <DataTable> dataTableList = new List <DataTable> (); 11 using (FileStream fileStream = new FileStream (xlsxFile, FileMode. open, FileAccess. read) 12 {13 IWorkbook workbook =! (Path. GetExtension (xlsxFile) = ". xlsx ")? (IWorkbook) new HSSFWorkbook (Stream) fileStream): (IWorkbook) new XSSFWorkbook (Stream) fileStream); 14 for (int index = 0; index <workbook. numberOfSheets; index ++) 15 {16 DataTable able = new dataTable (); 17 ISheet sheetAt = workbook. getSheetAt (index); 18 if (sheetAt = null) 19 {20 continue; 21} 22 dataTable. tableName = sheetAt. sheetName; 23 int rowsCount = sheetAt. physicalNumberOfRows; // obtain the maximum number of rows in Excel. 24 if (rowsCount <= 1) continue; 25 // to ensure the Table layout is the same as that in Excel, the maximum number of columns in all rows should be taken (the entire Sheet needs to be traversed ). 26 // for better performance, we can manually adjust the number of columns in the 0th rows to the maximum number of columns in all rows. 27 int colsCount = sheetAt. getRow (0 ). physicalNumberOfCells; 28 29 // The first row (title) of the table is Columns30 for (int I = 0; I <colsCount; I ++) 31 {32 var cellValue = sheetAt. getRow (0 ). getCell (I); 33 dataTable. columns. add (cellValue ?. ToString (); 34} 35 36 // fetch data from the second row. The first row defaults to 37 for (int x = 1; x <rowsCount; x ++) 38 {39 DataRow dr = dataTable. newRow (); 40 for (int y = 0; y <colsCount; y ++) 41 {42 var cellValue = sheetAt. getRow (x ). getCell (y); 43 dr [y] = cellValue ?. ToString (); 44} 45 dataTable. Rows. Add (dr); 46} 47 dataTableList. Add (dataTable); 48} 49} 50 return dataTableList; 51}

View Code