当前位置: 首页 > 知识库问答 >
问题:

如何保存Excel与公式在Apache Poi?

黄聪
2023-03-14

如果有一个单元格带有公式并保存文件,那么在手动关闭文件后,excel将被要求保存更改?如何从程序中完全保存文件,因为如果不保存,我无法通过读取文件获得正确的值。

当前文件保存:

        File file = new File("Test.xlsx");
        FileOutputStream out;
        try {
            out = new FileOutputStream(file);
            workbook.write(out);
            workbook.close();
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

公式更新:

        XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
        workbook.setForceFormulaRecalculation(true);

公式:

XSSFCell irrCell = row1.createCell(0);
irrCell.setCellFormula("IFERROR(IRR(A2:C2),0)");

文件读取:

        try {
            workbook = new XSSFWorkbook(file);
            System.out.println(workbook.getSheetAt(0).getRow(0).getCell(0).getNumericCellValue());
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

单元格值:-20000000,-10002399000

程序公式单元格值:0,文件公式单元格值:-0653687014

共有1个答案

云韬
2023-03-14

这是Microsoft不遵守自己规则的情况之一。IRR函数告诉我们:

Microsoft Excel使用迭代技术计算内部收益率。从guess开始,IRR循环计算,直到结果精确到0.00001%以内。如果IRR在20次尝试后找不到有效的结果,那么#NUM!返回错误值。

在大多数情况下,你不需要为内部收益率的计算提供猜测。如果省略猜测,则假定为0.1(10%)。

这正是apache poiIRR函数实现的。这就是为什么它会为您的数据示例获取#NUM!错误。但是Microsoft似乎做了一些不同的事情。

使用公式IRR(A2:C2,-0.7),它有一个-0.7的给定猜测,同样apache poi能够评估IRR公式。

为了解决这个问题,我们可以做IRR函数描述还告诉我们的事情:

如果IRR给出#NUM!错误值,或者如果结果与预期不符,请使用不同的“猜测”值重试。

因此,尝试从-1.0到1.0,以0.1的步长进行猜测,直到得到一个值而不是“NUM!”错误

例:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

class ExcelEvaluateIRRFunction {
 
 public static void main(String[] args) throws Exception {
  
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx")); String filePath = "./ExcelNew.xlsx";
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);
  Row row = sheet.getRow(1); if (row == null) row = sheet.createRow(1);
  row.createCell(0).setCellValue(-20000000d);
  row.createCell(1).setCellValue(-1000d);
  row.createCell(2).setCellValue(2399000d);

  row = sheet.getRow(0); if (row == null) row = sheet.createRow(0);
  Cell cell = row.createCell(0);

  CellValue evaluatedValue = null;
  for (int i = -10; i < 10; i++) {
   double guess = i/10d;
   System.out.println(guess);
   cell.setCellFormula("IRR(A2:C2," + guess + ")");
   evaluator.clearAllCachedResultValues();
   evaluatedValue = evaluator.evaluate(cell);
   System.out.println(evaluatedValue);
   if (evaluatedValue.getCellType() != CellType.ERROR) break;
  }

  evaluator.evaluateFormulaCell(cell);
  System.out.println(cell.getNumericCellValue());

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close() ;
  workbook.close();
 }
}

另请参见poi返回NaN中的IRR,但Excel中的正确值,以了解相同问题的另一个示例。

 类似资料:
  • 我使用下面的openssl命令将公钥存储到。pem文件。 但是当我尝试使用此命令时,它将整个证书信息存储在mypubkey.pem文件中。 我已经看到我可以保存我的公钥使用 但这是一个错误。我不能使用“

  • 我已经实现了下面的脚本,但无法保存相同的UTF-8格式。 如何保存转换的CSV从Excel在UTF-8 CSV格式使用VBScript?

  • 问题内容: 我想在excel电子表格中阅读python / pandas,但使用公式而不是单元格结果。 例如,如果单元格A1为25,单元格B1 == A1,我希望数据框显示: 现在它显示: 我该怎么办? 问题答案: 开箱即用提供此功能。看到这里和这里。一个例子:

  • 我有两个excel表(例如:Sheet1和Shee2),表1单元格A1中的公式如下: 此代码将在单元格A1中的Sheet2中搜索值“YES”,并根据值“YES”是否存在而在Sheet1,A1中写入TRUE或FALSE。 我遇到的问题是,我希望公式不仅搜索A1,而且搜索表2中的A1到A3,如果任意两个单元格包含值“是”,那么公式应该返回“true”,否则返回“false”。 我将非常感谢你在这方面的

  • 我使用NPOI dll在C#中对excel表进行genrating。当我以编程方式在某个单元格上应用公式并导出excel时,在excel表的保护模式下,所有具有公式的单元格都显示为“0”值。但是当我编辑这个excel时,所有的公式都能在这些单元格上正常工作。是否有任何解决方案,从应用公式也可以工作在保护模式?

  • 问题内容: 我正在从UI发送对象。将使用对现有子项的引用来创建该对象。 这是这种关系的简单说明。 保存该对象时,Hibernate给我“ org.hibernate.TransientPropertyValueException:对象引用了一个未保存的瞬态实例”。 我根本不需要改变孩子,所以不必拯救孩子。只需要将孩子的ID保存在父母的表中。 我尝试使用一些CascadeType,但没有一个起作用。