ASPNET 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}\t\tException:{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


    }