Cannot trigger Pivot Table calculation on .xlsx generation

Question

I need some help with an issue I have . I'm first going to describe my problem and then, if you need to, you can read the code below and see the implementation details.

Short description: I generate an Excel workbook containing 2 sheets:

Sheet 1: generic data.

Sheet 2: pivot table over the generic data.

Because some of the Apache provided POI proved buggy I created the Pivot table by accessing the underlying XML structure of the. xlsx document. In this I indicate the Pivot table fields and operations(COUNT in this case).

I am now designing automatic JUnit tests to verify this and this is where I ran into trouble.

Problem: When generating the XLSX containing the document , the pivot table only fills with values after I open it in the client.

I want to ask if there is a way to programmatically trigger the Pivot Table before I open this in the client. Here are 2 sections of the xlsx doc’s underlying xml (pivotTable1.xml):

Before opening in excel client :

<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="A3:D7"/>
<pivotFields count="8">
<pivotField showAll="false" axis="axisPage">
<items count="8">
<item t="default"/>
<item t="default"/>
<item t="default"/>

After opening in excel client

<pivotFields count="8">
<pivotField axis="axisPage" showAll="0">
<items count="2">
<item x="0"/>
<item t="default"/>
</items>

If I try to use the generated excel in a JUnit , before opening it I get a NULL on this: currentWbSheet.getRow(0). This does not happen if I open the file first and then run the test.

Do you know of a way to generate the Pivot Table in such a way that the Pivot table is calculated on generation or how to trigger it from my Java application?

My goal is to compare this generated xlsx against a known ("golden") test xlsx, cell by cell and verify that their contents are identical.

Code samples: Workbook sheet creation:

    private void createSheets(XSSFWorkbook wb) {
    generalDataSheet = wb.createSheet(GENERAL_DATA_SHEET_NAME);
    pivotTableSheet = wb.createSheet(PIVOT_TABLE_SHEET_NAME);
}

Pivot table implementation and use details:

// Pivot table constants:
// where the Table starts with the Report Filter field
public static final String PIVOT_TABLE_SOURCE_START = "A1";
// Where the 2nd part of the pivot table starts with the Sum Values field
public static final String PIVOT_TABLE_DATA_START = "A3:B3";
private static final String PIVOT_TABLE_NAME = " Pivot Table";

private static final int INTERFACE_NAME_CELL_POS = 0;
private static final int PROVIDER_NAME_CELL_POS = 4;
private static final int REQUESTER_NAME_CELL_POS = 6;

…
private void populatePivotTableSheet(List<MyDataSet> list) {
//Set position of the pivot table in sheet
    CellReference pivotTableCellPosition = new CellReference(PIVOT_TABLE_SOURCE_START); 
    //set source area for the pivot table
    AreaReference pivotTableSourceArea = getDefaultPivotTableSourceArea(list);
// create pivot table and set attributespivotTable = new PivotTableMyTools(pivotTableSourceArea, PIVOT_TABLE_NAME);
    pivotTable.createPivotTable(pivotTableSheet, pivotTableCellPosition);
    // set the size of the pivot Table - this is because of a bug in regular API
    pivotTable.setRefField(PIVOT_TABLE_DATA_START);
    pivotTable.addRowLabelsField(PROVIDER_NAME_CELL_POS);
    pivotTable.addColumnLabelsField(REQUESTER_NAME_CELL_POS);
    pivotTable.addReportFilterField(INTERFACE_NAME_CELL_POS);
pivotTable.addSumValuesField(DataConsolidateFunction.COUNT,PROVIDER_NAME_CELL_POS);
    }

I get the source area for the PivotTable like:

private AreaReference getDefaultPivotTableSourceArea(Object linkSetList) {

List< MyDataSet > list = (List< MyDataSet >) DataSetList;
// construct the target area of the Pivot table
// start cell is calculated as for ex: "General data!A2"
CellReference c1 = new CellReference(GENERAL_DATA_SHEET_NAME + "!" + PIVOT_TABLE_SOURCE_START);
String colName = CellReference.convertNumToColString(COLUMN_HEADERS.length - 1);
// end cell is calculated as for ex: "General data!H5"
CellReference c2 = new CellReference(GENERAL_DATA_SHEET_NAME + "!" + colName + (list.size() + 1));

return new AreaReference(c1, c2);
}

I am then using my own Pivot table class to overwrite some of the methods:

    public class PivotTableMyTools extends XSSFPivotTable implements IPivotTableMyTools {

    private XSSFSheet pivotTableSheet; // Sheet displaying information in pivot
    private AreaReference sourceDataArea;
    private XSSFPivotTable pivotTable;
    private int numberOfDataFields;
    private String pivotTableName;

public PivotTableMyTools(AreaReference sourceDataArea, String pivotTableName) {

        this.sourceDataArea = sourceDataArea;
        numberOfDataFields = 0;
        this.pivotTableName = pivotTableName;
    }

@Override
public void createPivotTable(XSSFSheet destinationSheet, CellReference pivotTableCellPosition) {

        pivotTableSheet = destinationSheet;
        pivotTable = pivotTableSheet.createPivotTable(sourceDataArea, pivotTableCellPosition);
        pivotTable.getCTPivotTableDefinition().setName(pivotTableName);
    }

// int fieldID is the ID of the field in the list of fields to be added to
// the report (column headers of the source data area)
@Override
public void addReportFilterField(int fieldID) {

    int lastColIndex = getSourceAreaLastColumnIndex();
    // create new pivot field with Column Specifications
    try {
        // throws index out of bounds
        checkColumnIndexOutOfBounds(fieldID, lastColIndex);
        // add pivot field to PivotTable, lastColindex also indicates the
        // number of columns
        addNewPivotField(fieldID, lastColIndex, STAxis.AXIS_PAGE);
        // Columns labels colField should be added.
        addNewCTPageField(fieldID);

    } catch (IndexOutOfBoundsException e) {
        Activator.logInfo("Column index is out of bounds");
        Activator.logError(e.getMessage());
    }

}



private void addNewCTPageField(int columnIndex) {

        CTPageFields pageFields;
        if (pivotTable.getCTPivotTableDefinition().getPageFields() != null) {
            pageFields = pivotTable.getCTPivotTableDefinition().getPageFields();
        } else {
            pageFields = pivotTable.getCTPivotTableDefinition().addNewPageFields();
        }
        // Set the fld and hier attributes
        CTPageField pageField = pageFields.addNewPageField();
        pageField.setFld(columnIndex);
        pageField.setHier(-1);
        // set the count attribute
        pageFields.setCount(pageFields.sizeOfPageFieldArray());

    }



@Override
    public void addRowLabelsField(int columnIndex) {

        pivotTable.addRowLabel(columnIndex);
    }

    @Override
    public void addColumnLabelsField(int columnIndex) {

        int lastColIndex = getSourceAreaLastColumnIndex();
        // create new pivot field with Column Specifications
        try {
            // throws index out of bounds
            checkColumnIndexOutOfBounds(columnIndex, lastColIndex);
            // add pivot field to PivotTable, lastColindex also indicates the
            // number of columns
            addNewPivotField(columnIndex, lastColIndex, STAxis.AXIS_COL);
            // Columns labels colField should be added.
            addNewCTColField(columnIndex);

        } catch (IndexOutOfBoundsException e) {
            Activator.logInfo("Column index is out of bounds");
            Activator.logError(e.getMessage());
        }
    }

    @Override
    public void addSumValuesField(DataConsolidateFunction function, int fieldID) {

        // pivotTable.addColumnLabel(DataConsolidateFunction.COUNT,
        // PROVIDER_NAME_CELL_POS, "Provider count");
        try {
            CTPivotField pivotField = getPivotField(fieldID);
            pivotField.setDataField(true);
        } catch (IndexOutOfBoundsException e) {
            Activator.logInfo("The selected column is out of current range");
            Activator.logError(e.getMessage());
        }

        addNewCTDataField(fieldID, "Count of Provider");

    }



private void addNewCTDataField(int fieldID, String fieldName) {

        numberOfDataFields++;
        CTDataFields dataFields = pivotTable.getCTPivotTableDefinition().addNewDataFields();
        CTDataField dataField = dataFields.addNewDataField();
        dataField.setName(fieldName);
        dataField.setFld(fieldID);
        dataField.setSubtotal(STDataConsolidateFunction.COUNT);
        dataField.setBaseField(0);
        dataField.setBaseItem(0);
        dataFields.setCount(numberOfDataFields);
    }

    private CTPivotField getPivotField(int fieldID) throws IndexOutOfBoundsException {

        CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
        if (null == pivotFields)
            throw new IndexOutOfBoundsException();
        return pivotFields.getPivotFieldArray(4);
    }

    @Override
    public AreaReference getPivotTableSourceArea() {

        return sourceDataArea;
    }

    @Override
    public int getSourceAreaLastColumnIndex() {

        return (sourceDataArea.getLastCell().getCol() - sourceDataArea.getFirstCell().getCol());
    }

    @Override
    public void setRefField(String pivotTableFieldArea) {

        CTLocation location = pivotTable.getCTPivotTableDefinition().getLocation();
        location.setRef("A3:D7");
    }



/***************** private methods ***********************************/

    private void addNewCTColField(int columnIndex) {

        CTColFields colFields;
        if (pivotTable.getCTPivotTableDefinition().getColFields() != null) {
            colFields = pivotTable.getCTPivotTableDefinition().getColFields();
        } else {
            colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();
        }
        colFields.addNewField().setX(columnIndex);
        colFields.setCount(colFields.sizeOfFieldArray());
    }

    private void addNewPivotField(int columnIndex, int numberOfItems, Enum axisValue) {

        IPivotFieldARTools pivotField = new PivotFieldARTools();
        pivotField.setAxis(axisValue);
        pivotField.createItemsList(numberOfItems);
        pivotField.addToPivotTable(columnIndex, pivotTable);
    }

    private void checkColumnIndexOutOfBounds(int columnIndex, int lastColIndex) throws IndexOutOfBoundsException {

        if (columnIndex > lastColIndex && columnIndex < 0) {
            throw new IndexOutOfBoundsException();
        }
    }

Sergiu Sergiu · Accepted Answer · 2016-04-21T10:32:43

To workaround this issue I will be creating a VBScript application that I can ship together with my plugin and that can be triggered from the plugin.

. The actions this application will perform are : open a excel file received as parameter in the Excel client then save the file and close the client.

. This should trigger the Pivot table generation steps that Excel does and allow me to automatically generate the complete Excel with the Pivot table.

Drawbacks:

. I have to reach outside Java libraries to perform this.

. I must perform the extra steps of: opening/saving and closing the excel client. The code for my wscript workaround looks like this:

excelOpenSave.vbs :

on error resume next

Dim filename
filename = WScript.Arguments(0)

'WScript.Echo filename

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open(filename)
objExcel.Visible = true
objWorkbook.RefreshAll


objExcel.ActiveWorkbook.Save 
objExcel.ActiveWorkbook.Close SaveChanges=True
objExcel.Application.Quit
'WScript.Echo "Finished."


'always deallocate after use...
Set objWorkbook = Nothing
Set objExcel = Nothing
WScript.Quit

My Java code is:

public static void triggerOpenSaveCloseScript(String file) {

    String projPath = System.getProperty("user.dir");
    String script = projPath + "\\Script\\excelOpenSave.vbs";
    String command = "CScript " + script + "  " + file;
    int exitValue = 0;
    try {
        Runtime rt = Runtime.getRuntime();
        Process pr = rt.exec(command);
        exitValue = pr.waitFor(); //wait until script finishes
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InterruptedException e) {
        System.out.println("CScript exited with error: " + exitValue);
        e.printStackTrace();
    }
}

Cannot trigger Pivot Table calculation on .xlsx generation

1 Answers