常用方法 Excel转换为DataSet
注意一下Excel格式,我平时导入的是xlsx
/// <summary> /// Excel 转换为 DataSet /// </summary> /// <param name="isFirstRowColumn">首行作为列名</param> /// <param name="fileName"></param> /// <param name="IsSupportFormula">是否支持公式</param> /// <returns></returns> public static DataSet ExcelToDataSet(bool isFirstRowColumn, string fileName, bool IsSupportFormula = true) { IWorkbook workbook = null; DataSet ds = new DataSet(); var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); #region 初始化Excel if (fileName.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(fs); } else { throw new Exception("格式错误!"); } #endregion for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++) { DataTable myTable = new DataTable(); ISheet sheet = null; int sheetNum = sheetIndex; sheet = workbook.GetSheetAt(sheetNum); #region 工作表不能为空 if (sheet == null) { string str = ""; for (int i = 0; i < workbook.NumberOfSheets; i++) { str += workbook.GetSheetAt(i).SheetName + ","; } str = workbook.NumberOfSheets + str; throw new Exception($"sheet不能为空!参数:{sheetNum} 工作簿信息:{str}"); } #endregion #region Excel最大列数 int MaxColumnNum = 0; for (int i = 0; i < sheet.LastRowNum; i++) { var row = sheet.GetRow(i); if (row == null) { continue; } if (row.LastCellNum > MaxColumnNum) { MaxColumnNum = row.LastCellNum; } } #endregion //Excel行数 int MaxRowNum = sheet.LastRowNum; #region table新增列 for (int i = 0; i < MaxColumnNum; ++i) { //首行为列 if (isFirstRowColumn) { bool addEmptyCell = true;//是否添加空列 ICell cell = sheet.GetRow(0).GetCell(i); if (cell != null) { //table列赋值 string cellValue = "";//列名 if (cell.CellType == CellType.Numeric) { cellValue = cell.NumericCellValue.ToString(); } else { cellValue = cell.StringCellValue; } if (!string.IsNullOrWhiteSpace(cellValue)) { //列数据为Excel的数据 addEmptyCell = false; if (myTable.Columns.Contains(cellValue)) { throw new Exception($"已包含列:{cellValue},工作表:{sheet.SheetName}"); } myTable.Columns.Add(new DataColumn(cellValue)); } } if (addEmptyCell) { myTable.Columns.Add(new DataColumn(""));//列数据为空 } } else { myTable.Columns.Add(new DataColumn(i + "")); } } #endregion //起始行 int startRow = 0; if (isFirstRowColumn) { startRow = 1; } #region DataTable赋值 for (int i = startRow; i <= MaxRowNum; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; DataRow NewRow = myTable.NewRow(); for (int j = row.FirstCellNum; j < row.LastCellNum; ++j) { ICell cell = row.GetCell(j); string value = ""; if (cell != null) { //table行赋值 if (cell.CellType == CellType.Numeric) { value = cell.NumericCellValue.ToString(); //if ((j == 0) && ((i == 6) || (i == 12))) //{ // //特殊的几个单元格 转换为 日期格式 // value = ToDateTimeValue(cell.NumericCellValue.ToString()); //} } else if (cell.CellType == CellType.Formula) { if (IsSupportFormula) { try { #region 公式计算 if (fileName.IndexOf(".xlsx") > 0) { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); value = cell.ToString(); } else if (fileName.IndexOf(".xls") > 0) { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); value = cell.ToString(); } #endregion } catch { //日期 if (DateUtil.IsCellDateFormatted(cell)) { value = cell.DateCellValue.ToString("yyyy-MM-dd"); } else { value = cell.NumericCellValue.ToString(); } } } else { throw new Exception($"Excel含有公式 fileName:{fileName} sheetIndex:{sheetIndex + 1} rowIndex:{i + 1} colIndex:{j + 1}\r\n"); } } else { //row.GetCell(j).SetCellType(CellType.String); value = cell.StringCellValue; } } NewRow[j] = value; } myTable.Rows.Add(NewRow); } #endregion ds.Tables.Add(myTable); } return ds; }