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

查看：2836
本文介绍了如何使用POI更新Excel工作表链接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图让使用后setForceFormulaRecal方法更新单元格值。但我发现了还在旧值。这是不实际的结果。如果我点击它会要求更新打开原始文件链接对话框。如果我点击确定按钮，然后它更新的所有单元格公式的结果。所以我想更新之前，使用POI其开放的Excel工作表的链接。请在这种情况下提供帮助。

//之前设定值

  HSSFCell CEL2 = row1.getCell（2）;
HSSFCell cel4 = row1.getCell（5）;
cel2.setCellValue（690）;
cel4.setCellValue（690）;
wb.setForceFormulaRecalculation（真）;
wb.write（流）;

// Evaluatting工作簿的公式我想如下之后

  HSSFWorkbook WB = HSSFReadWrite.readFile（D：//workspace//ExcelProject//other.xls）;
   HSSFSheet片= wb.getSheetAt（14）;
   HSSFRow row11 = sheet.getRow（10）;
   的System.out.println（**细胞VAL：+ row11.getCell（3）.getNumericCellValue（））;

我也用公式计算器但它显示错误如下试图

 无法解析外部工作簿的名称'\\用户\\ ASUS \\下载\\？ ＆安培; ???? ????? _ 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：产生的原因无法解析外部工作簿的名称'\\用户\\ ASUS \\下载\\？ ＆安培; ???? ????? _ 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更多


解决方案

OK，想一个答案：

首先：支持链接到外部工作簿不包含到当前稳定版本3.10。因此，与这个版本是不可能直接评估这样的链接。这就是为什么 evaluateAll（）将失败链接到外部工作簿的工作簿。

使用3.11版将有可能这样做。但也仅即使所有的工作簿打开，所有的工作簿评价者是present。参见：<一href=\"http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setu$p$pferencedWorkbooks%28java.util.Map%29\" rel=\"nofollow\">http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setu$p$pferencedWorkbooks%28java.util.Map%29

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

例如：

该工作簿workbook.xlsx包含了一个链接到A2外部工作簿的公式：

 进口org.apache.poi.xssf.usermodel *。
导入org.apache.poi.ss.usermodel *。
导入org.apache.poi.ss.util *。
进口org.apache.poi.openxml4j.exceptions.InvalidFormatException;进口java.io.FileOutputStream中;
进口java.io.FileNotFoundException;
进口java.io.IOException异常;
进口java.io.FileInputStream中;
进口的java.io.InputStream;进口的java.util.Map;
进口的java.util.HashMap;类ExternalReferenceTest { 公共静态无效的主要（字串[] args）{
  尝试{   InputStream的INP =新的FileInputStream（workbook.xlsx）;
   工作簿WB = WorkbookFactory.create（INP）;   苫布苫布= wb.getSheetAt（0）;   鳞次栉比= sheet.getRow（0）;
   如果（行== NULL）行= sheet.createRow（0）;   细胞细胞= row.getCell（0）;
   如果（细胞== NULL）细胞= row.createCell（0）;
   cell.setCellValue（123.45）;   细胞= row.getCell（1）;
   如果（细胞== NULL）细胞= row.createCell（1）;
   cell.setCellValue（678.90）;   细胞= row.getCell（2）;
   如果（细胞== NULL）细胞= 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   //但是我们一定可以评估单个细胞：
   细胞= wb.getSheetAt（0）.getRow（0）.getCell（2）;
   的System.out.println（evaluator.evaluate（单元）.getNumberValue（））; //802.35   FileOutputStream中FILEOUT =新的FileOutputStream（workbook.xlsx）;
   wb.write（FILEOUT）;
   fileOut.flush（）;
   fileOut.close（）;  }赶上（IFEX InvalidFormatException）{
  }赶上（FileNotFoundException异常fnfex）{
  }赶上（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天全站免登陆