After opening and immediately closing an xlsx-file, created with Apache POI XSSF, I get prompted to save unsaved changes. As far as i can tell, this is happening because I am using formulas within the xlsx-file.
According to the javadoc, this should be bypassed by setting XSSFWorkbook.setForceFormulaRecalculation(true)
However, this doesn't solve the problem.
I also tried to manually recalculate the formulas before saving the file without success.
SSCCE:
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XSSFExample {
public static void main(String[] args) {
// Create workbook and sheet
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet 1");
// Create a row and put some cells in it.
Row row = sheet.createRow((short) 0);
row.createCell(0).setCellValue(5.0);
row.createCell(1).setCellValue(5.0);
row.createCell(2).setCellFormula("A1/B1");
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("XSSFExample.xlsx")) {
wb.setForceFormulaRecalculation(false);
System.out.println(wb.getForceFormulaRecalculation()); // prints "false"
XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wb); // this doesn't seem to make any difference
wb.write(fileOut);
} catch (IOException ex) {
Logger.getLogger(XSSFExample.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
What can I do to create the file and not get prompted to save it after I opened it the first time?
Update:
As stated here (https://poi.apache.org/spreadsheet/eval.html#recalculation) I also tried another method to manually recalculate with no success. Even re-reading the file after save, recalc and save as a second file doesn't work.
Update 2:
Considering the accepted answer, I was able to solve the problem by adding following lines of code to the above SSCCE:
(Please note that this was just a "quick and dirty" attempt to solve the problem. There are probably a lot of improvements possible).
ZipFile zipFile = new ZipFile("XSSFExample.xlsx");
final ZipOutputStream zos = new ZipOutputStream(new FileOutputStream("XSSFExample_NoSave.xlsx"));
for (Enumeration e = zipFile.entries(); e.hasMoreElements();) {
ZipEntry entryIn = (ZipEntry) e.nextElement();
if (!entryIn.getName().equalsIgnoreCase("xl/workbook.xml")) {
zos.putNextEntry(entryIn);
InputStream is = zipFile.getInputStream(entryIn);
byte[] buf = new byte[1024];
int len;
while ((len = (is.read(buf))) > 0) {
zos.write(buf, 0, len);
}
} else {
zos.putNextEntry(new ZipEntry("xl/workbook.xml"));
InputStream is = zipFile.getInputStream(entryIn);
byte[] buf = new byte[1024];
int len;
while (is.read(buf) > 0) {
String s = new String(buf);
String searchFileVersion = "/relationships\"><workbookPr";
String replaceFileVersion = "/relationships\"><fileVersion appName=\"xl\" lastEdited=\"5\" lowestEdited=\"5\" rupBuild=\"9303\"/><workbookPr";
String searchCalcId = "<calcPr calcId=\"0\"/>";
String replaceCalcId = "<calcPr calcId=\"" + String.valueOf(Integer.MAX_VALUE) + "\"/>";
if (s.contains(searchFileVersion)) {
s = s.replaceAll(searchFileVersion, replaceFileVersion);
}
if (s.contains(searchCalcId)) {
s = s.replaceAll(searchCalcId, replaceCalcId);
}
len = s.trim().length();
buf = s.getBytes();
zos.write(buf, 0, (len < buf.length) ? len : buf.length);
}
}
zos.closeEntry();
}
zos.close();
The problem could lie in MS Excel itself (once you are sure that all formulas were calculated and saved in the .xlsx file). According to my testing, Excel will recalculate all formulas during opening if it finds out that the file was last saved by older version of Excel or other application (the point is that the version numbers doesn't match and/or are lower than current version of Excel opening the file) to maintain good compatibility.
(making Excel think that the .xlsx file was generated by the same Excel version to avoid recalculation)
Excel reads all file versioning info from workbook.xml file located in xl directory inside .xlsx archive (.xlsx is just a zipped archive).
workbook.xml file generated by Apache POI could look like this:
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<workbookPr date1904="false"/>
<bookViews><workbookView activeTab="0"/></bookViews>
<sheets>
<sheet name="new sheet" r:id="rId3" sheetId="1"/>
</sheets>
<calcPr calcId="0"/>
</workbook>
The file generated by Excel 2010 looks like this:
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303"/>
<workbookPr defaultThemeVersion="124226"/>
<bookViews><workbookView xWindow="630" yWindow="510" windowWidth="27495" windowHeight="14505"/></bookViews>
<sheets>
<sheet name="new sheet" sheetId="1" r:id="rId1"/>
</sheets>
<calcPr calcId="145621"/>
</workbook>
Notice the <fileVersion> tag completely missing in POI generated file and <calcPr> tag with calcId set to some real value in Excel generated file.
I was able to avoid Excel 2010 automatic formula recalculation (and annoying "Save changes" dialog) by inserting correlated <fileVersion> tag and setting calcId to equal or greater number than the number generated by my current version of Excel to the workbook.xml generated by POI.
More information regarding the workbook.xml format can be found on MSDN Open XML SDK documentation.