-1

I am doing project which reads a .xlsx file. In that .xlsx file there are some formula cells which I am trying to read but it throws Error 15. Below is the print.. The actual value of the cell is $92,345.16. but that is not getting printed. But if i read non formula cells it works for me. The issue is only with the formula cells. Below is the code snippet. Please some one help me to solve the issue. thanks

Just for info. This .xlsx file is available in one of our server which I am reading. But if I create my own manual .xlsx file by putting some formula and then reading it, it works for me. I am not sure why this happens only for production .xlsx file.

Formula Cell: SUM(G21:G2629) Evaluated Cell Type: ERROR Formula Evaluation Error: 15

XSSFSheet sheet = workbook.getSheetAt(1); 
int desiredRowNum = 5; // Replace with the desired row number (0-based index)
int desiredCellNum = 10; // Replace with the desired cell number (0-based index)
Cell cell = sheet.getRow(desiredRowNum).getCell(desiredCellNum);
if (cell != null && cell.getCellType() == CellType.FORMULA) {
    System.out.println("Formula Cell: " + cell.getCellFormula());
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    System.out.println("Evaluated Cell Type: " + cellValue.getCellType());
    if (cellValue.getCellType() == CellType.NUMERIC) {
        double numericValue = cellValue.getNumberValue();
        System.out.println("Evaluated Numeric Value: " + numericValue);
    } else if (cellValue.getCellType() == CellType.STRING) {
        String stringValue = cellValue.getStringValue();
        System.out.println("Evaluated String Value: " + stringValue);
    } else if (cellValue.getCellType() == CellType.ERROR) {
        System.out.println("Formula Evaluation Error: " + cellValue.getErrorValue());
        if (cellValue.getErrorValue() == FormulaError.DIV0.getCode()) {
            System.out.println("Division by Zero Error Detected");
        }
    }
}
Abra
  • 19,142
  • 7
  • 29
  • 41
Prashant Naik
  • 105
  • 1
  • 8
  • 1
    I Googled for **apache poi excel read formula cell**. First result was: [Read Excel Cell Value Rather Than Formula With Apache POI](https://www.baeldung.com/apache-poi-read-cell-value-formula) (from baeldung.com). Second result was: [Formula Evaluation](https://poi.apache.org/components/spreadsheet/eval.html) (from poi.apache.org). Do they help? If not, there are another 700,000 results including SO, like [Java POI : How to read Excel cell value and not the formula computing it?](https://stackoverflow.com/questions/7608511/java-poi-how-to-read-excel-cell-value-and-not-the-formula-computing-it) – Abra Aug 11 '23 at 12:01
  • 1
    Perhaps you can post a `.xlsx` file that causes the error so that I can reproduce it and subsequently debug it? The file just needs to contain the minimum amount of data in order to reproduce the problem. – Abra Aug 11 '23 at 12:09
  • The `FormulaError ` 15 (0x0F) is the `#VALUE` error. `SUM(G21:G2629)` only results in that error if any of the cells in range `G21:G2629` also contain that error. – Axel Richter Aug 12 '23 at 05:59
  • Hello. this issue has been resolved. Issue was because of Date Cell which was in string instead of date format. After making that change everything is working fine. thanks – Prashant Naik Aug 16 '23 at 10:59

0 Answers0