NPOI把EXCEL转成DataTable(去空列和不去空列)
直接复制即可用,记得去Nuget引用 NPOI;
一、excel里有些列是空的,空值也放到DataTable里
/// <summary>/// Excel导入成Datable /// </summary> /// <param name="file">导入路径(包含文件名与扩展名)</param> /// <returns></returns> public static DataTable ExcelToTable(string file) { try { DataTable dt = new DataTable(); IWorkbook workbook; string fileExt = Path.GetExtension(file).ToLower(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式 if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; } if (workbook == null) { return null; } ISheet sheet = workbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueType(file, workbook, header.GetCell(i)); if (obj == null || obj.ToString() == string.Empty) { //xxc20220613遇空列,停止读取列 break; //dt.Columns.Add(new DataColumn("Columns" + i.ToString())); } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { try { dr[j] = GetValueType(file, workbook, sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } catch (Exception ee) { continue; } } if (hasValue) { dt.Rows.Add(dr); } else { //xxc20220613遇空行,停止读取行 break; } } } return dt; } catch (Exception e) { return null; } } /// <summary> /// 获取单元格类型 /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueType(string strFileName, IWorkbook workbook, ICell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: object rv = null; if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx") { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); if (eva.Evaluate(cell).CellType == CellType.Numeric) { rv = eva.Evaluate(cell).NumberValue; } else { rv = eva.Evaluate(cell).StringValue; } } else { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook); if (eva.Evaluate(cell).CellType == CellType.Numeric) { rv = eva.Evaluate(cell).NumberValue; } else { rv = eva.Evaluate(cell).StringValue; } } return rv; default: return "=" + cell.CellFormula; } }
二、去掉为空的列
public static DataTable ReadExcel(string excelPath) { DataTable dtTable = new DataTable(); List<string> rowList = new List<string>(); ISheet sheet; try { using (var stream = new FileStream(excelPath, FileMode.Open)) { stream.Position = 0; XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream); sheet = xssWorkbook.GetSheetAt(0); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue; { dtTable.Columns.Add(cell.ToString()); } } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null) continue; if (row.Cells.All(d => d.CellType == CellType.Blank)) continue; for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString())) { rowList.Add(GetValueByCellStyle(row.GetCell(j), row.GetCell(j).CellType)); } } } if (rowList.Count > 0) dtTable.Rows.Add(rowList.ToArray()); rowList.Clear(); } } } catch (Exception ex) { Console.WriteLine($"ReadExcel: {ex.Message}"); } return dtTable; } /// <summary> /// 根据单元格的类型获取单元格的值 /// </summary> /// <param name="rowCell"></param> /// <param name="type"></param> /// <returns></returns> public static string GetValueByCellStyle(ICell rowCell, CellType? type) { string value = string.Empty; switch (type) { case CellType.String: value = rowCell.StringCellValue; break; case CellType.Numeric: if (DateUtil.IsCellInternalDateFormatted(rowCell)) { value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); } else if (DateUtil.IsCellDateFormatted(rowCell)) { value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); } //有些情况,时间搓?数字格式化显示为时间,不属于上面两种时间格式 else if (rowCell.CellStyle.GetDataFormatString() == null) { value = DateTime.FromOADate(rowCell.NumericCellValue).ToString(); } else { value = rowCell.NumericCellValue.ToString(); } break; case CellType.Boolean: value = rowCell.BooleanCellValue.ToString(); break; case CellType.Error: value = ErrorEval.GetText(rowCell.ErrorCellValue); break; case CellType.Formula: // TODO: 是否存在 嵌套 公式类型 value = GetValueByCellStyle(rowCell, rowCell?.CachedFormulaResultType); break; } return value; }