|Summary:||Problems with POI-created formula cells|
|Product:||POI||Reporter:||Luca Billi <lucabilli>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
Description Luca Billi 2003-02-12 09:28:56 UTC
I'm using jakarta-poi-1.9.0-dev-20030122. If I create a workbook containing formulas and then I try to call cell.getNumericCellValue on formula cells, returns 0.0 (the same happens if I save the workbook on disk and then I read it again with POI) If I open such a workbook with Excel2000, it works, but when I try to close the document (without any manual change), Excel prompts me with a question: "Do you want to save the changes you made to POI.xls? Microsoft Excel recalculates formulas when opening files last saved by an earlier version of Excel". Now, if I say "yes", and I read the newly saved workbook with POI, the cell.getNumericCellValue on formula cells returns the correct value. Here I put a short Java code presenting the problem, then I will attach two very simple xls (POI.xls and Excel.xls), the first created with POI and containing a formula cell that is not calculated by POI when asked to, the latter is the Excel saved version of the same workbook (it is quite bigger than the original). HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.createSheet("Sheet"); HSSFRow row = sh.createRow((short) 0); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(3.18); cell = row.createCell((short) 1); cell.setCellFormula("A1"); //wb.write(new FileOutputStream("POI.xls")); //wb = new HSSFWorkbook(new FileInputStream("POI.xls")); //cell = wb.getSheet("Sheet").getRow(0).getCell((short) 1); System.out.println(cell.getCellFormula()); System.out.println(cell.getNumericCellValue()); This prints out: A1 0.0 The same happens if you uncomment the three commented lines By the way, since I know that formulas are under development, today I wanted to download the last nightly release before submitting this bug, but I found the nightly directory is empty ...
Comment 3 Avik Sengupta 2003-10-27 13:57:43 UTC
I am sorry it took so long to respond, but for some reason this has slipped under, and I hadnt read the bug report properly. What you describe is NOT a bug. There is no calculation engine in POI. POI just puts the formula in the workbook in a format that excel understands. It does NOT do any calculation. When you open the file in excel, it is excel that calculates the result and stores in the file. POI can only then read the value. Hope this explains how things work. If not, please continue to discuss this on the mailing lists, where we will be glad to help.