如何使用 Apache-POI 在 Excel 中计算公式并避免“保存更改"消息框? [英] How to calculate Formulas in Excel with Apache-POI and avoid the "save changes" messagebox?

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

问题描述

我遇到了以下问题:我编写了一个将值写入 xlsx 文件的 Java 程序.此 xlsx 文件使用公式计算新值.现在我想从 xlsx 文件中获取这个计算值.问题是,我没有将计算出的值输入到我的 Java 程序中,因为没有保存更改.所以我尝试编辑 xlsx 文件中的 xl/workbook.xml 以摆脱保存更改问题.这有效,但现在我尝试读取的公式单元格返回默认值而不是计算值.所以 atm 我有 2 个选择:我使用

workbook.setForceFormulaRecalculation(true)

计算在不手动保存文件的情况下无法读取的值.或者我编辑 xl/workbook.xml 以避免手动保存文件,但是公式不计算值..在这两种情况下,我的程序只能读取默认值而不是我想要的计算值.这是我编辑 xml 的代码:

 public void editXML(String path) 抛出 FileNotFoundException, IOException{ZipFile zipFile = new ZipFile(path);最终 ZipOutputStream zos = new ZipOutputStream(new FileOutputStream("D:\\Excels\\SO_Berechnung_nosave.xlsx"));for(Enumeration e = zipFile.entries(); e.hasMoreElements();){ZipEntry entryIn = (ZipEntry) e.nextElement();//if(!(entryIn.getName().equalsIgnoreCase("xl/workbook.xml")))){System.out.println(entryIn.getName());zos.putNextEntry(entryIn);InputStream is = zipFile.getInputStream(entryIn);字节[]缓冲区=新字节[4096];内里;while((len = (is.read(buffer)))>0){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.xml")){System.out.println("找到正确的条目和工作簿:现在将更改XML");zos.putNextEntry(new ZipEntry("xl\\workbook.xml"));System.out.println("找到正确的条目和工作簿:现在将更改XML");InputStream is = zipFile.getInputStream(entryIn);字节[]缓冲区=新字节[2048];内里;while(is.read(buffer) >= 0){字符串 s = 新字符串(缓冲区);//添加独立是String sFirstLine = "";String rFirstLine = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>";if(s.contains(sFirstLine)){s = s.replace(sFirstLine, rFirstLine);System.out.println("firstLine 旧:" + sFirstLine);System.out.println("firstLine new:" + rFirstLine);}//替换错误的路径String sPath = "\\Empty";字符串 rPath = "";if(s.contains(sPath)){s = s.replaceAll(Pattern.quote(sPath), Matcher.quoteReplacement(rPath));System.out.println("旧路径:" + sPath);System.out.println("新路径:" + rPath);}//替换文件版本String searchFileVersion = "/main\"><fileVersion appName=\"xl\" lastEdited=\"6\" minimumEdited=\"6\" rupBuild=\"14420\"/>";//我知道它的 2 倍相同String replaceFileVersion = "/main\"><fileVersion appName=\"xl\" lastEdited=\"6\" minimumEdited=\"6\" rupBuild=\"14420\"/>";//rup构建应该是正确的if(s.contains(searchFileVersion)){s = s.replaceAll(searchFileVersion, replaceFileVersion);System.out.println("旧文件版本:" + searchFileVersion);System.out.println("新文件版本:" + replaceFileVersion);}//替换calcIdString searchCalcId = "";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 = "";if(s.contains(searchAlternateContent)){s = s.replaceAll(searchAlternateContent, replaceAlternateContent);System.out.println("AlternateContent old:" + searchAlternateContent);System.out.println("AlternateContent new:" + replaceAlternateContent);}//idk 如果这有影响...String searchXmlns = "mc:Ignorable=\"x15\" "+ "xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" "+ "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" "+ "xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" "+ "xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\">";String replaceXmlns = "xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" "+ "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" "+ "xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" "+ "mc:Ignorable=\"x15\" "+ "xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\">";if(s.contains(searchXmlns)){s = s.replaceAll(searchXmlns, replaceXmlns);System.out.println("AlternateContent old:" + searchXmlns);System.out.println("AlternateContent new:" + replaceXmlns);}//替换最后一行String swb = "</workbook";String rWb = "</workbook>";if(s.contains(sWb)){s = s.replaceAll(sWb, rWb);System.out.println("旧工作簿:" + swb);System.out.println("新工作簿:" + rWb);}System.out.println("");System.out.println(s);System.out.println("");len = s.trim().length();缓冲区 = s.getBytes();zos.write(buffer, 0, (len < buffer.length) ? len : buffer.length);}}}zos.flush();zos.closeEntry();zos.close();}

我已经尝试将除 xl/workbook.xml 之外的所有 xml 文件复制到新创建的 xlsx 中,然后导入编辑过的 xl/workbook.xml 但随后公式不起作用.. 为那个意大利面条代码,但我尝试了很多东西,不想删除可能导致我找到可行解决方案的东西.我所做的所有替换的解释:我在保存之前和保存之后比较了 xl/workbook.xml 并消除了所有差异.如果我现在比较 xml,就没有更多区别了.

更新:

根据接受的答案,我不再使用上面的代码.我补充说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)

我检查了我在公式中使用的所有函数这里 并且它们都提供.. 是否有问题,由本地化或其他原因引起?

解决方案

我写了一个 Java 程序,将值写入一个 xlsx 文件.这个xlsx 文件使用公式计算新值.现在我想得到这个从 xlsx 文件中计算出的值.问题是,我不将计算值放入我的 Java 程序中,因为更改没有保存."

到目前为止,您的假设是正确的.

<块引用>

"所以我尝试编辑 xlsx 文件中的 xl/workbook.xml 以摆脱保存更改问题."

但现在你走错了路.

workbook.setForceFormulaRecalculation(true) 将公式的重新计算委托给 Excel 的 GUI.如果 Excel 的 GUI 下次打开文件,则重新计算完成.重新计算时,发生了变化.所以关于保存更改的问题是合法的.并且只有在保存更改后,即重新计算的公式结果,这些结果才会存储在文件中.然后,只有在从该文件新建 Workbook 后,才能使用 apache poi 读取结果.

但是委托重新计算只是一个选项>重新计算公式.另一种选择是使用 FormulaEvaluator 和这是 evaluateAll 方法示例.

<预><代码>...workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();...

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

当然,这仅适用于 apache poiFormulaEvaluator 支持的公式.有一些不受支持.那么你不能简单地执行 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.xml in the xlsx file to get rid of that save changes question. That works, but now the formula cells that I try to read return a default value and not the calculated value. So atm I have 2 options: I use

workbook.setForceFormulaRecalculation(true)

to calculate Values that i can't read without manually saving the file. Or I edit the xl/workbook.xml to avoid manually saving the file, but then the formulas don't calculate values.. in both cases my programm can just read default values and not the calculated ones that I want.. Here is my code to edit the xml:

 public void editXML(String path) throws FileNotFoundException, IOException{

    ZipFile zipFile = new ZipFile(path);
    final ZipOutputStream zos = new ZipOutputStream(new FileOutputStream("D:\\Excels\\SO_Berechnung_nosave.xlsx"));
    for(Enumeration e = zipFile.entries(); e.hasMoreElements();){
        ZipEntry entryIn = (ZipEntry) e.nextElement();
//    if(!(entryIn.getName().equalsIgnoreCase("xl/workbook.xml"))){
            System.out.println(entryIn.getName());
            zos.putNextEntry(entryIn);
            InputStream is = zipFile.getInputStream(entryIn);
            byte[] buffer = new byte[4096];
            int len;
            while((len = (is.read(buffer)))>0){
                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.xml")){
            System.out.println("RIGHT ENTRY FOUND AND WORKBOOK:XML WILL BE CHANGED NOW");
            zos.putNextEntry(new ZipEntry("xl\\workbook.xml"));
            System.out.println("RIGHT ENTRY FOUND AND WORKBOOK:XML WILL BE CHANGED NOW");
            InputStream is = zipFile.getInputStream(entryIn);
            byte[] buffer = new byte[2048];
            int len;
            while(is.read(buffer) >= 0){
                String s = new String(buffer);
                //Add standallone yes
                String sFirstLine = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
                String rFirstLine = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>";
                if(s.contains(sFirstLine)){
                    s = s.replace(sFirstLine, rFirstLine);
                    System.out.println("firstLine old: " + sFirstLine);
                    System.out.println("firstLine new: " + rFirstLine);
                }

                //replace wrong path
                String sPath = "\\Empty";
                String rPath = "";
                if(s.contains(sPath)){
                    s = s.replaceAll(Pattern.quote(sPath), Matcher.quoteReplacement(rPath));
                    System.out.println("path old: " + sPath);
                    System.out.println("path new: " + rPath);
                }

                //replace FileVersion
                String searchFileVersion = "/main\"><fileVersion appName=\"xl\" lastEdited=\"6\" lowestEdited=\"6\" rupBuild=\"14420\"/>"; //I know its 2times the same
                String replaceFileVersion =  "/main\"><fileVersion appName=\"xl\" lastEdited=\"6\" lowestEdited=\"6\" rupBuild=\"14420\"/>";//the rup Build should be right
                if(s.contains(searchFileVersion)){
                    s = s.replaceAll(searchFileVersion, replaceFileVersion);
                    System.out.println("fileVersion old: " + searchFileVersion);
                    System.out.println("fileVersion new: " + replaceFileVersion);
                }

                //replace calcId
                String searchCalcId = "<calcPr calcId=\"0\"/>";
                String replaceCalcId = "<calcPr calcId=\"152511\"/>"; //2147483647   152511
                if(s.contains(searchCalcId)){
                    s = s.replaceAll(searchCalcId, replaceCalcId);
                    System.out.println("calcId old: " + searchCalcId);
                    System.out.println("calcId new: " + replaceCalcId);
                }

                //replace Alternate
                String searchAlternateContent = "<mc:AlternateContent>";
                String replaceAlternateContent = "<mc:AlternateContent xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\">";
                if(s.contains(searchAlternateContent)){
                    s = s.replaceAll(searchAlternateContent, replaceAlternateContent);
                    System.out.println("AlternateContent old: " + searchAlternateContent);
                    System.out.println("AlternateContent new: " + replaceAlternateContent);
                }
                //idk if this has impact...
                String searchXmlns = "mc:Ignorable=\"x15\" "
                        + "xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" "
                        + "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" "
                        + "xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" "
                        + "xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\">";
                String replaceXmlns = "xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" "
                        + "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" "
                        + "xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" "
                        + "mc:Ignorable=\"x15\" "
                        + "xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\">";
                if(s.contains(searchXmlns)){
                    s = s.replaceAll(searchXmlns, replaceXmlns);
                    System.out.println("AlternateContent old: " + searchXmlns);
                    System.out.println("AlternateContent new: " + replaceXmlns);
                }

                //replace last line
                String sWb = "</workbook";
                String rWb = "</workbook>";
                if(s.contains(sWb)){
                    s = s.replaceAll(sWb, rWb);
                    System.out.println("Workbook old: " + sWb);
                    System.out.println("Workbook new: " + rWb);
                }

                System.out.println("");
                System.out.println(s);
                System.out.println("");

                len = s.trim().length();
                buffer = s.getBytes();
                zos.write(buffer, 0, (len < buffer.length) ? len : buffer.length);
            }
        }
    }

    zos.flush();
    zos.closeEntry();
    zos.close();


}

I allready tryed to copy all xml files except the xl/workbook.xml to a new created xlsx and then import the edited xl/workbook.xml but then the formulas didn't work.. And sry for that spaghetti-code, but i tryed i lot of things and didn't wanted do delete things that could lead me to a working solution. Explanation of all the replacements I do: I compared the xl/workbook.xml before saving and after saving and eliminated all differences. If I compare the xml's now there are no more differences.

Update:

According to the accepted answer, I don't use the code above anymore. And i added book.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?

解决方案

"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. "

Until now your assumptions are correct.

"So I tryed to edit the xl/workbook.xml in the xlsx file to get rid of that save changes question. "

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 中计算公式并避免“保存更改"消息框?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆