Bug 55145 - #VALUE! error in RANK formula
Summary: #VALUE! error in RANK formula
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-06-26 10:52 UTC by najh
Modified: 2013-06-26 10:59 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.