Apache POI org.apache.poi.ss.formula.FormulaParseException

apache-poi, java

Solution

First, leave POI and check in excel itself if the formula is working fine. There is no problem at POI end. I wrote this sample code and it's working fine.

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
        XSSFCell cellvalue = sheet.createRow(0).createCell(0);
        sheet.getRow(0).createCell(1).setCellValue(2);
        sheet.getRow(0).createCell(2).setCellValue(5);
        cellvalue.setCellFormula("B1+C1");
        if (cellvalue.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
           evaluator.evaluateFormulaCell(cellvalue);
        }
        System.out.println(cellvalue.getNumericCellValue());

It evaluates to 2 + 5 = 7 as it should.

Problem

I'm using Apache POI and I'm facing an issue when evaluating a formula cell. This is my code: ``` XSSFCell cellvalue = sheet.getRow(6).getCell(2); if (cellvalue.getCellType() == cellvalue.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(cellvalue); } ``` The formula: ``` =C6/Num_Input and Num_Input = F36 ``` The issue: ``` org.apache.poi.ss.formula.FormulaParseException: Cell reference expected after sheet name at index 18. ```

Original source