如何使用Apache-POI在Excel中计算公式并避免“保存更改"?留言框? [英] How to calculate Formulas in Excel with Apache-POI and avoid the "save changes" messagebox?
问题描述
我遇到以下问题:我编写了一个Java程序，将值写入xlsx文件.此xlsx文件使用公式计算新值.现在，我想从xlsx文件中获取此计算出的值.问题是，由于没有保存更改，因此我没有将计算出的值输入到Java程序中. 因此，我尝试在xlsx文件中编辑xl/workbook.xml，以摆脱该保存更改问题.那行得通，但是现在我尝试读取的公式单元格返回默认值，而不是计算值.所以atm我有2个选择:我使用
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)
计算如果不手动保存文件则无法读取的值.或者，我编辑xl/workbook.xml以避免手动保存文件，但是公式不会计算值.在两种情况下，我的程序只能读取默认值，而不能读取所需的计算值. 这是我编辑xml的代码:
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();
}
我已经准备好将xl/workbook.xml以外的所有xml文件复制到新创建的xlsx，然后导入已编辑的xl/workbook.xml，但是公式不起作用. ，但是我尝试了很多事情，但又不想删除可能导致我找到可行解决方案的事情.我所做的所有替换的解释:我比较了保存之前和保存之后的xl/workbook.xml，并消除了所有差异.如果现在比较xml，就没有更多区别了.
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.
根据接受的答案，我不再使用上面的代码.我加了
在以下情况下book.getCreationHelper().createFormulaEvaluator().evaluateAll();到我的代码:
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();
现在我得到了Followign异常:
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)
我在fomulas中检查了所有使用的功能此处并且都提供了..可能是由于本地化或其他原因引起的问题吗?
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?
推荐答案
我编写了一个Java程序，该程序将值写入xlsx文件. xlsx文件使用公式计算新值.现在我要得到这个 xlsx文件中的计算值.问题是，我没有 将计算得到的值放入我的Java程序中，因为更改 没有保存. "
"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.
所以我试图编辑xlsx文件中的xl/workbook.xml以摆脱 保存更改问题. "
"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.
workbook.setForceFormulaRecalculation(true)将公式的重新计算委派给Excel的GUI.如果Excel的GUI下次打开文件，则重新计算完成.重新计算后，进行了更改.因此，有关保存更改的问题是合法的.而且，只有 保存更改后(即重新计算的公式结果)，这些结果才会存储在文件中.然后从该文件重新创建Workbook后，只能使用apache poi读取结果.
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.
但是，授权重新计算只是重新计算公式.另一个选项是使用 FormulaEvaluator 和它是 evaluateAll 方法例子.
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();
...
此后，将使用apache poi评估所有公式，并且新的结果将重新读取.
After that all formulas will be evaluated using apache poi and the new results will be rereadable.
当然，仅适用于apache poi的FormulaEvaluator支持的公式.有些不被支持.然后，您不能简单地执行evaluateAll，而只能评估包含受支持公式的单元格.
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.
更新:
如前所述，到目前为止，并非所有公式都受支持.并且apache poi的容忍度不如Excel容忍.
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.
例如，Excel只允许在表示0的公式中不给任何内容作为参数.对于前. =INDEX(A:A,MATCH("Text",Z:Z,))代替=INDEX(A:A,MATCH("Text",Z:Z,0)).但是apache poi FormulaEvaluator不会容忍这一点.
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.
因此，您现在需要调查哪个公式导致错误.因此，evaluateAll而是按照公式评估-重新计算工作簿中的所有公式":
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);
}
}
}
}
调试System.out.println应该告诉您哪个单元导致了问题.
There the debug System.out.println should tell you which cell causes the problem.
这篇关于如何使用Apache-POI在Excel中计算公式并避免“保存更改"?留言框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
