Scala中使用HSSFWorkbook实现Excel循环引用迭代计算方案咨询

核心解决思路 #

HSSFWorkbook没有直接暴露启用迭代计算的上层API，但可以通过操作其底层的IterationRecord记录来实现，同时配合公式计算器的迭代参数设置，完成循环引用的计算。

具体实现步骤及代码示例 #

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.hssf.record.IterationRecord

// 初始化HSSF工作簿
val workbook = new HSSFWorkbook()

// 查找现有IterationRecord，不存在则新建
val iterationRecord = workbook.getRecords
  .find(_.isInstanceOf[IterationRecord])
  .map(_.asInstanceOf[IterationRecord])
  .getOrElse(new IterationRecord())

// 启用迭代计算
iterationRecord.setIteration(true)
// 可选：设置最大迭代次数（默认100）
iterationRecord.setIterateCount(100)
// 可选：设置计算允许的最大误差（默认0.001）
iterationRecord.setMaxChange(0.001)

// 若为新建记录，需添加到工作簿
if (!workbook.getRecords.contains(iterationRecord)) {
  workbook.addRecord(iterationRecord)
}

// 构建带循环引用的工作表示例
val sheet = workbook.createSheet("LoopRefSheet")
val cellA1 = sheet.createRow(0).createCell(0)
val cellA2 = sheet.createRow(1).createCell(0)
// 设置循环引用公式：A1 = A2 + 1，A2 = A1 -1
cellA1.setCellFormula("A2+1")
cellA2.setCellFormula("A1-1")

// 配置公式计算器的迭代参数，确保计算生效
val evaluator = workbook.getCreationHelper.createFormulaEvaluator()
evaluator.setIterationCount(100)
evaluator.evaluateAll()

// 输出工作簿到文件
val outputStream = new java.io.FileOutputStream("loop_ref_test.xls")
workbook.write(outputStream)
outputStream.close()
workbook.close()

额外建议 #

如果业务允许，优先考虑迁移到XSSFWorkbook（对应.xlsx格式），它上层直接提供了迭代计算的API：

val xssfWorkbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook()
xssfWorkbook.setIterationCount(100)
xssfWorkbook.setForceFormulaRecalculation(true)

内容的提问来源于stack exchange，提问作者Vaibhav Dixit