Answers

• allan Posts: 64,061Questions: 1Answers: 10,559 Site admin

  February 27

  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 Posts: 21,725Questions: 26Answers: 5,027

  February 27

  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_ Posts: 2Questions: 1Answers: 0

  February 28 edited February 28

  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 Posts: 64,061Questions: 1Answers: 10,559 Site admin

  February 28

  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