CODEWITHSUNDEEP
javaexcelexcel-formulaapache-poi
javaGuyInNeedOfHelp
javaGuyInNeedOfHelp

Reputation: 23

Reading cell with formula gives different result from excel file - Java Apache POI

I'm writing a program that:

Given my the snippet below, I could successfully write down the formula and checking the excel file would yield the correct answer of SUM. 

            Row row = sheet.getRow(2); //which is row 3 at excel file
            Cell cell = row.createCell(0); //so that's cell A3
            cell.setCellFormula("SUM(A1:A2)");

Sadly when I read the value of the formula, which is supposed to be "101.0" it gives me a "0.0".

            row = sheet.getRow(2);
            Cell currentCell = row.getCell(0); //getting value of formula which we wrote above
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM PROGRAM: "+currentCell.getNumericCellValue());
                    }
                }
            }

I tried something out which was to write a formula via the MS Excel app to compare the value it returns when read by the program. Turns out, for my progam-written formula, it's the same incorrect value but for the one written using MS Excel, the value was perfectly read.

What am I missing here? Why won't the answer of the first formula be read correctly? Hoping someone can help. Thanks in advance.

Here's what's inside my file:

        |    A      |     B     |
    1   |    1      |     2     |
    2   |   100     |    100    |
    3   |=SUM(A1:A2)|=SUM(B1:B2)| //where A3 was written by java and B3 was written using MS Excel

Here's the full code inside main:

    String surveyDirectory = "DATABASE.xlsx";
    XSSFWorkbook workbookx;
    FileOutputStream outputStream;
    FileInputStream fis;

    File file = new File(surveyDirectory);

    if (file.exists()) {
        try {
            fis = new FileInputStream(surveyDirectory);
            workbookx = new XSSFWorkbook(fis);
            Sheet sheet = workbookx.getSheetAt(0);

            //writing a formula to file

            Row row = sheet.createRow(2); //which is row 3 at excel file
            Cell cell = row.createCell(0); //so that's A3 in excel
            cell.setCellFormula("SUM(A1:A2)");

            outputStream = new FileOutputStream(surveyDirectory); //write it down the file
            workbookx.write(outputStream);
            outputStream.close();

            System.out.println("DONE WRITING");

            //reading the fomula file
            //we are accessing same sheet
            row = sheet.getRow(2);
            Cell currentCell = row.getCell(0); //getting value of formula which we wrote above
            System.out.println("FORMULA at A3: " + currentCell);
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM PROGRAM: "+currentCell.getNumericCellValue());
                    }
                }
            }

            row = sheet.getRow(2); //formula is =SUM(B1:B2)
            currentCell = row.getCell(1); //getting value of formula which was written vie MS Excel
            System.out.println("FORMULA at B3: " + currentCell);
            if (currentCell != null) {
                if (currentCell.getCellTypeEnum() == CellType.FORMULA) {
                    if (currentCell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
                        System.out.println("VALUE FROM MS EXCEL: "+currentCell.getNumericCellValue());
                    }
                }
            }

            System.out.println("DONE READING");
            fis.close();
            workbookx.close();
        } catch (IOException ex) {
            Logger.getLogger(TextHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

Here's the output I get:

    DONE WRITING
    FORMULA at A3: SUM(A1:A2)
    VALUE FROM PROGRAM: 0.0
    FORMULA at B3: SUM(B1:B2)
    VALUE FROM MS EXCEL: 102.0
    DONE READING
    BUILD SUCCESSFUL (total time: 1 second)

EDIT: I'm using Apache POI 3.17

EDIT #2: Here's the working snippet. 

    Row row = sheet.getRow(2); 
    Cell cell = row.createCell(0); 
    cell.setCellFormula("SUM(A1:A2)");
    FormulaEvaluator evaluator = workbookx.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateFormulaCell(cell);

Upvotes: 2

Views: 1536

Answers (1)

Paul Hicks
Paul Hicks

Reputation: 14019

Setting the formula doesn't update the associated value in the cell. You need to evaluate the cell too, as described here. You can use FormulaEvaluator.evaluate() to "peek" at the result, or FormulaEvaluator.evaluateFormulaCell() to save the result into the cell like Excel does.

Upvotes: 4

Related Questions