DataTables multiple header Export to Excel

Export datatable with multiple / complex headers

by Rajshekar Reddy

HTML

<script src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css">
<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="//cdn.datatables.net/buttons/1.2.4/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.4/js/dataTables.buttons.min.js"></script>
<table id="example" class="table GproSortingColor GproSubmissionPatineViewgrid-hover-color tableWidth table-bordered table-striped patient-report-navigation-datatable dataTable no-footer" role="grid" aria-describedby="GPROAcoLevelPatientViewTable_info"
style="width: 9173px;">
  <thead>
    <tr class="GPROAcoLevelPatientViewTableMeasureHead">
      <th colspan="9"></th>
      <th colspan="4" style="background-color: turquoise;" class="thWidthCare2">CARE-2</th>
      <th colspan="4" style="background-color: lightsalmon">CARE-3</th>
      <th colspan="4" style="background-color: burlywood">CAD-7</th>
      <th colspan="4" style="background-color: darkgrey">DM-Composite</th>
      <th colspan="4" style="background-color: coral">HF-6</th>
      <th colspan="4" style="background-color: burlywood">HTN-2</th>
      <th colspan="4" style="background-color: cadetblue">IVD-2</th>
      <th colspan="4" style="background-color: darksalmon">MH-1</th>
      <th colspan="4" style="background-color: darkseagreen">PREV-5</th>
      <th colspan="4" style="background-color: sandybrown">PREV-6</th>
      <th colspan="4" style="background-color: rosybrown">PREV-7</th>
      <th colspan="4" style="background-color: seagreen">PREV-8</th>
      <th colspan="4" style="background-color: steelblue">PREV-9</th>
      <th colspan="4" style="background-color: palevioletred">PREV-10</th>
      <th colspan="4" style="background-color: lightseagreen">PREV-11</th>
      <th colspan="4" style="background-color: mediumseagreen">PREV-12</th>
      <th colspan="4"...

CSS

body {
  margin: 15px;
}

JavaScript

$(function() {

  function GetColumnPrefix(colIndex) {
    switch (colIndex) {
      case 9:
      case 10:
      case 11:
      case 12:
        return "CARE-2 "; // leave space after the prefix
      case 13:
      case 14:
      case 15:
      case 16:
        return "CARE-3 ";
      case 17:
      case 18:
      case 19:
      case 20:
        return "CAD-7 ";
      default:
        return "";
    }
  }

  var buttonCommon = {
    exportOptions: {
      columns: ':visible',
      format: {
        header: function(data, columnindex, trDOM, node) {
          debugger;
          return GetColumnPrefix(columnindex) + data;
        }
      }
    }
  };

  var table = $('#example').dataTable({
    dom: 'Bfrtip', //change this according to your table structure, B is responsible for holding the button.
    "columnDefs": [{
        "targets": [0],
        "visible": false
      }, {
        "width": "1.5%",
        "targets": 1
      }, {
        "width": "1%",
        "targets": 2
      }, {
        "width": "1%",
        "targets": 3
      }, {
        "width": "1.5%",
        "targets": 4
      }, {
        "width": "1.5%",
        "targets": 6
      }, {
        "width": "2.5%",
        "targets": 5
      }, {
        "width": "1%",
        "targets": 7
      }
      // { "width": "7%", "targets": 8 },
      // { "width": "7%", "targets": 9 },
      // { "width": "8%", "targets": 10 }
    ],
    buttons: [
      $.extend(true, {}, buttonCommon, {
        extend: 'excelHtml5'
      }),
    ]

  });



});