Thanks Dave, actually it took me a while to clean this spreadsheet and I believe it now doesn't have any user defined functions, but I'll double check.
So cells refering to yet-unparsed formulas shouldn't be a problem? Does HSSF read all the file and create a graph or something similar, in order to figure out which cells affect which cells? Or is it more of a brute force approach? BTW, I tried turning on the debug flag in log4j, but the amount of information is overwhelming and I didn't know where to start. Regards, On Tue, Jan 6, 2009 at 5:03 PM, David Fisher <[email protected]> wrote: > Dario, > > You should try a recent svn. There were changes to improve named ranges > across separate worksheets. > > I think you have a good idea. It allows all types of advantages. We are > doing something similar although we are focused now on the creation side, we > recognize the possibilities of reading in the users adjustments. > > You do need to be careful if your workbook uses any User Defined Functions. > These aren't implemented, I do begin to wonder if a plug-in technology might > allow people to roll in their own implementations of custom functions. > > Regards, > Dave > > > On Jan 6, 2009, at 4:47 PM, Darío Vasconcelos wrote: > > 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 >> > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > -- Here's a rule I recommend: Never practice two vices at once. - Tallulah Bankhead
