Paul, My personal experience with circular references is that POI doesn't handle these well. I can't say for certain but the behavior I've seen with circular references is that it's usually conditional, meaning the recursion only happens when an 'if' statement is true. Excel seems to handle these cases on the fly, meaning that if there really is recursion, it's probably limited by the state of the cells at run time. POI, on the other hand, seems to try to parse the references, including the circular references. It's been my experience it's usually easier to fix the spreadsheet.
Maybe other people have had difference experiences, but for what it's worth, this is my 2¢. Sincerely, Jon >________________________________ > From: Paul T. Calhoun <[email protected]> >To: [email protected] >Sent: Friday, October 12, 2012 3:09 PM >Subject: Circular Reference in Excel > >I have a spreadsheet with a cell with a circular reference. > >I read the SS into memory with poi, Add / Change values, compute and save the >spreadsheet. > >If I open the wb in excel the formula is not computed. If I calculate the >sheet >it computes the value with NO errors. > >How can I get POI to perform the calculation and save the correct value in the >spreadsheet. > >It behaves this way with or without automatic calculation enabled. > >Here is the code that I'm using to try to force evaluation. > >Any insight would be greatly appreciated. > > >wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(0).getStringCellValue()); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(1).getNumericCellValue()); > FormulaEvaluator evaluator = >wb.getCreationHelper().createFormulaEvaluator(); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(0).getStringCellValue()); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(1).getNumericCellValue()); > //String wbsheets[] = >{"Original","Main_DB","Parameters","Translator","Workers Comp.","Rate >Report","Notes Fields"}; > String wbsheets[] = {"Rate Report","Notes Fields"}; > for(String sheetName : wbsheets) { > Sheet sheet = wb.getSheet(sheetName); > System.out.println("processing sheet: " + >sheet.getSheetName()); > for(Row r : sheet) { > for(Cell c : r) { > if(c.getCellType() == >Cell.CELL_TYPE_FORMULA) { > > evaluator.evaluateFormulaCell(c); > evaluator.clearAllCachedResultValues(); > //System.out.println("Recalced: >"+r.getRowNum()+c.getColumnIndex() + "in "+ sheet.getSheetName()); > } > } > } > } > > >XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wb); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(0).getStringCellValue()); > System.out.println(wb.getSheet("Rate >Report").getRow(16).getCell(1).getNumericCellValue()); > > // Write the output to a file > String excelOutput = "C:\\temp\\calcrater.xlsx"; > FileOutputStream fileOut = new >FileOutputStream(excelOutput); > wb.write(fileOut); > fileOut.close(); > > > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [email protected] >For additional commands, e-mail: [email protected] > > > >
