How to I get rid of “Save changes?” prompt on xlsx-files created with Apache POI XSSF

Question

I am creating a new .xlsx-File and very time i open it for the first time and close it, this annoying popup comes up. this project should create a Excel-File, looking like something my coworker did by hand and the xml-File I post down there, is from one of his files. My code where i save the file is working fine:

private File save() throws IOException {
    Date now = new Date();
    String userHome = System.getProperty("user.home");
    for (int fCounter = -1;; fCounter++) {
        Path path = Paths.get(MessageFormat.format(PATH_FMT, userHome, now, fCounter));
        try (OutputStream out = Files.newOutputStream(path, StandardOpenOption.CREATE_NEW)) {
            this.workBook.write(out);
            this.workBook.close();
            return path.toFile();
        } catch (FileAlreadyExistsException incrCounterAndRetry) {
        }
    }
}

here an example on how i create the pivot tables (also working fine):

private void createPivotSid(XSSFSheet sheet) {
    XSSFSheet data = workBook.getSheet("SID Table");

    CellReference cr = new CellReference("A1");
    CellReference c1 = new CellReference(0, 0);
    CellReference c2 = new CellReference(data.getPhysicalNumberOfRows() - 1, data.getRow(0).getLastCellNum() - 1);

    AreaReference ar = new AreaReference(c1, c2);
    XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, data);

    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(5).setAxis(STAxis.AXIS_COL);
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(5).addNewItems();
    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(5).getItems().addNewItem()
            .setT(STItemType.DEFAULT);

    pivotTable.getCTPivotTableDefinition().addNewColFields().addNewField().setX(5);

    pivotTable.addRowLabel(11);
    pivotTable.addRowLabel(12);
    pivotTable.addRowLabel(2);
    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 1);
}

This is all working fine but i get this annoying popup. So I googled much and found this question on SO and tried what they suggested. The answer with simply evaluating all Formulas does not work (no formulas here). So i tried the answer which suggests editing the xml-File before opening. Here i run into an problem when creating/setting defined names (not sure what this is, copied from example xml). My code is:

private void setWorkbookXmlContent() {
    workBook.getCTWorkbook().addNewFileVersion();
    workBook.getCTWorkbook().getFileVersion().setAppName("xl");
    workBook.getCTWorkbook().getFileVersion().setLastEdited("5");
    workBook.getCTWorkbook().getFileVersion().setLowestEdited("5");
    workBook.getCTWorkbook().getFileVersion().setRupBuild("9303");

    workBook.getCTWorkbook().getWorkbookPr().unsetDate1904();
    workBook.getCTWorkbook().getWorkbookPr().setDefaultThemeVersion(124226);

    workBook.getCTWorkbook().getBookViews().getWorkbookViewArray(0).setXWindow(480);
    workBook.getCTWorkbook().getBookViews().getWorkbookViewArray(0).setYWindow(105);
    workBook.getCTWorkbook().getBookViews().getWorkbookViewArray(0).setWindowWidth(27795);
    workBook.getCTWorkbook().getBookViews().getWorkbookViewArray(0).setWindowHeight(14310);
    workBook.getCTWorkbook().getBookViews().getWorkbookViewArray(0).unsetActiveTab();

    workBook.getCTWorkbook().addNewDefinedNames().addNewDefinedName().setName("_xlnm._FilterDatabase");
    workBook.getCTWorkbook().addNewDefinedNames().addNewDefinedName().setLocalSheetId(3);
    workBook.getCTWorkbook().addNewDefinedNames().addNewDefinedName().setHidden(false);
    workBook.getCTWorkbook().addNewDefinedNames().addNewDefinedName().setStringValue("SID Q3 2016'!$A$1:$M$81");

    workBook.getCTWorkbook().addNewCalcPr().setCalcId(145621);

}

The xml-File i am trying to achieve looks like this (from Excel file of my coworker):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<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="480" yWindow="45" windowWidth="27795" windowHeight="14370"/></bookViews>
<sheets><sheet name="Overview SID" sheetId="3" r:id="rId1"/><sheet name="Overview Exchange" sheetId="5" r:id="rId2"/><sheet name="Overview Total Cost" sheetId="6" r:id="rId3"/><sheet name="SID Q3 2016" sheetId="1" r:id="rId4"/><sheet name="Exchange Q3 2016" sheetId="2" r:id="rId5"/><sheet name="Total Cost Q3 2016" sheetId="4" r:id="rId6"/></sheets>
<definedNames><definedName name="_xlnm._FilterDatabase" localSheetId="3" hidden="1">'SID Q3 2016'!$A$1:$M$81</definedName></definedNames>
<calcPr calcId="145621"/>
<pivotCaches><pivotCache cacheId="8" r:id="rId7"/><pivotCache cacheId="15" r:id="rId8"/><pivotCache cacheId="21" r:id="rId9"/></pivotCaches>
</workbook>

But what i actually get is this:

<?xml version="1.0" encoding="UTF-8"?>
<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="480" yWindow="105" windowWidth="27795" windowHeight="14310"/></bookViews>
<sheets><sheet name="SID Table" r:id="rId3" sheetId="1"/><sheet name="Exchange Table" r:id="rId4" sheetId="2"/><sheet name="Total Cost Table" r:id="rId5" sheetId="3"/><sheet name="Overview SID" r:id="rId6" sheetId="4"/><sheet name="Overview Exchange" r:id="rId8" sheetId="5"/><sheet name="Overview Total Cost" r:id="rId10" sheetId="6"/></sheets>
<definedNames><definedName localSheetId="3"/></definedNames>
<definedNames><definedName hidden="1"/></definedNames>
<definedNames><definedName>SID Q3 2016'!$A$1:$M$81</definedName></definedNames>
<definedNames><definedName localSheetId="3"/></definedNames>
<calcPr calcId="145621"/>
<pivotCaches><pivotCache cacheId="2" r:id="rId7"/><pivotCache cacheId="3" r:id="rId9"/><pivotCache cacheId="4" r:id="rId11"/></pivotCaches>
</workbook>

Maybe someone has a solution to my problem or can me help with the xml-editing. Any help is appreciated.

UPDATE

I found a way to get rid of the popup (pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setRefreshOnLoad(false);) but now I need to "evaluate" all the pivot tables before saving. Or is it possible to save the workbook, then open the file in a new workbook (this should refresh the pivot tables?) and then save that?

the definedNames don't have name-attribute ... so this looks strange. I would try with the full schemas first. you are not using OSGi by any chance? — kiwiwings, Dec 13 '16 at 12:28
Wild guess. Adding a new pivot object does not update an internal cached cell values. Once you open Excel thinks this is a "dirty" file and evaluates formulas and objects. Cached values are stored to cells. Formula cell has a last calculated value and formula props in a xmlx file. Pivot may act similarly. — Whome, Dec 13 '16 at 12:29
`definedName name="_xlnm._FilterDatabase"` this is generated by excel. — Aelop, Dec 13 '16 at 12:33
"this annoying popup comes up". It is not annoying. Apache poi cannot creating pivot tables properly - means all inclusive the proper pivot-cache. So in reality Excel will create the pivot table while opening the `*.xlsx` file from the not ready done partial things from apache poi. So Excel **has** made changings and so Excel **must** at least asking for saving those changings. — Axel Richter, Dec 13 '16 at 12:35
@AxelRichter is there a way to create the pivot tables programmatic so this popup does not come up? — Aelop, Dec 13 '16 at 12:40
@Aelop: Surely. But not with the current state of apache poi. And I suspect this will not have much priority in further development. You could unzip the `*.xlsx` generated from apache poi and comparing the `\xl\pivotTables\pivotTable*.xml` and the `\xl\pivotCache\pivotCacheDefinition*.xml` and `\xl\pivotCache\pivotCacheRecords*.xml` with the ones created after saving the file from Excel. The differences will show you what needs to be done. — Axel Richter, Dec 13 '16 at 12:51
@AxelRichter i looked through the pivotTable*.xml, pivotCacheDefinition*.xml and pivotCacheRecords*.xml and there i found a field that is called `refreshOnLoad`, which is automatically set to true. if you set it to false with `pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setRefreshOnLoad(false);` the popup is gone, **but** you will have to refresh the pivot tables by hand once you open the excel and want to view them. if there is a way to do this refresh programmatic, this would solve the problem — Aelop, Dec 13 '16 at 13:06
@Aelop: Autorun macro and .xlsm (macro-enabled) file format but then you end up the same situation. Excel asks for saving changes if you programmatically refresh a "hollow" pivot table. You don't have a shortcut, create everything with POI+own custom xml manipulation code. I know this is not an easy route for complex files. — Whome, Dec 13 '16 at 15:27
@Whome i would be totally fine without the xml editing, if i can somehow evaluate the pivot tables before writing and closing the workbook. id actually really appreciate a solution without the xml editing — Aelop, Dec 13 '16 at 15:30

Whome · Answer 1 · 2016-12-13T16:03:47.060

If you are fine with macros you could create an external helper xlsm file and VBA macro to be run once it is opened in Excel application. Use a shell-execute from Java if you want to automate this. User do need to have a full Excel installation.

Private Sub Auto_open()
  // you could foreach loop pivottables array
  // but this example uses a predefined instance names
  Dim wb as workbook
  Dim ws as worksheet

  Set wb = Application.Workbooks.Open("c:\data\mypoifile.xlsx", 3, False, 5, "", "", True, xlWindows, "", False, False, 0, False, True, xlNormalLoad)

  set ws = wb.Worksheets("data1")
  ws.PivotTables("PivotTable1").PivotCache.Refresh
  ws.PivotTables("PivotTable2").PivotCache.Refresh
  set ws = wb.Worksheets("data2")
  ws.PivotTables("PivotTable3").PivotCache.Refresh
  ws.PivotTables("PivotTable4").PivotCache.Refresh

  wb.Close(true) // save+close poi file
  ActiveWorkbook.close(false)
End Sub

Reason you may need to use this kind of hack is POI is just a "simple" excel file format handler working without Excel installation. POI knows nothing about the real Excel runtime UI engine features and many xls file format things happen at UI engine level only.

with my last comment, i didn't mean i want a VBA macro. i meant to have pure java code to complete the task. but thanks for your effort — Aelop, Dec 14 '16 at 07:55

How to I get rid of “Save changes?” prompt on xlsx-files created with Apache POI XSSF

1 Answers1