问题描述
给定一个工作簿,其中包含由 Coldfusion 9
通过 Apache POI
生成的跨表公式.
Given a workbook with cross-sheet formulas generated by Coldfusion 9
via 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.
这样当我的最终用户打开电子表格时,他们不必按 ---.
So that when my end-users open the spreadsheet, they do not have to press ---.
如何在 coldfusion
中完成此操作?
How do I accomplish this in coldfusion
?
参考:
推荐答案
很遗憾,这个问题没有简单的答案.
Unfortunately there is no easy answer to this one.
选项 1:
我认为最短的路线是使用 CreationHelper.createFormulaEvaluator().evaluateAll()
.不幸的是,它在 CF9 中不可用.该方法是在比 CF9 附带的版本更高的 POI 版本中添加的.这同样适用于 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:
否则,如果您知道正确的顺序,您可以遍历表格并重新计算每个表格的公式:
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 中强制完整计算整个工作簿的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!