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();
workbook.xml, edit it, and merge it back to the xlsx. Do you know a proper way to do that? – Sculptress