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

查看：171
本文介绍了如何使用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（））;

我也尝试使用Formula Evaluator，但显示错误如下

 无法解析外部工作簿名称'\Users\asus\Downloads\ ??? &安培; ???? ????? _ 091230.xls’。工作簿环境尚未建立。 
在org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator（OperationEvaluationContext.java:87）
在org.apache.poi.ss.formula.OperationEvaluationContext.getArea3DEval（OperationEvaluationContext.java:273） 
在org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg（WorkbookEvaluator.java:660）
在org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula（WorkbookEvaluator.java:527） 
在org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny（WorkbookEvaluator.java:288）
在org.apache.poi.ss.formula.WorkbookEvaluator.evaluate（WorkbookEvaluator.java:230） 
在org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue（HSSFFormulaEvaluator.java:351）
在org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell（HSSFFormulaEvaluator.java:213） 
在org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells（HSSFFormulaEvaluator.java:324）
在org .apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll（HSSFFormulaEvaluator.java:343）
在HSSFReadWrite.readSheetData（HSSFReadWrite.java:85）
在HSSFReadWrite.main（HSSFReadWrite.java:346）
导致：org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment $ WorkbookNotFoundException：无法解析外部工作簿名称'\Users\asus\Downloads\ ??? &安培; ???? ????? _ 091230.xls’。工作簿环境尚未建立。 
在org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.getWorkbookEvaluator（CollaboratingWorkbooksEnvironment.java:161）
在org.apache.poi.ss.formula.WorkbookEvaluator.getOtherWorkbookEvaluator（WorkbookEvaluator.java:181） 
在org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator（OperationEvaluationContext.java:85）
 ... 11更多


解决方案

确定，尝试回答：



首先：支持到外部工作簿的链接不包括在当前的稳定版本3.10中。所以使用这个版本是不可能直接评估这样的链接。这就是为什么 evaluateAll（）将失败的工作簿链接到外部工作簿。



对于版本3.11将是可能的这样做但是，即使所有的工作簿都被打开，并且所有工作簿的评估者都在场。请参阅： http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks%28java.util.Map%29



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



示例： p>

工作簿workbook.xlsx包含一个公式，其中有一个指向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）; 
工作簿wb = WorkbookFactory.create（inp）; 
 
 Sheet sheet = wb.getSheetAt（0）; 
 
行row = sheet.getRow（0）; 
 if（row == null）row = sheet.createRow（0）; 
 
单元格= 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评估器= wb.getCreationHelper（）。createFormulaEvaluator（）; 
 //evaluator.evaluateAll（）; //不能工作，因为A2中的公式的外部工作簿不可访问
 System.out.println（sheet.getRow（1）.getCell（0））; // [1] Sheet1！$ A $ 1 
 
 //但是我们肯定可以评估单个单元格：
 cell = wb.getSheetAt（0）.getRow（0）.getCell（2） ; 
 System.out.println（evaluateator.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）{
} 
} 
}


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

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

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆