java - 无法从错误公式单元格获取文本值

嗨，我遇到了一个错误


  无法从错误公式单元格获取文本值


同时获取具有公式的单元格的值。我正在使用Apache poi 3.17

下面是我的代码：

 public ArrayList<String> excelread(int sheetnum, int rownum, int lastcell) throws IOException{  // to read the expected values from the excel sheet

            ArrayList<String>expected= new ArrayList <String>();

                File src= new File(filepath);               //Writing in excel
                FileInputStream fis=new FileInputStream(src);
                XSSFWorkbook wb=new XSSFWorkbook(fis);
                XSSFSheet sh=wb.getSheetAt(sheetnum);
                XSSFRow row=sh.getRow(rownum);
                XSSFFormulaEvaluator evaluator= wb.getCreationHelper().createFormulaEvaluator();

            for (int currentcell=1;currentcell<=lastcell;currentcell +=2){
                    XSSFCell cell= row.getCell(currentcell);
                    evaluator.evaluateFormulaCell(cell);
                    String expectedresult = sh.getRow(rownum).getCell(currentcell).getStringCellValue(); // getting the value from a particular row and cell
                    expected.add(expectedresult);
                }
             wb.close();
            return expected;
}

最佳答案

在C＃中使用ExcelDataReader

字符串savepath = HttpContext.Current.Server.MapPath（“〜/ Modules / Test / Excel / excel.xls”）;

var excelData = new ExcelDataReaderCaller（savepath）;

公共类ExcelDataReaderCaller
    {
        字符串_path;
        公共ExcelDataReaderCaller（字符串路径）
        {
            _path =路径;
        }

    public IExcelDataReader getExcelReader()
    {
        FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
        IExcelDataReader reader = null;
        try
        {
            if (_path.EndsWith(".xls"))
            {
                reader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            if (_path.EndsWith(".xlsx"))
            {
                reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            return reader;
        }
        catch (Exception)
        {
            throw;
        }
    }

    public IEnumerable<DataRow> getData()
    {
        var reader = this.getExcelReader();
        var excelSheet = reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true
            }
        });

        var sheetsName = from DataTable sheets in excelSheet.Tables select sheets.TableName;
        string sheetName = sheetsName.Take(1).FirstOrDefault().ToString();

        var workSheet = reader.AsDataSet().Tables[sheetName];

        if (workSheet.Rows.Count > 0)
        {
            var rows = from DataRow a in workSheet.Rows select a;

            reader.Close();
            return rows;
        }
        else
        {
            reader.Close();
            return null;
        }

    }
}

获得价值

var Data = excelData.getData（）;

        if (Data != null && Data.Any())
        {
            foreach (var row in Data)
            {
                var  objData = new DataInfo()
                {
                    FirstName = row[0].ToString(),
                    SurName = row[1].ToString()
                };

                ObjDataList.Add(objData);
            }
            Data= null;
            return ObjDataList;
        }

// ObjDataList =新建列表