Hi Josh, I know, and I've done simple proof-of-concept tests where formulas are evaluated and values are updated automatically, even if there is a number of formulas in the middle that need updating in the way.
But it's not working for this specific sheet, and now I'm questioning myself if HSSF should be used as an "in-server" Excel engine. Most of the questions that I've read in the mailing list regard creating Excel files from scratch, or modifying existing ones so the final user opens them later in Excel. In my case, all I want is HSSF to recalculate values based on existing formulas on an existing file and let me retrieve the result. Regards, On Tue, Jan 6, 2009 at 4:21 PM, Josh Micich <[email protected]> wrote: > Hello Dario, > > > So this is my question: is this a good idea? Is POI supposed to do such > > things? > > POI has supported formula evaluation since version 3. There have been many > improvements as of late including > Speed optimisations > > > > > > > So this is my question: is this a good idea? Is POI supposed to do such > > things? Because so far, my tests have shown that the recalculation isn't > > fully working: if I change the value of A1, and A2 and A3 have formulas > > that > > depend on its value, only A2 is updated but A3 not. Of course this is a > > trivial example: in reality, some cells have more than 20 levels of > > dependency, sometimes the formulas refer to cells that are "before" them, > > other times refer to cells that are after. > > > > The process I'm using is as follows: > > * Open the workbook > > * Create a HSSFFormulaEvaluator > > * Get the value of a cell (with getSheetAt, getRow, getCell) > > * Set its value to some double > > * Traverse all rows and all cells (using sheet.lastRowNum() and > > row.getLastCellNum() and do an evaluateFormulaCell on each one > > * Get the value of a result cell > > > > And the value isn't even the same as Excel shows before changing the > value > > of the cell. I'm worried because for some process we're implementing a > > MonteCarlo method, thus we're going to to this whole process 500 times, > > retrieving the results each cycle, and plotting these points in graphs. > > > > The actual code is as follows: > > > > FileInputStream fis = new > > > > > FileInputStream("/Users/dariovasconcelos/Documents/desa/QA76/Bancomext/src_paramext/poi/riesgo4.xls"); > > HSSFWorkbook wb = new HSSFWorkbook(fis); //or new > > HSSFFormulaEvaluator evaluator = new > > HSSFFormulaEvaluator(wb); > > > > BufferedWriter out = new BufferedWriter(new > > > > > > > > FileWriter("/Users/dariovasconcelos/Documents/desa/QA76/Bancomext/src_paramext/poi/sinCambios.txt")); > > HSSFSheet hoja = wb.getSheetAt(0); > > HSSFRow renglon = hoja.getRow(686); > > HSSFCell celda = renglon.getCell(8); > > celda.setCellValue((double)0.483); > > evaluator.notifySetFormula(celda); > > evaluator.clearAllCachedResultValues(); > > > > n = recalculateAll(wb, evaluator, out, n); > > > > And recalculateAll is coded like this: > > > > private int recalculateAll(HSSFWorkbook wb, > > HSSFFormulaEvaluator evaluator, BufferedWriter out, > int > > n) { > > try { > > // Recalculo todos los renglones > > //for(int sheetNum = 0; sheetNum < > > wb.getNumberOfSheets();sheetNum++) { > > for(int sheetNum = 0; sheetNum < 1; sheetNum++) { > > //int sheetNum =0; > > HSSFSheet sheet = wb.getSheetAt(sheetNum); > > for (int i = 0; i < sheet.getLastRowNum(); i++) { > > HSSFRow r = sheet.getRow(i); > > if (r == null) > > continue; > > for (int j=0; j < r.getLastCellNum(); j++) { > > HSSFCell c = r.getCell(j); > > if (c == null) > > continue; > > int tipo = c.getCellType(); > > int row = c.getRowIndex(); > > int col = c.getColumnIndex(); > > switch (tipo) { > > case HSSFCell.CELL_TYPE_NUMERIC: > > miPrint(out, n++, sheetNum, row, col > , > > "" + > c.getNumericCellValue()); > > break; > > /* > > case HSSFCell.CELL_TYPE_STRING: > > miPrint(out, n++, sheetNum, row, col, > > c.getStringCellValue()); > > break; > > */ > > case HSSFCell.CELL_TYPE_FORMULA: > > miPrint(out, n++, sheetNum, row, col, > > > c.getCellFormula().replace("$", > > "")); > > int tipoRes = > > evaluator.evaluateFormulaCell(c); > > if (tipoRes == > > HSSFCell.CELL_TYPE_NUMERIC) > > miPrint(out, n++, sheetNum, row, > > col, > > "" + > > c.getNumericCellValue()); > > else if (tipoRes == > > HSSFCell.CELL_TYPE_STRING) > > miPrint(out, n++, sheetNum, row, > > col, > > c.getStringCellValue()); > > else if (tipoRes == > > HSSFCell.CELL_TYPE_ERROR) > > miPrint(out, n++, sheetNum, row, > > col, > > "" + > > c.getErrorCellValue()); > > /* > > HSSFCell celdaTmp = > > evaluator.evaluateInCell(c); > > if (celdaTmp.getCellType() > > == > HSSFCell.CELL_TYPE_NUMERIC) > > miPrint(out, n++, sheetNum, row, > > col, > > "" + > > c.getNumericCellValue()); > > */ > > break; > > } > > } > > } > > } > > } catch (Exception e) { > > e.printStackTrace(); > > } > > > > return n; > > } > > > > > > "miPrint" is simply a routine to print the value of the cell to a > Buffered > > Writer. > > > > Regards, > > > > Dario > > -- > > Here's a rule I recommend: Never practice two vices at once. > > - Tallulah Bankhead > > > -- Here's a rule I recommend: Never practice two vices at once. - Tallulah Bankhead
