Export with Summary and Pagination

[vijay]

Hello Paramveer,

I am using PQGrid with the latest V11 version.

I am encountering an issue when exporting a grid that has pagination enabled along with a summary column.

For example, the grid contains 250 records and pagination is configured to display 20 records per page. The summary is calculated based on the current page (20 records).

However, when exporting the grid, the output file contains all 250 records, while the summary still reflects only the paginated data instead of the full dataset.

Expectation:
In the exported file, all records should be displayed, and the summary values should be calculated based on the complete dataset (i.e., all records), not just the paginated records.

Kindly check the code below for your reference:

async function exportXlsx() {
    var $t = this.toolbar(),
        hlAlternateRows = $t.find('.alternateRows').prop('checked');

    var blob = await this.exportData({
        format: 'xlsx',
        eachRow: function(row, ri, rowData, rows) {
            if (hlAlternateRows && rows.length % 2 != 0) {
                row.bgColor = "#f0f0f0";
            }
        },
        linkStyle: "text-decoration:underline;color:#0d6efd;",
        skipHiddenCols: $t.find('.xHCols').prop('checked'),
        skipHiddenRows: $t.find('.xHRows').prop('checked'),
        selection: $t.find('.selectedRows').prop('checked') ? 'row' : '',
        render: $t.find('.render').prop('checked')
    });
    pq.saveAs(blob, "pqGrid.xlsx");
}

var colM = [{
        title: "Order ID",
        width: 100,
        dataIndx: "OrderID"
    },
    {
        title: "Customer Name",
        width: 130,
        dataIndx: "CustomerName"
    },
    {
        title: "Product Name",
        width: 190,
        dataIndx: "ProductName"
    },
    {
        title: "Unit Price",
        width: 100,
        dataIndx: "UnitPrice",
        align: "right",
      //format: '$##,###.00',
      summary: {
            type: "sum"
        },
    },
    {
        title: "Quantity",
        width: 100,
        dataIndx: "Quantity",
        align: "right"
    },
    {
        title: "Order Date",
        width: 100,
        dataIndx: "OrderDate"
    },
    {
        title: "Required Date",
        width: 100,
        dataIndx: "RequiredDate"
    },
    {
        title: "Shipped Date",
        width: 100,
        dataIndx: "ShippedDate"
    },
    {
        title: "ShipCountry",
        width: 100,
        dataIndx: "ShipCountry"
    },
    {
        title: "Freight",
        width: 100,
        align: "right",
        dataIndx: "Freight"
    },
    {
        title: "Shipping Name",
        width: 120,
        dataIndx: "ShipName"
    },
    {
        title: "Shipping Address",
        width: 180,
        dataIndx: "ShipAddress"
    },
    {
        title: "Shipping City",
        width: 100,
        dataIndx: "ShipCity"
    },
    {
        title: "Shipping Region",
        width: 110,
        dataIndx: "ShipRegion"
    },
    {
        title: "Shipping Postal Code",
        width: 130,
        dataIndx: "ShipPostalCode"
    }
];
var dataModel = {
    location: "remote",
    dataType: "JSON",
    method: "GET",
    url: "/pro/invoice/get",
    //url: "/invoice.php", //for PHP
    getData: function(dataJSON) {
        return {
            data: dataJSON.data
        };
    }
}

var groupModel = {
    on: true,
   
    grandSummary: true,
   
};

var grid1 = $("div#grid_paging").pqGrid({
    width: 901,
    height: 400,
    collapsible: false,
    pageModel: {
        type: "local",
        rPP: 20,
        strRpp: "{0}",
        strDisplay: "{0} to {1} of {2}"
    },toolbar: {
        items: [
            {
                type: 'button',
                label: "Export",
                icon: 'ui-icon-arrowthickstop-1-s',
                listener: exportXlsx
            }
        ]
    },
    skipExport: function() {

        //update the header titles upon every change in skipHeader property via header popup.
        this.refreshHeader();
    },
    dataModel: dataModel,
    colModel: colM,
    wrap: false,
    hwrap: false,
   groupModel: groupModel,
    //freezeCols: 2,           
    numberCell: {
        show: false,
        resizable: true,
        title: "#"
    },
    title: "Shipping Orders",
    resizable: true
});

[paramvir]

The issue arises because exportData generates the Excel file based on the grid's current state. Even though it exports all 250 row data records (because your paging is local), the summary row is typically pulled from the footer, which the grid has calculated only for the current page (20 records).

To fix this, you must temporarily disable pagination (show all rows) to force the grid to calculate the "Grand Total" for the entire dataset, perform the export, and then restore the pagination settings.

Disable pagination before call to exportData

Code: [Select]

this.option("pageModel.type", "");
this.refreshView();

Re- enable pagination after call to exportData

Code: [Select]

this.option("pageModel.type", "local");
this.refreshView();