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