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]
