强制整个工作簿的全部计算在ColdFusion中/ Apache的POI [英] Force full calculation of entire workbook in Coldfusion / Apache POI

问题描述

由于与 Col​​dFusion的9所产生的交叉表公式工作簿通过的Apache POI 。

我想以编程方式迫使整个工作簿做一个有依赖关系树完全重新计算的之前工作簿保存到磁盘上。

I want to programmatically force an entire workbook to do a "full calculation with dependency tree rebuild" before saving the workbook to disk.

所以，当我的最终用户打开在S preadsheet，他们没有到preSS <大骨节病>控制 - <大骨节病>替代 - <大骨节病>移< /骨节病> - <大骨节病> F9

So that when my end-users open the spreadsheet, they do not have to press Ctrl-Alt-Shift-F9.

我如何做到这一点的的ColdFusion ？

参考文献：

• http://adobe.ly/HbLBZo


• http://poi.apache.org/s$p$padsheet/ eval.html


• http://bit.ly/HJJA3Q

• http://adobe.ly/HbLBZo
• http://poi.apache.org/spreadsheet/eval.html
• http://bit.ly/HJJA3Q

推荐答案

不幸的是没有简单的答案这一个。

Unfortunately there is no easy answer to this one.

选项1：

我觉得最短的路线是使用<一个href=\"http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CreationHelper.html\"><$c$c>CreationHelper.createFormulaEvaluator().evaluateAll().不幸的是，它不提供在CF9。在POI的版本比一个随CF9加入该方法。这同样适用于 setForceFormulaRecalculation（）。你也许可以使用JavaLoader.cfc加载POI的新版本。然后，你将有机会获得这些方法。但是，这可能会更多地参与比你想为这一个任务。

I think the shortest route is to use CreationHelper.createFormulaEvaluator().evaluateAll(). Unfortunately, it is not available in CF9. That method was added in a later version of POI than the one shipped with CF9. The same applies to setForceFormulaRecalculation(). You could probably use the JavaLoader.cfc to load a newer version of POI. Then you would have access to those methods. But that may be more involved than you want for this one task.

选项2：

如果做不到这一点，如果你知道正确的顺序，你可以通过Sheets迭代，并重新计算公式为每一个：

Failing that, if you know the proper sequence you could iterate through the sheets 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);
            }
        }
    }
}

选项3：

另一种可能是工作簿打开时，填充自动重新计算宏的模板。的明显的缺点是它依赖于一个宏。但由于它是用Excel本身执行，这大概是最可靠/可靠的方案。

Another possibility is to populate a template with a macro which automatically recalculates when the workbook opens. The obvious disadvantage is it relies on a macro. But since it is performed by Excel itself, it is probably the most reliable/robust option.

这篇关于强制整个工作簿的全部计算在ColdFusion中/ Apache的POI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！