如何使用Apache在Excel中计算公式，并避免“保存更改”消息框？
如何使用Apache在Excel中计算公式，并避免“保存更改”消息框？
提问于 2018-07-24 09:05:38
我遇到了以下问题:我编写了一个将值写入xlsx文件的Java程序。此xlsx文件使用公式计算新值。现在，我希望从xlsx文件中获取这些计算出来的值。问题是，我没有将计算出来的值输入到Java程序中，因为更改没有保存。因此，我尝试编辑xlsx文件中的xl/workbook.xml，以消除这个保存更改问题。这是可行的，但现在我试图读取的公式单元格返回一个默认值，而不是计算值。所以ATM机我有两个选择:我用
workbook.setForceFormulaRecalculation(true)若要计算不手动保存文件无法读取的值，请执行以下操作。或者我编辑xl/workbook.xml以避免手动保存文件，但是公式不计算值。在这两种情况下，我的程序只能读取默认值，而不能读取我想要的计算值。下面是我编辑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，现在就没有更多的区别了。
更新：
根据公认的答案，我不再使用上面的代码了。我在下面的上下文中将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(); 现在我得到了下面的例外：
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 这里中使用的所有函数，它们都是提供的。会不会有什么问题，是因为本地化什么的？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-07-24 09:56:16
“我编写了一个将值写入xlsx文件的Java程序。这个xlsx文件使用公式计算新值。现在我希望从xlsx文件中获取计算值。问题是，我没有将计算值输入到Java程序中，因为没有保存更改。”
到目前为止你的假设是正确的。
“因此，我尝试编辑xlsx文件中的xl/workbook.xml，以消除这个保存更改问题。”
但现在你走错路了。
workbook.setForceFormulaRecalculation(true)将公式的重新计算委托给Excel的图形用户界面。如果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 (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应该告诉您哪个单元格会导致问题。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51494698
复制相关文章
相似问题
腾讯云开发者
