How to Keep Specific Row at Bottom When Exporting Table to Excel?

Mansi_

Hello,

I’m currently working on a project where I have a data table displayed on the screen. I’ve implemented some JavaScript code to keep the grand total row fixed at the bottom of the table, ensuring it always appears as the last row when viewed on the screen.

However, I’ve encountered an issue when exporting this data to Excel. The grand total row ends up being placed in the middle of the exported file instead of at the bottom, which is not the desired outcome.

Here’s a screenshot of how the data looks in the table and how it appears in the exported Excel file:

Data shown on my table:

Exported data in excel:

Is there a way to export the data to Excel so that it maintains the same layout as shown in the table, with the grand total row fixed at the bottom? Any guidance or code snippets would be greatly appreciated!

Thank you in advance for your help!

allan

Is your "Grand Total" a row in the table's footer? If it is, it should be exported at the bottom of the table. If that isn't working for you, please link to a test case showing the issue.

Allan

kthorngren

It looks like you are using jQuery or Javascript methods to move the row to the bottom of the table, is this correct? If yes then that means Datatables doesn't know about the move as it has the rows in alphabetical order. Instead use the Absolute Position sorting plugin to have Datatable sort that row to the bottm. Then it should export at the bottom.

If this doesn't help then please provide the test case Allan asked for.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Mansi_

yes i have writen js code to move the row at bottom and then as allan said using footer but the issue is not resolved it is showing correctly in table but in excel it is based on sorted data like this below code:

$.ajax({
    url: '@Url.Action("GetGridTableData", "Grid")',
    data: { 'axis': axis, 'value': value, 'data': data },
    type: 'POST',
    "datatype": "json",
    success: function (response) {
        if (response.success) {
            if ($.fn.DataTable.isDataTable('#dynamicTable')) {
                dataTable.destroy();
            }
            $("#tableHeader").empty();
            $("#tableBody").empty();
            response.headers.forEach(function (header) {
                $('#tableHeader').append('<th class="middle center white">' + header + '</th>');
            });

            let grandTotalRow = null;
            let tableBodyHtml = "";

            response.data.forEach(function (row) {
                var rowHtml = '<tr>';
                row.forEach(function (cell, index) {
                    if (index === 0) {
                        cell = cell === "Period Total" ? "Grand Total" : cell;
                        rowHtml += '<td>' + cell + '</td>';
                    } else {
                        //rowHtml += '<td class="text-right">' + Number(cell).toFixed(2) + '</td>';
                        let formattedValue = Number(cell).toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
                        rowHtml += '<td class="text-right">' + formattedValue + '</td>';
                    }
                });
                rowHtml += '</tr>';

                if (row[0] === "Grand Total") {
                    grandTotalRow = rowHtml;
                } else {
                    tableBodyHtml += rowHtml;
                }
            });

            $("#tableBody").html(tableBodyHtml);
            if (grandTotalRow) {
                $("#tableBody").append(grandTotalRow);
            }

            dataTable = $('#dynamicTable').DataTable({
                destroy: true,
                responsive: false,
                paging: false,
                searching: false,
                ordering: true, // Allow sorting for normal rows
                autoWidth: false,
                info: false,
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'excel',
                        text: '<i class="fa fa-download" title="Export To Excel"></i>',
                        titleAttr: 'Export To Excel',
                        filename: function () {
                            return "Grid Report for " + data + "-" + vdata;
                        },
                        title: function () {
                            return "Grid Report for " + data + " - " + vdata;
                        }
                    }
                ],
                initComplete: function (settings, json) {
                    $("#dynamicTable").wrap("<div style='overflow:auto; width:100%; position:relative;'></div>");
                    $(".dt-buttons").appendTo("#exportButtonContainer");
                },
                footerCallback: function (row, data, start, end, display) {
                    debugger;
                    var api = this.api();
                    var grandTotalRow = null;

                    // Find the "Grand Total" row
                    api.rows().every(function () {
                        var rowData = this.data();
                        if (rowData[0] === "Grand Total") {
                            grandTotalRow = rowData;
                        }
                    });

                    // If Grand Total row exists, move it to the footer
                    if (grandTotalRow) {
                        let footerHtml = "<tr>";
                        grandTotalRow.forEach(function (cell, index) {
                            let formattedValue = index === 0 ? cell : Number(cell).toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 });
                            footerHtml += `<td class="text-right">${formattedValue}</td>`;
                        });
                        footerHtml += "</tr>";

                        $("#tableFooter").html(footerHtml);
                    }
                },
                order: [[0, 'asc']], // Sort normally for other rows
                rowCallback: function (row, data, index) {
                    if (data[0] === "Grand Total") {
                        $(row).addClass("grand-total-row"); // Add class for styling
                    }
                },
                drawCallback: function () {
                    // Move Grand Total row to the footer to prevent sorting on it
                    $("#tableBody tr").each(function () {
                        if ($(this).find("td:first").text().trim() === "Grand Total") {
                            $(this).remove(); // Remove from body
                        }
                    });
                }
            });


        } else {
            alert(response.message);
        }
    },
    error: function () {
        alert('Error fetching data.');
    }
});

Table:

Excel:

please help me to find where i am doing wrong?

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Your Grand total data is still in the table body data. You are using drawCallback to remove it from the display, so yes, I would expect it to still be shown in the Excel output since it is part of the data.

It needs to be removed from the data - it is not a regular row.

You can either include the information in a special property of the JSON reply - perhaps footerData, and then check for that in your callbacks (initComplete in this case) and write it into the footer. Or you can calculate the values client-side.

Allan