如何使用 poi 更新 Excel 工作表链接 [英] How to update Excel sheet links using poi

问题描述

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

I'm trying to get updated cell values after use setForceFormulaRecal method. But I'm getting still old values. Which is not actual result. If I opened Original file by clicking It will asking update Links dialogue box. If I click "ok" button then Its updating all cell formula result. So I want to update excel sheet links before its open by using poi. Please help in this situation.

//设置值前

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

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

//After Evaluatting the work book formulas I'm trying as follow

 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());

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

I'm Also tried with Formula Evaluator But its showing errors As follow

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() 对于带有外部工作簿链接的工作簿会失败.

First of all: Support for links to external workbooks is not included into the current stable version 3.10. So with this version it is not possible to evaluate such links directly. That's why evaluateAll() will fail for workbooks with links to external workbooks.

在 3.11 版中可以这样做.但也仅当所有工作簿都已打开并且所有工作簿的评估器都存在时.请参阅:http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks%28java.util.Map%29

With Version 3.11 it will be possible to do so. But also only even if all the workbooks are opened and Evaluators for all the workbooks are present. See: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks%28java.util.Map%29

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

What we can do with the stable version 3.10, is to evaluate all the cells which contains formulas which have not links to external workbooks.

示例:

工作簿workbook.xlsx"包含一个公式，其中包含指向 A2 中外部工作簿的链接:

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) {
  }
 }
}

这篇关于如何使用 poi 更新 Excel 工作表链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！