title: Reading cell with formula gives different result from excel file - Java Apache POI
body: <p>I'm writing a program that:</p>
  
  <ul>
  <li>Opens an excel file</li>
  <li>Writes a formula to a cell - SUM(item:item);</li>
  <li>Reads the value of the output of the formula</li>
  </ul>
  
  <p>Given my the snippet below, I could successfully write down the formula and checking the excel file would yield the correct answer of SUM. </p>
  
  <pre><code>            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)");
  </code></pre>
  
  <p>Sadly when I read the value of the formula, which is supposed to be "101.0" it gives me a "0.0".</p>
  
  <pre><code>            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());
                      }
                  }
              }
  </code></pre>
  
  <p>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. </p>
  
  <p><strong>What am I missing here? Why won't the answer of the first formula be read correctly? Hoping someone can help. Thanks in advance.</strong></p>
  
  <p>Here's what's inside my file:</p>
  
  <pre><code>        |    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
  </code></pre>
  
  <p>Here's the full code inside main:</p>
  
  <pre><code>    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);
          }
      }
  </code></pre>
  
  <p>Here's the output I get:</p>
  
  <pre><code>    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)
  </code></pre>
  
  <p><strong>EDIT</strong>: I'm using Apache POI 3.17</p>
  
  <p><strong>EDIT #2</strong>: Here's the working snippet. </p>
  
  <pre><code>    Row row = sheet.getRow(2); 
      Cell cell = row.createCell(0); 
      cell.setCellFormula("SUM(A1:A2)");
      FormulaEvaluator evaluator = workbookx.getCreationHelper().createFormulaEvaluator();
      evaluator.evaluateFormulaCell(cell);
  </code></pre>