Force full calculation of entire workbook in Coldfusion / Apache POI
Given the book with a cross formulas generated using Coldfusion 9
through Apache POI
.
I want to programmatically force the entire book to do a "complete calculation with rebuilding the dependency tree" before saving the book to disk.
So, when my end users open the spreadsheet, they don't have to click Ctrl- Alt- Shift- F9.
How to do it in coldfusion
?
Literature:
source to share
Unfortunately, there is no simple answer to this question.
Option 1:
I think the shortest route is to use CreationHelper.createFormulaEvaluator().evaluateAll()
. Unfortunately it is not available in CF9. This method was added in a later version of POI than the one that ships with CF9. The same applies to setForceFormulaRecalculation()
. You can probably use JavaLoader.cfc to load the new POI version. Then you will have access to these methods. But that may be more than you want for this task.
Option 2:
If not, if you know the correct sequence, you can iterate through the worksheets and recalculate the formulas for each one:
wb = sheet.getWorkBook();
evaluator = wb.getCreationHelper().createFormulaEvaluator();
// you could also use an array of sheet names instead
for(sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
poiSheet = wb.getSheetAt(sheetNum);
rows = poiSheet.rowIterator();
while (rows.hasNext()) {
r = rows.next();
cells = r.cellIterator();
while (cells.hasNext()) {
c = cells.next();
if (c.getCellType() == c.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
Option 3:
Another possibility is to populate the template with a macro that automatically recalculates when you open the workbook . The obvious downside is that it relies on a macro. But since it is executed by Excel itself, this is probably the most robust / reliable option.
source to share