首页 > 代码库 > NPOI 导入excel
NPOI 导入excel
应用NUget 安装NPOI 很方便
读出excel的主要语句是
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
当excel以xlsx结尾用 XSSFWorkbook 否则用HSSFWorkbook
public DataTable ImportExcelFile(string filePath) { XSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new XSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); //using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0)) //{ DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0);//第一行为标题行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } return table; //} } /// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> private static string GetCellValue(ICell cell) { if (cell == null) return string.Empty; switch (cell.CellType) { case CellType.Blank: return string.Empty; case CellType.Boolean: return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: case CellType.Unknown: default: return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number case CellType.String: return cell.StringCellValue; case CellType.Formula: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } }
NPOI 导入excel
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。