Hello David, May I throw a spanner into the works.
To my mind, HSSF/HSSF is an API that is used to create or read files. I would argue that if you want a calculation engine then there are other solutions and the one I would look at - well two really - is either controling Excel itself via OLE/COM or installing OpenOffice and using UNO (Unoversal Network Objects) to accomplish a similar task. The advantages UNO has over OLE is that the OpenOffice API supports full access via Java (and other languages) and OpenOffice itself is platform independent. Using either of these approaches would allow you to interact with a live, running application and to take advatage of the calculation engine built into those pieces of software. Alternatively, there are pieces of software that I have seen kicking around that claim to emulate Excel's support for formulae. If I come across one again this morning, I will send you the URL. --- On Tue, 1/6/09, Darío Vasconcelos <[email protected]> wrote: From: Darío Vasconcelos <[email protected]> Subject: Conceptual question, using HSSF as a recalculation engine To: [email protected] Date: Tuesday, January 6, 2009, 12:02 PM 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. 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
