​

$(document).ready(function() {

  // API to get column indexif the supplied object key

  jQuery.fn.dataTable.Api.register( 'col.index()', function (data) {

    // Get the table settings;

    var context = this.context[0];

​

    var idx = null;

    // loop through the columns

    context.aoColumns.forEach(function (col, index) {

      // See if column.data setting matches parameter

      if (col.mData == data) {

        idx = col.idx;  // Save index of matching column.data

      }

    });

    return idx;

  } );

  function getHeaderNames(dt) {

    // Gets header names.

    //params:

    //  dt: Datatable API.

    //Returns:

    //  Array of column header names.

    var header = dt.columns().header().toArray();

​

    var names = [];

    header.forEach(function(th) {

     names.push($(th).html());

    });

    return names;

  }

  function getFooterNames(dt) {

    // Gets header names.

    //params:

    //  dt: Datatable API.

    //Returns:

    //  Array of column header names.

    var footer = dt.columns().footer().toArray();

​

    var names = [];

    footer.forEach(function(th) {

     names.push($(th).html());

    });

    return names;

  }

  function buildCols(data) {

    // Builds cols XML.

    //To do: deifne widths for each column.

    //Params:

    //  data: row data.

    //Returns:

    //  String of XML formatted column widths.

    var cols = '<cols>';

    for (i=0; i<data.length; i++) {

      colNum = i + 1;

      cols += '<col min="' + colNum + '" max="' + colNum + '" width="20" customWidth="1"/>';

    }

    cols += '</cols>';

    return cols;

  }

  function buildRow(data, rowNum, styleNum) {

    // Builds row XML.

    //Params:

    //  data: Row data.

    //  rowNum: Excel row number.

    //  styleNum: style number or empty string for no style.

    //Returns:

    //  String of XML formatted row.

    var style = styleNum ? ' s="' + styleNum + '"' : '';

    var row = '<row r="' + rowNum + '">';

​

    for (i=0; i<data.length; i++) {

      colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha

      var cr = colNum + rowNum;

      row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +

              '<is>' +

                '<t>' + data[i] + '</t>' +

              '</is>' +

            '</c>';

    }

    row += '</row>';

    return row;

  }

  function getTableData(groupName, title, button, dt) {

    // Processes Datatable row data to build sheet.

    //Params:

    //  dt: Datatable API.

    //  title: Title displayed at top of SS or empty str for no title.

    //Returns:

    //  String of XML formatted worksheet.

    //console.log('getTableData', groupName);

    var dataSrc = getDataSrc( dt );

    var colIndex = dt.col.index( dataSrc );

    var header = getHeaderNames(dt);

    var rowNum = 1;

    var mergeCells = [];

    var mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();

    var ws = '';

    var selectorModifier = {};

    if (button.exportOptions.hasOwnProperty('modifier') ) {

      selectorModifier = button.exportOptions.modifier;

    }

    ws += buildCols(header);

    ws += '<sheetData>';

​

    // Print button.title

    if (button.title.length > 0) {

      if (button.title === '*') {

        button.title = document.getElementsByTagName("title")[0].innerHTML;

      }

      ws += buildRow([button.title], rowNum, 51);

      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');

      rowNum++;

    }

​

    // Print button.messageTop

    if (button.messageTop.length > 0 && button.messageTop.length != '*') {

      ws += buildRow([button.messageTop], rowNum, 51);

      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');

      rowNum++;

    }

​

    // All rows on one page with group names separating groups

    if ( Array.isArray( groupName ) ) {

​

      if (button.header) {

        ws += buildRow(header, rowNum, 2);

        rowNum++;

      }

​

      var currentGroup = '';

​

      // Loop through each row to append to sheet.    

      table.rows(selectorModifier).every( function ( rowIdx, tableLoop, rowLoop ) {

        var data = this.data();

        // Build ordered list, dt  col index, of object values

        if ( ! Array.isArray( data ) ) {

          // Build temp array

          var temp = new Array( header.length );

          // Insert object into array based on DT column index

          for (const [key, value] of Object.entries(data)) {

            temp.splice(dt.col.index(key), 1, value)

          }

          data = temp;

        }

​

        if ( data[ colIndex ] !== currentGroup ) {

          currentGroup = data[ colIndex ];

          ws += buildRow([currentGroup], rowNum, 51);

          mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');

          rowNum++;

        }          

        // If data is object based then it needs to be converted 

        // to an array before sending to buildRow()

        ws += buildRow(data, rowNum, '');

​

        rowNum++;

      } );

​

    } else {

​

      // Place each group on individual sheets

        if ( title ) {

          ws += buildRow([title], rowNum, 51);

          mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');

          rowNum++;

        }          

​

      if (button.header) {

        ws += buildRow(header, rowNum, 2);

        rowNum++;

      }

​

      // Loop through each row to append to sheet.    

      table.rows(

        function ( idx, data, node ) {

          return data[ dataSrc ] === groupName ?

            true : false;

        },

        selectorModifier

      ).every( function ( rowIdx, tableLoop, rowLoop ) {

        var data = this.data();

        // Build ordered list, dt  col index, of object values

        if ( ! Array.isArray( data ) ) {

          // Build temp array

          var temp = new Array( header.length );

          // Insert object into array based on DT column index

          for (const [key, value] of Object.entries(data)) {

            temp.splice(dt.col.index(key), 1, value)

          }

          data = temp;

        }

        // If data is object based then it needs to be converted 

        // to an array before sending to buildRow()

        ws += buildRow(data, rowNum, '');

​

        rowNum++;

      } );

​

    }

    // Output footer

    if (button.footer) {

      ws += buildRow(getFooterNames(dt), rowNum, 2);

      rowNum++;

    }

​

    // Print button.messageBottom

    if (button.messageBottom.length > 0 && button.messageBottom.length != '*') {

      ws += buildRow([button.messageBottom], rowNum, 51);

      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');

      rowNum++;

    }

​

    mergeCellsElement = '';

​

    if (mergeCells) {

      mergeCellsElement = '<mergeCells count="' + mergeCells.length + '">'+

        mergeCells +

       '</mergeCells>';

    }

    ws += '</sheetData>' + mergeCellsElement;

​

    return ws;

​

  }

  function setSheetName(xlsx, name) {

    // Changes tab title for sheet.

    //Params:

    //  xlsx: xlxs worksheet object.

    //  name: name for sheet.

    if (name.length > 0) {

      var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];

      source.setAttribute('name', name);

    }

  }

  function addSheet(xlsx, groupName, title, name, sheetId, button, dt) {

    //Clones sheet from Sheet1 to build new sheet.

    //Params:

    //  xlsx: xlsx object.

    //  table: table ID.

    //  title: Title for top row or blank if no title.

    //  name: Name of new sheet.

    //  sheetId: string containing sheetId for new sheet.

    //Returns:

    //  Updated sheet object.

    //Add sheet2 to [Content_Types].xml => <Types>

    //============================================

    var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];

    var clone = source.cloneNode(true);

    clone.setAttribute('PartName','/xl/worksheets/sheet' + sheetId + '.xml');

    xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);

    //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships

    //=====================================================================

    var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];

    var clone = source.cloneNode(true);

    clone.setAttribute('Id','rId'+sheetId+1);

    clone.setAttribute('Target','worksheets/sheet' + sheetId + '.xml');

    xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);

    //Add second sheet to xl/workbook.xml => <workbook><sheets>

    //=========================================================

    var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];

    var clone = source.cloneNode(true);

    clone.setAttribute('name', name);

    clone.setAttribute('sheetId', sheetId);

    clone.setAttribute('r:id','rId'+sheetId+1);

    xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);

    //Add sheet2.xml to xl/worksheets

    //===============================

    var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+

      '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+

      getTableData(groupName, title, button, dt) +

      '</worksheet>';

​

    xlsx.xl.worksheets['sheet' + sheetId + '.xml'] = $.parseXML(newSheet);

  }

  function updateSheet1(xlsx, groupName, title, button, dt) {

    //console.log('updateSheet', groupName);

​

    // Get number of columns to remove last hidden index column.

    var numColumns = dt.columns().header().count();

    var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'+

      '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">'+

      getTableData(groupName, title, button, dt) +

      '</worksheet>';

​

    // Get sheet.

    var sheet = xlsx.xl.worksheets['sheet1.xml'] = $.parseXML(newSheet);

​

    if ( Array.isArray( groupName ) ) {

      setSheetName( xlsx, button.sheetName ? button.sheetName : document.getElementsByTagName("title")[0].innerHTML );

    } else {

      setSheetName( xlsx, groupName );

    }

  }

​

  function getDataSrc(dt) {

    // Return the RowGroup dataSrc

    var dataSrc = dt.rowGroup().dataSrc();

​

    // If multi level use only the first level

    if ( Array .isArray( dataSrc ) ) {

      dataSrc = dataSrc[0];

    }

    return dataSrc;

  }

​

​

  var table = $('#example').DataTable({

        "ajax": "/ajax/objects.txt",

        "columns": [

            { "data": "name" },

            { "data": "position" },

            { "data": "office" },

            { "data": "extn" },

            { "data": "start_date" },

            { "data": "salary" }

        ],

    rowGroup: {

      dataSrc: 'office'

    },

    order: [[2, 'asc']],

    dom: 'Bfrtip',

    buttons: [

      {

        extend: 'excelHtml5',

        messageTop: 'This is the top',

        messageBottom: 'This is the bottom',

        footer: true

      },

      {

        extend: 'excelHtml5',

        text: 'RowGroup Excel',

​

        // Options supported by RowGroup customize function

        title: '',

        header: true,

        messageTop: 'This is the top',

        messageBottom: 'This is the bottom',

        sheetName: 'Single Sheet',

        footer: true,

​

        exportOptions: {

          modifier: {

            search: 'applied',

          }

        },

​

​

        customize: function( xlsx, button, dt ) {

​

          //console.log(button);

          multiSheet = true;  // Export all groups in one sheet or multiple

​

          var dataSrc = getDataSrc( dt );

​

          // Get unique values from rowGroup.dataSrc column

          var groupNames = [... new Set( dt.column( dt.col.index( dataSrc) ).data().toArray() )];

          //console.log('Groups:', groupNames);

​

          if (multiSheet) {

            // Export each group in their own sheet

            var sheetId = 1;

​

            // Get row data belonging to each group

            groupNames.forEach(function (groupName, index) {

​

              if (sheetId === 1) {

                // First sheet is already built, just update it

                updateSheet1( xlsx, groupName, groupName, button, dt );

              } else {

                // Create new sheet for remaining groups

                addSheet( xlsx, groupName, groupName, groupName, sheetId, button, dt );

              }

              sheetId++;

​

            });     

​

          } else {

            // All groups shown on one page

            updateSheet1( xlsx, groupNames, '', button, dt );

          }

​

​

        },

      }]

  });

​

​

​

});