Want to fix particular row at the bottom in jQuery datatables while exporting data in to the Excel

Question

I want to export data in Excel using jQuery export button for datatables and here I have fixed grand total row at the bottom of table. The issue is: when I do sorting and then I export data into Excel, at that time the grand total also get sorted and it takes place somewhere at the middle of the data set - how can I fix that?

I want the grand total row as the last row always. I tried to remove the row and then appended at the last but it also didn't work here.

function loadDynamicTable(axis, value, data, vdata) {
        $.ajax({
            url: '@Url.Action("GetGridTableData", "Admin")',
            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,
                    //scrollX: true,
                    paging: false,
                    searching: false,
                    ordering: true,
                    autoWidth: false,
                    info: false,
                    order: [],
                    //dom: '<"top"lfB<"spacer">>rt<"bottom"ip><"clear">',
                    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");
                    },
                    drawCallback: function () {
                        var api = this.api();
                        var rows = api.rows().nodes();

                        $(rows).each(function () {
                            if ($(this).find("td:first").text().trim() === "Grand Total") {
                                $(this).appendTo("#tableBody");
                                $(this).addClass("grand-total-row");
                            }
                        });
                    }
                });
            } else {
                alert(response.message);
            }
        },
        error: function () {
            alert('Error fetching data.');
        }
    });
}

Lajos Arpad · Accepted Answer · 2025-02-26 14:01:31Z

0

Found this CodePen code. Let me share it in a snippet too:

jQuery.fn.dataTableExt.oSort["customString-desc"] = function(x, y) {
  return (x !== Infinity) && x < y;
};

jQuery.fn.dataTableExt.oSort["customString-asc"] = function(x, y) {
  return (y !== Infinity) && x > y;
}

jQuery.fn.dataTableExt.oSort["customString-pre"] = function(value) {
  const regex = /^<b>.*<\/b>$/g;
  const found = value.match(regex);
  if (found) {
      return Infinity;
  } else
    return value;
}


jQuery.fn.dataTableExt.oSort["customNumber-desc"] = function(x, y) {
  return (y !== Infinity) && x < y;
};

jQuery.fn.dataTableExt.oSort["customNumber-asc"] = function(x, y) {
  return (y !== Infinity) && x > y;
}

jQuery.fn.dataTableExt.oSort["customNumber-pre"] = function(num) {
  const regex = /^<b>.*<\/b>$/g;
  const found = num.match(regex);
  if (found) {
      return Infinity;
  } else if (num === "\u221E")
      return 2147483647;
  else
    if (num.indexOf('B') >= 0) {
        return num.split(" ")[0] * 1000000000;
    } else if (num.indexOf('M') >= 0) {
        return num.split(" ")[0] * 1000000;
    } else if (num.indexOf('K') >= 0) {
        return num.split(" ")[0] * 1000;
    }
    return num;
}

var dt = $("#myTable").DataTable({
  "scrollX": true,
  "bPaginate": false,
  "aoColumnDefs": [{
    "sType": "customNumber",
    "bSortable": true,
    "aTargets": [1]

  },
  {
    "sType": "customString",
    "bSortable": true,
    "aTargets": [0, 2]
  }, {
    "searchable": false,
    "targets": [0, 1]
  }]
});

body {
  max-width: 400px;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<link href="https://cdn.datatables.net/v/dt/dt-2.2.2/datatables.min.css" rel="stylesheet" integrity="sha384-2vMryTPZxTZDZ3GnMBDVQV8OtmoutdrfJxnDTg0bVam9mZhi7Zr3J1+lkVFRr71f" crossorigin="anonymous">
 
<script src="https://cdn.datatables.net/v/dt/dt-2.2.2/datatables.min.js" integrity="sha384-2Ul6oqy3mEjM7dBJzKOck1Qb/mzlO+k/0BQv3D3C7u+Ri9+7OBINGa24AeOv5rgu" crossorigin="anonymous"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>
<body>
  <table id="myTable">
    <thead>
      <tr>
        <th>Name</th>
        <th>Num</th>
        <th>Col2</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>a</td>
        <td>10 M</td>
        <td>Z</td>
      </tr>
      <tr>
        <td>b</td>
        <td>20 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>c</td>
        <td>1 K</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>d</td>
        <td>0</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>e</td>
        <td>7 B</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>a</td>
        <td>10 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>b</td>
        <td>20 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>c</td>
        <td>1 K</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>d</td>
        <td>0</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>e</td>
        <td>7 B</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>g</td>
        <td>&infin;</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>a</td>
        <td>10 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>b</td>
        <td>20 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>c</td>
        <td>1 K</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>d</td>
        <td>0</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>e</td>
        <td>7 B</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>a</td>
        <td>10 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>b</td>
        <td>20 M</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>c</td>
        <td>1 K</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>d</td>
        <td>0</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>e</td>
        <td>7 B</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>e</td>
        <td>1 B</td>
        <td>Y</td>
      </tr>
      <tr>
        <td>g</td>
        <td>&infin;</td>
        <td>Y</td>
      </tr>
      <tr>
        <td><b>Grand Total</b></td>
        <td><b>1234</b></td>
        <td><b>Y</b></td>
      </tr>
    </tbody>
  </table>
</body>

Applied some edits on my own for the purpose of this snippet.

So basically we can define the oSort of dataTableExt for ["customNumber-asc"] and [customNumber-desc], respectively, you can also preformat it and then you will need to specify sType as customNumber and specify aTargets too

edited Feb 26 at 14:01

answered Feb 26 at 12:53

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MANSI Feb 26 at 12:59

i am still not getting that how do i will fix particular row at the bottom even i have manged to show it at the bottom in table but in excel it is not showing it as a last row

Lajos Arpad Feb 26 at 13:02

@MANSI you are absolutely correct, I was so much focused in solving the custom sorting that I completely forgot about this peculiarity. Let me edit my answer.

Lajos Arpad Feb 26 at 13:10

@MANSI basically in order to solve your problem, I've put the Grand Total into bold and am setting the value to Infinity if it is bold. And I am making an exception for Infinity for descending sort.

MANSI Feb 26 at 13:31

have you seen that when you sort column the grand total also moves which should not happen

Lajos Arpad Feb 26 at 13:46

@MANSI you mean the other columns?

|

Collectives™ on Stack Overflow

Want to fix particular row at the bottom in jQuery datatables while exporting data in to the Excel

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related