如何摆脱“保存更改？”提示使用Apache POI XSSF创建的xlsx文件 [英] How to get rid of "Save changes?" prompt on xlsx-files created with Apache POI XSSF

问题描述

打开并立即关闭使用Apache POI XSSF创建的xlsx文件后，系统会提示我保存未保存的更改。据我所知，这种情况正在发生，因为我在xlsx文件中使用公式。

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.

根据javadoc，应该通过设置<$ c来绕过$ c> XSSFWorkbook.setForceFormulaRecalculation（true）
但是，这并没有解决问题。

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?

更新：

如上所述此处（https://poi.apache.org/spreadsheet/eval.html#recalculation）我还尝试了另一种方法来手动重新计算但没有成功。即使在保存，重新计算和保存为第二个文件后重新读取文件也不起作用。

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.

更新2：

考虑到接受的答案，我能够通过在上面的SSCCE中添加以下代码行来解决问题：

（请注意，这只是一个快速而肮脏的尝试来解决问题。可能有很多改进）。

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();

推荐答案

问题

问题可能在于MS Excel本身（一旦确定所有公式都已计算并保存在.xlsx文件中）。根据我的测试，Excel将在打开期间重新计算所有公式，如果它发现该文件是由旧版本的Excel或其他应用程序上次保存的（关键是版本号不匹配和/或低于当前版本Excel打开文件）以保持良好的兼容性。

PROBLEM

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.

（使Excel认为.xlsx文件是由相同的Excel版本生成的，以避免重新计算）

(making Excel think that the .xlsx file was generated by the same Excel version to avoid recalculation)

Excel从 workbook.xml中读取所有文件版本控制信息文件位于.xlsx档案中的 xl 目录（.xlsx只是一个压缩档案）。

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 Apache POI生成的文件可能如下所示：

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>

Excel 2010生成的文件如下所示：

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>

完全注意< fileVersion> 标签在POI生成的文件中缺少< calcPr> 标记，其中 calcId 在Excel生成的文件中设置为某个实际值。

Notice the <fileVersion> tag completely missing in POI generated file and <calcPr> tag with calcId set to some real value in Excel generated file.

我能够通过插入相关的< fileVersion>来避免Excel 2010自动公式重新计算（以及恼人的保存更改对话框）。 标记和设置 calcId 等于或大于我当前版本的Excel生成的数字到 workbook.xml 由POI生成。

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.

有关 workbook.xml的更多信息格式可以在 MSDN上找到打开XML SDK文档。

这篇关于如何摆脱“保存更改？”提示使用Apache POI XSSF创建的xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！