You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Peter Arrenbrecht <pe...@gmail.com> on 2009/05/06 17:39:03 UTC
Re: Conceptual question, using HSSF as a recalculation engine
2009/1/6 Darío Vasconcelos <da...@gmail.com>:
> 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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org