2009/1/6 Darío Vasconcelos <[email protected]>: > Hi, > > My problem is, I have a very complex XLS file (some 33,000 formulas, > unordered and scattered all over the place) which I was initially trying to > fully implement in Java. When the complexity of the document struck us, we > wondered if POI could do the job. So the idea is to: > * Read some input data from a text file > * Open the file via HSSF > * Feed all the inputs to the cells > * Tell HSSF to recalculate all cells > * Retrieve the value of some of the results. > The XLS file is then dismissed because we're saving these results to a > database. > > 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.
Sorry for the late answer, but you might want to look at http://formulacompiler.org. It compiles the formulae in an Excel/OpenOffice spreadsheet to a JVM byte code implementation of the same computation. -parren > 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 > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
