计算公式

计算公式

关注
发信
关注(28)粉丝(399)

如何使用 Apache-POI 在 Excel 中计算公式并避免“保存更改"消息框?

本文介绍了如何使用 Apache-POI 在 Excel 中计算公式并避免“保存更改"消息框?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我遇到了以下问题:我编写了一个将值写入 xlsx 文件的 Java 程序.此 xlsx 文件使用公式计算新值.现在我想从 xlsx 文件中获取这个计算值.问题是，我没有将计算出的值输入到我的 Java 程序中，因为没有保存更改.所以我尝试编辑 xlsx 文件中的 xl/workbook.

workbook.setForceFormulaRecalculation(true)

计算在不手动保存文件的情况下无法读取的值.或者我编辑 xl/workbook.

 public void edit0){zos.write(buffer, 0, len);}//}zos.flush();zos.closeEntry();}File excel = new File("D:\\Excels\\SO_Berechnung_nosave.xlsx");FileInputStream fis = new FileInputStream(excel);XSSFWorkbook book = new XSSFWorkbook(fis);FileOutputStream fos = new FileOutputStream("D:\\Excels\\SO_Berechnung_nosave.xlsx");book.setForceFormulaRecalculation(true);book.write(fos);fis.close();fos.flush();fos.close();for(Enumeration e = zipFile.entries(); e.hasMoreElements();){System.out.println("???????????????????????????");ZipEntry entryIn = (ZipEntry) e.nextElement();if(entryIn.getName().equalsIgnoreCase("xl/workbook.";String rFirstLine = "<?";String replaceCalcId = "";//2147483647 152511if(s.contains(searchCalcId)){s = s.replaceAll(searchCalcId, replaceCalcId);System.out.println("calcId old:" + searchCalcId);System.out.println("calcId new:" + replaceCalcId);}//替换备用String searchAlternateContent = "";String replaceAlternateContent = "
我已经尝试将除 xl/workbook.
更新:
根据接受的答案，我不再使用上面的代码.我补充说book.getCreationHelper().createFormulaEvaluator().evaluateAll(); 在以下上下文中添加到我的代码中:
 FileOutputStream fos = new FileOutputStream(path);book.setForceFormulaRecalculation(true);//XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) book);book.getCreationHelper().createFormulaEvaluator().evaluateAll();book.write(fos);fis.close();fos.flush();fos.close();
现在我得到了以下异常:
线程AWT-EventQueue-0"中的异常java.lang.RuntimeException: Invalid ValueEval type传递给转换:(类org.apache.poi.ss.formula.eval.MissingArgEval)在 org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValue(MultiOperandNumericFunction.java:219)在 org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValues(MultiOperandNumericFunction.java:179)在 org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.getNumberArray(MultiOperandNumericFunction.java:128)在 org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.evaluate(MultiOperandNumericFunction.java:90)在 org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)在 org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:540)在 org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:303)在 org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:245)在 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:268)在 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:155)在 org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:335)在 org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:326)在 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:256)在 XLSXEditor.searchWriter(XLSXEditor.java:218)在 Main.fillTable(Main.java:962)在 Main.btShowActionPerformed(Main.java:715)在 Main.access$900(Main.java:25)在 Main$11.actionPerformed(Main.java:402)在 javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)在 javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)在 javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)在 javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)在 javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)在 java.awt.Component.processMouseEvent(Component.java:6533)在 javax.swing.JComponent.processMouseEvent(JComponent.java:3324)在 java.awt.Component.processEvent(Component.java:6298)在 java.awt.Container.processEvent(Container.java:2236)在 java.awt.Component.dispatchEventImpl(Component.java:4889)在 java.awt.Container.dispatchEventImpl(Container.java:2294)在 java.awt.Component.dispatchEvent(Component.java:4711)在 java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)在 java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)在 java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)在 java.awt.Container.dispatchEventImpl(Container.java:2280)在 java.awt.Window.dispatchEventImpl(Window.java:2746)在 java.awt.Component.dispatchEvent(Component.java:4711)在 java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)在 java.awt.EventQueue.access$500(EventQueue.java:97)在 java.awt.EventQueue$3.run(EventQueue.java:709)在 java.awt.EventQueue$3.run(EventQueue.java:703)在 java.security.AccessController.doPrivileged(Native Method)在 java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)在 java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)在 java.awt.EventQueue$4.run(EventQueue.java:731)在 java.awt.EventQueue$4.run(EventQueue.java:729)在 java.security.AccessController.doPrivileged(Native Method)在 java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)在 java.awt.EventQueue.dispatchEvent(EventQueue.java:728)在 java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)在 java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)在 java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)在 java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)在 java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)在 java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
我检查了我在公式中使用的所有函数这里 并且它们都提供.. 是否有问题，由本地化或其他原因引起?
 解决方案 
到目前为止，您的假设是正确的.
"所以我尝试编辑 xlsx 文件中的 xl/workbook.
但现在你走错了路.
workbook.setForceFormulaRecalculation(true) 将公式的重新计算委托给 Excel 的 GUI.如果 Excel 的 GUI 下次打开文件，则重新计算完成.重新计算时，发生了变化.所以关于保存更改的问题是合法的.并且只有在保存更改后，即重新计算的公式结果，这些结果才会存储在文件中.然后，只有在从该文件新建 Workbook 后，才能使用 apache poi 读取结果.
但是委托重新计算只是一个选项>重新计算公式.另一种选择是使用 FormulaEvaluator 和这是 evaluateAll 方法示例.
...workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();...

此后，所有公式都将使用 apache poi 进行评估，新结果将可重新读取.

当然，这仅适用于 apache poi 的 FormulaEvaluator 支持的公式.有一些不受支持.那么你不能简单地执行 evaluateAll 而必须只评估包含支持公式的单元格.

在这种情况下，关于公式评估的整章可能会引起关注.

更新:

如前所述，到目前为止，并非所有公式都受支持.而且 apache poi 不像 Excel 那样宽容.

根据问题的更新部分的错误，有一个MissingArgEval 又名遗漏的参数"，用在通常不应有此类遗漏参数的公式中.

例如，Excel 允许在表示 0 的公式中不提供任何参数作为参数.例如.=INDEX(A:A,MATCH("Text",Z:Z,)) 而不是 =INDEX(A:A,MATCH("Text",Z:Z,0)).但是 apache poi FormulaEvaluator 不会容忍这个.

所以您现在需要调查导致错误的公式.因此，evaluateAll 对单元格进行循环以进行评估，如 中所述公式评估 - 重新计算工作簿中的所有公式":

FormulaEvaluator evaluator = book.getCreationHelper().createFormulaEvaluator();对于(工作表:书){for (Row r : sheet) {对于(单元格 c:r){if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {System.out.println(c.getAddress() + ":" + c.getCellFormula());evaluator.evaluateFormulaCell(c);}}}}

调试 System.out.println 应该告诉你是哪个单元导致了问题.

I've got the following Problem: I wrote a Java programm that writes values into a xlsx file. This xlsx file calculates new values using Formulas. Now I want to get this calculated Values out of the xlsx file. The Problem is, that I don't get the calculated values into my Java programm, because changes aren't saved.So I tryed to edit the xl/workbook.

workbook.setForceFormulaRecalculation(true)

to calculate Values that i can't read without manually saving the file. Or I edit the xl/workbook.

 public void edit

I allready tryed to copy all

Update:

According to the accepted answer, I don't use the code above anymore. And i addedbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); to my code in the following context:

 FileOutputStream fos = new FileOutputStream(path);
 book.setForceFormulaRecalculation(true);
 //XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) book);
 book.getCreationHelper().createFormulaEvaluator().evaluateAll();
 book.write(fos);
 fis.close();
 fos.flush();
 fos.close();

And now i get the followign Exception:

Exception in thread "AWT-EventQueue-0" java.lang.RuntimeException: Invalid ValueEval type passed for conversion: (class org.apache.poi.ss.formula.eval.MissingArgEval)
at org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValue(MultiOperandNumericFunction.java:219)
at org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValues(MultiOperandNumericFunction.java:179)
at org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.getNumberArray(MultiOperandNumericFunction.java:128)
at org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.evaluate(MultiOperandNumericFunction.java:90)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:540)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:303)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:245)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:268)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:155)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:335)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:326)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:256)
at XLSXEditor.searchWriter(XLSXEditor.java:218)
at Main.fillTable(Main.java:962)
at Main.btShowActionPerformed(Main.java:715)
at Main.access$900(Main.java:25)
at Main$11.actionPerformed(Main.java:402)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6533)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6298)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4889)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2746)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

I checked all functions I use in my fomulas here and they are all offered.. Could there be a problem, caused by the localization or something?

解决方案

Until now your assumptions are correct.

But now you are on the wrong way.

The workbook.setForceFormulaRecalculation(true) delegates the recalculation of formulas to Excel's GUI. The recalculation is done if Excel's GUI is opening the file next time. When recalculated, changes were made. So the question about save the changes is legit. And only after saving the changes, which are the recalculated formula results, those results will be stored in the file. And then the results will only be readable using apache poi after new creating a Workbook from that file.

But delegating the recalculation is only one option for recalculating formulas. The other option is using a FormulaEvaluator and it's evaluateAll method for example.

...
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
...

After that all formulas will be evaluated using apache poi and the new results will be rereadable.

Of course that only works for formulas which are supported by apache poi' s FormulaEvaluator. There are some which are not supported. Then you cannot simply do evaluateAll but must only evaluate the cells which contains supported formulas.

In that context the whole chapter about Formula Evaluation may be of interest.

Update:

As said already not all formulas are supported until now. And apache poi is not as tolerant as Excel will be.

According to the error in the Update part of the question, there is a MissingArgEval aka "a missed argument" used in a formula which normally should not have such missed arguments.

For example Excel tolerates simply giving nothing as a parameter in a formula where 0 is meant. For ex. =INDEX(A:A,MATCH("Text",Z:Z,)) instead of =INDEX(A:A,MATCH("Text",Z:Z,0)). But the apache poi FormulaEvaluator will not tolerate this.

So you now needs investigating which formula causes the error. So instead evaluateAll do looping over the cells to evaluate as described in Formula Evaluation - "Re-calculating all formulas in a Workbook":

FormulaEvaluator evaluator = book.getCreationHelper().createFormulaEvaluator();
for (Sheet sheet : book) {
    for (Row r : sheet) {
        for (Cell c : r) {
            if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
System.out.println(c.getAddress() + ":" + c.getCellFormula());
                evaluator.evaluateFormulaCell(c);
            }
        }
    }
}

There the debug System.out.println should tell you which cell causes the problem.

这篇关于如何使用 Apache-POI 在 Excel 中计算公式并避免“保存更改"消息框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

07-30 19:57
Powered by LMLPHP ©2024 计算公式 0.036059