如何使用 poi 更新 Excel 工作表链接

我试图在使用 setForceFormulaRecal 方法后获取更新的单元格值。但我仍然保留旧价值观。这不是实际结果。如果我通过单击打开原始文件，它将询问更新链接对话框。如果我单击“确定”按钮，则它会更新所有单元格公式结果。所以我想在打开之前使用 poi 更新 Excel 工作表链接。请在这种情况下提供帮助。

//设置值之前

HSSFCell cel2=row1.getCell(2);
HSSFCell cel4=row1.getCell(5);
cel2.setCellValue(690);
cel4.setCellValue(690);
wb.setForceFormulaRecalculation(true);
wb.write(stream);

//评估工作簿公式后，我尝试如下

 HSSFWorkbook wb = HSSFReadWrite.readFile("D://workspace//ExcelProject//other.xls");
   HSSFSheet sheet=wb.getSheetAt(14);
   HSSFRow row11=sheet.getRow(10);
   System.out.println("** cell val: "+row11.getCell(3).getNumericCellValue());

我也尝试过公式评估器，但它显示错误如下

Could not resolve external workbook name '\Users\asus\Downloads\??? & ???? ?????_091230.xls'. Workbook environment has not been set up.
    at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:87)
    at org.apache.poi.ss.formula.OperationEvaluationContext.getArea3DEval(OperationEvaluationContext.java:273)
    at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:660)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:527)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:351)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll(HSSFFormulaEvaluator.java:343)
    at HSSFReadWrite.readSheetData(HSSFReadWrite.java:85)
    at HSSFReadWrite.main(HSSFReadWrite.java:346)
Caused by: org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment$WorkbookNotFoundException: Could not resolve external workbook name '\Users\asus\Downloads\??? & ???? ?????_091230.xls'. Workbook environment has not been set up.
    at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.getWorkbookEvaluator(CollaboratingWorkbooksEnvironment.java:161)
    at org.apache.poi.ss.formula.WorkbookEvaluator.getOtherWorkbookEvaluator(WorkbookEvaluator.java:181)
    at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:85)
    ... 11 more

好的，尝试回答：

首先：当前稳定版本 3.10 中不包含对外部工作簿链接的支持。因此，使用此版本无法直接评估此类链接。这就是为什么evaluateAll()对于包含外部工作簿链接的工作簿将失败。

在 3.11 版本中将可以做到这一点。但前提是所有工作簿都已打开并且所有工作簿的评估者都在场。看：http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks%28java.util.Map%29 http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks%28java.util.Map%29

我们可以使用稳定版本 3.10 做的是评估包含没有链接到外部工作簿的公式的所有单元格。

Example:

The workbook "workbook.xlsx" contains a formula with a link to an external workbook in A2:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.InputStream;

import java.util.Map;
import java.util.HashMap;

class ExternalReferenceTest {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   Row row = sheet.getRow(0);
   if (row == null) row = sheet.createRow(0);

   Cell cell = row.getCell(0);
   if (cell == null) cell = row.createCell(0);
   cell.setCellValue(123.45);

   cell = row.getCell(1);
   if (cell == null) cell = row.createCell(1);
   cell.setCellValue(678.90);

   cell = row.getCell(2);
   if (cell == null) cell = row.createCell(2);
   cell.setCellFormula("A1+B1");

   FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
   //evaluator.evaluateAll(); //will not work because external workbook for formula in A2 is not accessable
   System.out.println(sheet.getRow(1).getCell(0)); //[1]Sheet1!$A$1

   //but we surely can evaluate single cells:
   cell = wb.getSheetAt(0).getRow(0).getCell(2);
   System.out.println(evaluator.evaluate(cell).getNumberValue()); //802.35

   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);
   fileOut.flush();
   fileOut.close();

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}