How to export all data from JavaScript Pivot Table including collapsed field members?

Introduction

When working with a pivot table, you can export the data as PDF, Excel, or CSV documents. However, in certain scenarios, you may want to export all the underlying data, including the data for the collapsed field members that is not displayed on the user interface (UI). The default behavior of the JavaScript Pivot Table is to export only the data that is visible in the UI. This article will guide you on how to export the entire data from a pivot table, including the data associated with collapsed field members, by using the beforeExport event. Please note that this procedure is applicable only to engine exports and is not suitable for grid exports.

Export all data

To export all data from a pivot table, including the child members of collapsed fields, you can utilize the beforeExport event. This event allows you to manipulate the data before it is exported as PDF, Excel, or CSV documents.

Here is a code example that demonstrates how you can use the beforeExport event:

[index.js]

var pivotObj = new ej.pivotview.PivotView({
    allowExcelExport: true,
    allowPdfExport: true,
    beforeExport: (args) => {
        // Store the currently expanded row/column headers
        let member = pivotObj.dataSourceSettings.drilledMembers;      
        // Expand all fields in the dataSourceSettings to include all child members
        pivotObj.setProperties(
            { dataSourceSettings: { expandAll: true, drilledMembers: []}},
            true
        );
        // Generate the grid data with all members expanded
        pivotObj.engineModule.generateGridData(pivotObj.dataSourceSettings, true)
        // Assign the expanded data to the `dataCollections` property for export
        args.dataCollections = [pivotObj.engineModule.pivotValues];
        // Revert the dataSourceSettings to only show expanded members as before
        pivotObj.setProperties(
            { dataSourceSettings: { expandAll: false, drilledMembers: member}},
            true
        );
    }
});

Here’s a breakdown of how the code works:

1. First, we store the current drilledMembers, which represent the field headers that have been expanded in the Pivot Table UI.
2. Then, within the setProperties method, we set the expandAll property to true and assign an empty array to drilledMembers within the dataSourceSettings. This is to ensure that all records, including those that were collapsed, will be considered for export. We also set the second parameter of setProperties to true to ensure the changes.
3. Next, we call the generateGridData method on the pivotObj.engineModule. This method applies the changes to the data source settings and regenerates the table data accordingly.
4. Afterwards, we retrieve the expanded data from the pivotObj.engineModule.pivotValues property and assign it to the dataCollections property. This will export the entire data set from a pivot table, including the data associated with collapsed field members.
5. As a final step, we set expandAll back to false and restore the original drilledMembers. This action ensures that the appearance and state of the pivot table in the user interface remain unchanged after the export process is complete.

The following screenshot illustrates the difference between a pivot table and its exported documents, such as PDF, Excel, or CSV,

Screenshots

Pivot Table

PDF Export

Excel Export

CSV Export

For a practical example of this code in action, you can refer to the following Sample in Stackblitz.

Conclusion

I hope you enjoyed learning how to export all data from JavaScript Pivot Table including collapsed field members.

You can also refer to our JavaScript Pivot Table feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Pivot Table example to understand how to create and manipulate data.

For current customers, you can check out our components on the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!