Bug 55145

Summary: #VALUE! error in RANK formula
Product: POI Reporter: najh <najh.ru>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: major    
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description najh 2013-06-26 10:52:26 UTC
After executing the code

    public static void main(String args[]) throws IOException {
      //with #VALUE! error (1)
      createExample(new FileOutputStream("1.xls"), new HSSFWorkbook()); 

      //no error           (2)
      createExample(new FileOutputStream("1.xlsx"), new XSSFWorkbook());
    }

    private static void createExample(FileOutputStream file, Workbook wb) throws IOException {
        Sheet sh = wb.createSheet("sheet");
        Row row = sh.createRow(0);
        Cell cell1 = row.createCell(0);
        cell1.setCellValue(100);
        Cell cell2 = row.createCell(1);
        cell2.setCellFormula("RANK(A1,(A1:A1),0)");
        wb.write(file);
    }

 
we've got the following result for line (1)
the #VALUE! error in the generated excel file. 
This error could be corrected manually if we position the cursor into the formula bar and press "Enter".

For line (2) there is no error.
Comment 1 Nick Burch 2013-06-26 10:59:19 UTC
See http://poi.apache.org/spreadsheet/eval.html - you need to evaluate the formulas after setting them, to ensure the cached value is correctly set and available to excel.