​

$(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(rowData, 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 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++;

    }

​

​

​

    if (button.header) {

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

      rowNum++;

    }

​

    // Loop through each row to append to sheet.    

    rowData.forEach(function (data, index) {

​

      // 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)) {

          var rowIdx = dt.col.index(key);

          if (rowIdx !== null) {

            temp.splice(rowIdx, 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, rowData, 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(rowData, title, button, dt) +

      '</worksheet>';

​

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

  }

  function updateSheet1(xlsx, rowData, 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(rowData, title, button, dt) +

      '</worksheet>';

​

    // Get sheet.

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

​

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

​

  }

​

  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;

  }

​

  function customizeRowExport( rowData ) {

    // Manipulate the rows before exporting to Excel

    return rowData;

  }

​

​

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

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

        "columns": [

            { "data": "name" },

            { "data": "position" },

            { "data": "office" },

            { "data": "extn" },

            { "data": "start_date" },

            { "data": "salary" }

        ],

    dom: 'Bfrtip',

    buttons: [

      {

        extend: 'excelHtml5',

        text: 'Custom 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 ) {

​

          // Get the selector modifer for the rows() selection

          var selectorModifier = {};

​

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

            selectorModifier = button.exportOptions.modifier;

          }

          // Get all the row data

          var rowData = dt.rows( selectorModifier ).data().toArray();

​

          rowData = customizeRowExport( rowData );

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

          // Get data from table2

          var table2 = $('#example2').DataTable();

          var rowData = table2.rows( selectorModifier ).data().toArray();

          addSheet(xlsx, rowData, 'My Sheet2', 'Object data', '2', button, table2);

          // Get data from table3

          var table3 = $('#example3').DataTable();

          var rowData = table3.rows( selectorModifier ).data().toArray();

          addSheet(xlsx, rowData, 'My Sheet3', 'Array data', '3', button, table3);

        },

      }]

  });

​

​

    $('#example2').DataTable({

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

        "columns": [

            { "data": "position" },

            { "data": "name" },

            { "data": "office" },

        ],

    });

   $('#example3').DataTable();

​

});