DataTable plugin-change header while exporting as excel

by Saurabh Misra

HTML

<!DOCTYPE html>
<html>

  <head>
    <title>DataTable plugin-change header while exporting as excel</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.23/b-1.6.5/b-html5-1.6.5/datatables.min.css" />

    <script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.23/b-1.6.5/b-html5-1.6.5/datatables.min.js"></script>
  </head>

  <body>
    <table class="table stripe my-table">
      <thead>
        <tr>
          <th>Name</th>
          <th>Email</th>
          <th>Gender</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>Saurabh Misra</td>
          <td>[email protected]</td>
          <td>Male</td>
        </tr>
        <tr>
          <td>Arpita</td>
          <td>[email protected]</td>
          <td>Female</td>
        </tr>
        <tr>
          <td>Nandini</td>
          <td>[email protected]</td>
          <td>Female</td>
        </tr>
      </tbody>
    </table>
  </body>

</html>

JavaScript

$(document).ready(function() {
  $("table.my-table").DataTable({
    "dom": "lBfrtip",
    "buttons": [{
      "extend": 'excel',
      "exportOptions": {
        "format": {
          "header": function(content, index) {
            // Here 2 is the index of the column whose header name we want to change(0 based)
            return index === 2 ? "Gender" : content;
          }
        }
      }
    }],
    // execute callback when DataTable is completely initialiazed
    "initComplete": function() {
      // Select the column whose header we need replaced using its index(0 based)
      this.api().column(2).every(function() {
        var column = this;
        // Put the HTML of the <select /> filter along with any default options 
        var select = $('<select class="form-control input-sm"><option value="">All</option></select>')
          // remove all content from this column's header and 
          // append the above <select /> element HTML code into it 
          .appendTo($(column.header()).empty())
          // execute callback when an option is selected in our <select /> filter
          .on('change', function() {
            // escape special characters for DataTable to perform search
            var val = $.fn.dataTable.util.escapeRegex(
              $(this).val()
            );

            // Perform the search with the <select /> filter value and re-render the DataTable
            column
              .search(val ? '^' + val + '$' : '', true, false)
              .draw();
          });
        // fill the <select /> filter with unique values from the column's data
        column.data().unique().sort().each(function(d, j) {
          select.append("<option value='" + d + "'>" + d + "</option>")
        });
      });
    },
    // disable sorting on the column with the filter in its header.
    "columnDefs": [{
      targets: [2],
      orderable: false
    }]
  });
});