常用方法 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}
");
                                }

                            }
                            else
                            {
                                //row.GetCell(j).SetCellType(CellType.String);
                                value = cell.StringCellValue;
                            }
                        }
                        NewRow[j] = value;
                    }
                    myTable.Rows.Add(NewRow);
                }
                #endregion

                ds.Tables.Add(myTable);
            }
            return ds;
        }