读取excel

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="headerIndex">从0开始计算</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string filePath, string sheetName, int startRow = 0, int headerIndex = 0)
        {
            Stream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            //IWorkbook workbook = new HSSFWorkbook(fs);
            //IWorkbook workbook = new XSSFWorkbook(fs);
            IWorkbook workbook = WorkbookFactory.Create(fs);
            ISheet sheet = default(ISheet);
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }

            DataTable dt = new DataTable();
            if (sheet != null)
            {
                dt.TableName = sheet.SheetName;
                //计算Excel表区域(FirstRowNum,LastRowNum, 0, 2147483647)
                int minCellIndex = int.MaxValue;
                int maxCellIndex = int.MinValue;
                for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                {
                    IRow eRow = sheet.GetRow(i);
                    if (eRow != null)
                    {
                        if (eRow.FirstCellNum != -1) //改了高度的空行
                        {
                            ICell fc = default(ICell);
                            for (int c = 0; c <= eRow.FirstCellNum; c++)
                            {
                                fc = eRow.GetCell(c);
                                if (fc != null)
                                {
                                    if (fc.CellType != CellType.Blank)
                                    {
                                        minCellIndex = c < minCellIndex ? c : minCellIndex;
                                        break;
                                    }
                                }
                            }

                            ICell lc = default(ICell);
                            for (int c = eRow.LastCellNum; c > 0; c--)
                            {
                                lc = eRow.GetCell(c - 1);
                                if (lc != null)
                                {
                                    if (lc.CellType != CellType.Blank)
                                    {
                                        maxCellIndex = c > maxCellIndex ? c : maxCellIndex;
                                        break;
                                    }
                                }
                            }
                        }
                    }
                }
                //初始化DataTable
                if (minCellIndex != int.MaxValue && maxCellIndex != int.MinValue)
                {
                    int colCount = maxCellIndex - minCellIndex;
                    for (int i = 0; i < colCount; i++)
                    {
                        DataColumn dColumn = default(DataColumn);

                        IRow headerRow = sheet.GetRow(headerIndex);
                        if (headerRow != null)
                        {
                            ICell eCell = headerRow.GetCell(minCellIndex + i);
                            if (eCell != null)
                            {
                                string cellVal = GetCellValue(eCell);
                                cellVal = string.IsNullOrEmpty(cellVal) ? i.ToString() : string.Concat(cellVal, "_", i.ToString());
                                if (!string.IsNullOrEmpty(cellVal))
                                {
                                    dColumn = new DataColumn(cellVal);
                                }
                            }
                        }

                        dColumn = dColumn ?? new DataColumn();
                        dt.Columns.Add(dColumn);
                    }
                }

                //填充值
                if (dt.Columns.Count > 0)
                {
                    for (int r = startRow; r <= sheet.LastRowNum; r++)
                    {
                        IRow eRow = sheet.GetRow(r);
                        if (eRow != null)
                        {
                            DataRow dr = dt.NewRow();
                            for (int c = minCellIndex; c < maxCellIndex; c++)
                            {
                                int serialNo = c - minCellIndex;
                                ICell eCell = eRow.GetCell(c);
                                if (eCell != null)
                                {
                                    if (eCell.IsMergedCell)
                                    {
                                        dr[serialNo] = GetMergedValue(sheet, eCell.RowIndex, eCell.ColumnIndex);
                                    }
                                    else
                                    {
                                        dr[serialNo] = GetCellValue(eCell);
                                    }

                                }
                            }
                            dt.Rows.Add(dr);
                        }
                    }//for结束
                }
            }
            return dt;
        }

        public static string GetMergedValue(ISheet sheet, int rowIndex, int columnIndex)
        {
            string result = string.Empty;
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress range = sheet.GetMergedRegion(i);
                if (range.IsInRange(rowIndex, columnIndex))
                {
                    ICell eCell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                    result = GetCellValue(eCell);
                }
            }
            return result;
        }

        public static String GetCellValue(ICell eCell)
        {
            string cellVal = string.Empty;
            switch (eCell.CellType)
            {
                case CellType.Boolean:
                    cellVal = eCell.BooleanCellValue.ToString();
                    break;
                case CellType.String:
                    cellVal = eCell.StringCellValue;
                    break;
                case CellType.Numeric:
                    short format = eCell.CellStyle.DataFormat;
                    if (format == 14 || format == 31 || format == 57 || format == 58)
                    {
                        cellVal = eCell.DateCellValue.ToString();
                    }
                    else
                    {
                        cellVal = eCell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.Blank:
                    cellVal = DBNull.Value.ToString();
                    break;
                case CellType.Unknown:
                    break;
                case CellType.Error:
                    break;
                case CellType.Formula:
                    //cellVal = eCell.NumericCellValue.ToString();
                    switch (eCell.CachedFormulaResultType)
                    {
                        case CellType.Error:
                            //cellVal = ErrorEval.GetText(eCell.ErrorCellValue);
                            break;
                        case CellType.Numeric:
                            short format1 = eCell.CellStyle.DataFormat;
                            if (format1 == 14 || format1 == 31 || format1 == 57 || format1 == 58)
                            {
                                cellVal = eCell.DateCellValue.ToString();
                            }
                            else
                            {
                                cellVal = eCell.NumericCellValue.ToString();
                            }
                            break;
                    }
                    break;
                default:
                    break;
            }
            return cellVal;
        }