NET npoi帮助类

nuget添加npoi

    /// <summary>
    /// npoi帮助类
    /// </summary>
    public static class NpoiHelper
    {
        /// <summary>
        /// 根据文件路径，获取表格集合
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<DataTable> GetDataTableList(string filePath)
        {
            var list = new ConcurrentBag<DataTable>();

            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var isExcel2007 = filePath.IsExcel2007();
                var workBook = stream.GetWorkbook(isExcel2007);
                var sheetIndexList = new List<int>();
                for (int i = 0; i < workBook.NumberOfSheets; i++) sheetIndexList.Add(i);
                Parallel.ForEach(sheetIndexList, new ParallelOptions
                {
                    MaxDegreeOfParallelism = 3
                }, (source, state, index) =>
                {
                    try
                    {
                        if (!workBook.IsSheetHidden(source))
                            list.Add(GetDataTableToY(workBook, source));
                    }
                    catch (NPOI.POIFS.FileSystem.OfficeXmlFileException nopiEx)
                    {
                        Console.WriteLine($"SheetIndex:{index}		Exception:{nopiEx.Message}");
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                });
            }

            return list.ToList();
        }

        /// <summary>
        /// 根据sheet索引，把数据转换为datatable,以Y轴为准
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheetIndex">sheet索引</param>
        /// <param name="validRowIndex"></param>
        /// <returns></returns>
        public static DataTable GetDataTableToY(IWorkbook workBook, int sheetIndex, int validRowIndex = 0)
        {
            var sheet = workBook.GetSheetAt(sheetIndex);
            var table = new DataTable(sheet.SheetName);

            //  设置最大列，默认为1
            var maxColumnNum = 1;
            //  不是有效列集合，连续超过三行不读取后续所有列
            var noValidColumnList = new List<int>();
            //  列：按照列把数据填充到datatable中，防止无限列出现
            for (var columnIndex = 0; columnIndex < maxColumnNum; columnIndex++)
            {
                var column = new DataColumn();
                table.Columns.Add(column);
                noValidColumnList.Add(columnIndex);
                //  列中所有数据都是null为true
                var isAllEmpty = true;
                //  行
                for (var rowIndex = 0; rowIndex < sheet.LastRowNum; rowIndex++)
                {
                    if (columnIndex == 0) table.Rows.Add(table.NewRow());
                    var itemRow = sheet.GetRow(rowIndex);
                    if (itemRow == null) continue;
                    maxColumnNum = maxColumnNum < itemRow.LastCellNum ? itemRow.LastCellNum : maxColumnNum;
                    //  把格式转换为utf-8
                    var itemCellValue = itemRow.GetValue(columnIndex).FormatUtf8String();
                    if (!itemCellValue.IsNullOrWhiteSpace()) isAllEmpty = false;
                    table.Rows[rowIndex][columnIndex] = itemCellValue;
                }

                //  当前列有值
                if (!isAllEmpty)
                    noValidColumnList.Clear();
                //  连续空白列超过三行 或 有空白行且当前行为最后一行
                else if (noValidColumnList.Count > 3 || (noValidColumnList.Count > 0 && columnIndex == maxColumnNum - 1))
                {
                    for (var i = noValidColumnList.Count - 1; i >= 0; i--)
                        table.Columns.RemoveAt(noValidColumnList[i]);
                    break;
                }
            }
            // 得到一个sheet中有多少个合并单元格
            int sheetMergeCount = sheet.NumMergedRegions;
            for (var i = 0; i < sheetMergeCount; i++)
            {
                // 获取合并后的单元格
                var range = sheet.GetMergedRegion(i);
                sheet.IsMergedRegion(range);
                var cellValue = string.Empty;
                for (var mRowIndex = range.FirstRow; mRowIndex <= range.LastRow; mRowIndex++)
                {
                    for (var mColumnIndex = range.FirstColumn; mColumnIndex <= range.LastColumn; mColumnIndex++)
                    {
                        var itemCellValue = table.Rows[range.FirstRow][range.FirstColumn].FormatUtf8String();
                        if (!itemCellValue.IsNullOrWhiteSpace())
                            cellValue = itemCellValue;
                        table.Rows[mRowIndex][mColumnIndex] = cellValue;
                    }
                }
            }

            return table;
        }

        #region 公共方法

        /// <summary>
        /// 判断excel是否是2007版本：.xls
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool IsExcel2007(this string filePath)
        {
            return Path.GetExtension(filePath)?.ToLower() == ".xls";
        }

        /// <summary>
        /// 根据版本创建IWorkbook对象
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="isExcel2007"></param>
        /// <returns></returns>
        public static IWorkbook GetWorkbook(this Stream stream, bool isExcel2007)
        {
            return isExcel2007 ? (IWorkbook)new HSSFWorkbook(stream) : new XSSFWorkbook(stream);
        }
        /// <summary>
        /// 获取XSSFRow的值（全部统一转成字符串）
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static string GetValue(this IRow row, int index)
        {
            var rowCell = row.GetCell(index);
            return GetValueByCellStyle(rowCell, rowCell?.CellType);
        }

        /// <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 if (rowCell.CellStyle.GetDataFormatString().Contains("$"))
                    {
                        value = "$" + rowCell.NumericCellValue.ToString();
                    }
                    else if (rowCell.CellStyle.GetDataFormatString().Contains("￥"))
                    {
                        value = "￥" + rowCell.NumericCellValue.ToString();
                    }
                    else if (rowCell.CellStyle.GetDataFormatString().Contains("¥"))
                    {
                        value = "¥" + rowCell.NumericCellValue.ToString();
                    }
                    else if (rowCell.CellStyle.GetDataFormatString().Contains("€"))
                    {
                        value = "€" + 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;
        }

        #endregion


    }