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;
        }