java异常resolveExce,如何使用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.

//Before Setting values

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

解决方案

OK, trying an answer:

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.

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

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.

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

}

}

}