pivot datafields as rows

[CarNetSpire]

Hello,

I am creating a pivot table and I am quite happy how that is going.
However, The desired outcome is that all the datafields are shown as rows. In excel itself this is easily doable by dragging the "values" item from the columns area to the rows area.

With Spire.XLS I have found the method `myPivotTable.setShowDataFieldInRow(true);` which sounds exactly like the thing I want, but it doesn't work (or it's not what I was expecting it to do).

Is this possible with Spire.XLS?

Best Regards

(I am using spire-office 4.9.5)

[Lisa.Li]

Hello,

Thanks for your inquire.
Please try to add myPivotTable.calculateData() like the following code snippet. If your issue still exists after trying, to help us further look into it, please share us with your input excel file as well as your full testing code. You could attach them here or send them to us via email (support@e-iceblue.com).

Code: Select all

.......
myPivotTable.setShowDataFieldInRow(true);
myPivotTable.calculateData();
......


Sincerely,
Lisa
E-iceblue support team

[CarNetSpire]

Hi Lisa, thank you for your swift reply.

calling myPivotTable.calculateData() does not work either.

See below my full testing code, and attached is the input excel file.

Best Regards

Code: Select all

public void createPivotWithDataAsRows() throws IOException {
        InputStream templateInputStream = getClass().getClassLoader().getResourceAsStream("templates/sandbox/pivotShowDataInRows_template.xlsx");

        CarNetSpireUtil.applyLicense(); // -> LicenseProvider.setLicenseKey(licenseKey);
        Workbook workbook = new Workbook();
        workbook.loadFromStream(templateInputStream);
        Worksheet worksheet = workbook.getWorksheets().get(0);

        CellRange dataRange = worksheet.getCellRange("A1:D5");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        PivotTable pivotTable = worksheet.getPivotTables().add("Pivot Table", worksheet.getCellRange("F1"), cache);

        // make first column the row field
        IPivotField rowField = pivotTable.getPivotFields().get("aaa");
        rowField.setAxis(AxisTypes.Row);

        // make all other columns as data fields
        Set<String> dataFieldNames = new LinkedHashSet<>(Arrays.asList("bbb", "ccc", "ddd"));
        for (String dataFieldName : dataFieldNames) {
            IPivotField pivotField = pivotTable.getPivotFields().get(dataFieldName);
            pivotTable.getDataFields().add(pivotField, "Sum of "+dataFieldName, SubtotalTypes.Sum);
        }

        // This does not work :(
        pivotTable.setShowDataFieldInRow(true);
        pivotTable.calculateData();

        // save to file
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        workbook.saveToStream(os, FileFormat.Version2016);
        ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());
        File file = new File("target/testOutputFiles/showPivotDataFieldsInRow.xlsx");
        FileUtils.copyInputStreamToFile(is, file);
    }


[CarNetSpire]

Hi again, I just found the solution to my problem.

The method myPivotTable.setShowDataFieldInRow(true) works but ONLY IF called before actually adding the datafields! myPivotTable.calculateData() is not necessary.

Cheers

[Lisa.Li]

Hello,

Thanks for your feedback.
Yes, I did reproduce the behavior that "myPivotTable.setShowDataFieldInRow(true)" didn't work after adding the datafields. This issue has been logged into our bug tracking system with the ticket SPIREXLS-3500. Our Dev team will further investigate and fix it. Once there is any update, we will let you know immediately. Sorry for the inconvenience caused.

Sincerely,
Lisa
E-iceblue support team

[Lisa.Li]

Hello,

Glad to inform that SPIREXLS-3500 has been solved. There is a temporary version(Spire.Xls_4.10.1). You can download and test it firstly. Once the official version is released, we will notify you immediately.

Sincerely,
Lisa
E-iceblue support team

[Lisa.Li]

Hello,

Glad to inform that we just released Spire.Office for Java Version:4.10.5 which includes the fixing of SPIREXLS-3500. Welcome to test it. If there is any further question, just feel free to contact us.

Sincerely,
Lisa
E-iceblue support team