Excel导入:
if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower()) { HSSFWorkbook hssfworkbook; try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { //SET EVERY COLUMN NAME HSSFCell cell = (HSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } while (rows.MoveNext()) { IRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); if (row.RowNum == 0) continue;//The firt row is title,no need import for (int i = 0; i < row.LastCellNum; i++) { if (i >= dt.Columns.Count) { break; } ICell cell = row.GetCell(i); if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true)) { break; } if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } } else {//.xlsx XSSFWorkbook hssfworkbook; try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); } } catch (Exception e) { throw e; } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { XSSFCell cell = (XSSFCell)headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); if (row.RowNum == 0) continue; for (int i = 0; i < row.LastCellNum; i++) { if (i >= dt.Columns.Count) { break; } ICell cell = row.GetCell(i); if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true)) { break; } if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } } return dt; }
彭亚欧个人博客原创文章,转载请注明出处
文章关键词:.net Excel导入,Exvel导出插件,NPOI
文章固定链接:https://www.pengyaou.com/homeart/MTg3.html