使用NPOI获取EXCEL内容 如果含有公式 自动转化显示值

复制函数可以直接借用

private static List<List<string>> ReadExcel(string filePath)
        {
            List<List<string>> allRowsData = new List<List<string>>();
            // 检测文件类型
            IWorkbook workbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.EndsWith(".xlsx"))
                {
                    workbook = new XSSFWorkbook(file); // 处理 .xlsx 文件
                }
                else if (filePath.EndsWith(".xls"))
                {
                    workbook = new HSSFWorkbook(file); // 处理 .xls 文件
                }
                else
                {
                    throw new Exception("文件格式错误.");
                }
                // 获取第一个工作表
                ISheet sheet = workbook.GetSheetAt(0);
                // 遍历所有行--
                for (int row = 0; row <= sheet.LastRowNum; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    List<string> rowData = new List<string>();
                    if (currentRow != null) // 确保该行不是空行
                    {
                        // 遍历所有单元格
                        for (int col = 0; col < currentRow.LastCellNum; col++)
                        {
                            ICell cell = currentRow.GetCell(col);
                            string cellValue = string.Empty;
                            if (cell != null)
                            {
                                // 根据单元格类型获取值
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        cellValue = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:
                                        cellValue = cell.NumericCellValue.ToString();
                                        break;
                                    case CellType.Boolean:
                                        cellValue = cell.BooleanCellValue.ToString();
                                        break;
                                    case CellType.Formula://单元格中带有公式，根据类型继续判断
                                        switch (cell.CachedFormulaResultType)
                                        {
                                            case CellType.String:
                                                cellValue = cell.StringCellValue;
                                                break;
                                            case CellType.Numeric:
                                                cellValue = cell.NumericCellValue.ToString();
                                                break;
                                            case CellType.Boolean:
                                                cellValue = cell.BooleanCellValue.ToString();
                                                break;
                                            case CellType.Blank:
                                                break;
                                            default:
                                                StringBuilder sb = new StringBuilder();
                                                throw new Exception(sb.ToString());
                                        }
                                        break;
                                    default:
                                        cellValue = string.Empty;
                                        break;
                                }
                            }
                              rowData.Add(cellValue);
                        }
                    }
                    allRowsData.Add(rowData); // 将当前行数据添加到总列表中
                }
            }
            // 输出所有行的数据
            foreach (var row in allRowsData)
            {
                Console.WriteLine(string.Join(", ", row));
            }
            return allRowsData;
        }