title: Apache POI string formula error
body: <p>I use Apache POI to create Excel file with string formula and get #VALUE! error in result file.</p>
  
  <p>Source code is</p>
  
  <pre><code>public static void main(String[] args) throws IOException {
      Workbook wb = new HSSFWorkbook();
      FormulaEvaluator ev = wb.getCreationHelper().createFormulaEvaluator();
      Sheet ws = wb.createSheet();
      Row row = ws.createRow(0);
      Cell cell;
      cell = row.createCell(0);
      cell.setCellValue("abc");
  
      cell = row.createCell(1);
      printCellInfo(row.getCell(1),"before formula");
      cell.setCellFormula("IF(A1&lt;&gt;\"\",MID(A1,1,2),\" \")");
  
      printCellInfo(row.getCell(1), "after formula");
      ev.evaluateAll();
      printCellInfo(row.getCell(1), "after recalc");
  
      OutputStream os = new FileOutputStream("xx.xls");
      wb.write(os);
  
  }
  </code></pre>
  
  <p>(<strong>printCellInfo</strong> is info method I tell below)
  and the error cell is B1. Excel says "<em>value used in cell has incorrect data type</em>".
  BUT if open this cell to edit (using F2) and press Enter - formula calculates correctly!!!</p>
  
  <p>Now about cell info. Helping method is</p>
  
  <pre><code>static void printCellInfo(Cell cell, String infoText) {
      System.out.println("Cell "+CellReference.convertNumToColString(cell.getColumnIndex())+cell.getRowIndex()+" "+infoText);
      int ct = cell.getCellType();
      System.out.println("  Cell type "+ct);
      if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
          ct = cell.getCachedFormulaResultType();
          System.out.println("  Cached type "+ct);
      }
      switch (ct) {
          case Cell.CELL_TYPE_NUMERIC: 
              System.out.println("  Number &lt;"+cell.getNumericCellValue()+"&gt;");
              break;
          case Cell.CELL_TYPE_STRING:
              System.out.println(" String &lt;"+cell.getStringCellValue()+"&gt;");
      }
      System.out.println("============================");
  }
  </code></pre>
  
  <p>and output of <strong>main</strong> execution is</p>
  
  <pre><code>Cell B0 before formula
    Cell type 3
  ============================
  Cell B0 after formula
    Cell type 2
    Cached type 0
    Number &lt;0.0&gt;
  ============================
  Cell B0 after recalc
    Cell type 2
    Cached type 1
   String &lt;ab&gt;
  ============================
  </code></pre>
  
  <p>So B0 is BLANK at first and NUMBER after formula insertion. Why POI do so and maybe problem starts here?
  Unfortunately, correct datatype in POI after recalculation does not become correct in excel.</p>
  
  <p>By the way, formulas set from POI IF(A1&lt;>"","A","B") or MID(A1,1,2) are calculated correct.
  And B1=MID(A1,1,2) and C1=IF(A1&lt;>"",B1," "), set from POI is calculated correct true.</p>
  
  <p>Any ideas? I found the only topic <a href="https://openclassrooms.com/forum/sujet/apache-poi-formula-valeur" rel="nofollow">https://openclassrooms.com/forum/sujet/apache-poi-formula-valeur</a> over the internet, there are some ideas, but no working solution...</p>
  
  <pre><code>cell = row.getCell(1);
  cell.setCellValue(cell.getStringCellValue());
  </code></pre>
  
  <p>does not help too.</p>