You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Paul T. Calhoun" <pa...@gmail.com> on 2012/10/12 23:09:53 UTC
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Circular Reference in Excel
Posted by Jon Svede <js...@yahoo.com>.
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 <pa...@gmail.com>
>To: user@poi.apache.org
>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: user-unsubscribe@poi.apache.org
>For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>