$(document).ready(function() {  

  function isNumeric( n ) {

     return !isNaN(parseFloat(n)) && isFinite(n);

  }

  var dataSet = [

    {name:'Sally Smith', debt:{display: '$100,000', value: 100000, alarm: 'RED'}, position:'Accountant', age:40},

    {name:'Sam Smith',   debt:{display: '$500',     value: 500,    alarm: null}, position:'Accountant', age:50},

    {name:'Fred Smith',  debt:{display: '$800',     value: 800,    alarm: null}, position:'Accountant', age:50},

    {name:'Joe Newbee',  debt:{display: '$300,000', value: 300000, alarm: 'RED'}, position:'Office Worker', age:25}

  ];

​

  function styleCell( cell, cellData ) {

     var highlight = cellData.alarm;

     if ( highlight ) {

        $(cell).addClass( highlight.toLowerCase() + '_cell_highlight' );

     }

  }

  function renderCellValue( data, type, row, meta ) {

     if ( data === null ) { return null; }

     if ( type === 'display' ) {

        return data.display;

     } else if ( type === 'export' ) {

        // set expected `export` attributes to handle Excel styling

        // Create a new object with just needed values (so produced Excel column isn't as wide).

       let exportData = { value: data.value, 

                         highlight: (data.alarm !== null) ? data.alarm.toLowerCase() : null };

        return JSON.stringify(exportData);

     } else {   // will be used for 'filter' and 'sort'

        return data.value;

     }

  }

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

      dom: 'Bfrtip',

      pageLength: 2,

      data: dataSet,

      deferRender: true,

      "columns": [

        { "data": "name" },

        { "data": "debt", render: renderCellValue, createdCell: styleCell },

        { "data": "position" },

        { "data": "age" }

      ],

    buttons: [{

      extend: 'excelHtml5',

      exportOptions: { orthogonal: 'export' },

      customize: function( xlsx, buttonConfig, api ) {

         let sheet = xlsx.xl.worksheets['sheet1.xml'];

         let numExcelRows = $('sheetData', sheet).children().length;

         let numDataRows = api.rows()[0].length;

         // could be multiple header rows

         let numHeaderRows = numExcelRows - numDataRows;

​

         // Iterate through data rows & see if we need to "unpack" any styling including in JSON.

         // JSON should look like, e.g.:  { exportValue: 123, exportHighlight: 'red' }

         $('row:nth-child(n+'+(numHeaderRows+1)+') c', sheet).each(function() {

            let excelCell = $(this);

            let cellData  = excelCell.text();

​

            // only look at JSON objects

            if ( !cellData.startsWith("{") ) {

               return;

            }

            let cellDataParsed;

            try {

               cellDataParsed = JSON.parse(cellData);

            } catch ( exc ) {

               // exception trying to parse JSON, ignore & return

               return;

            }

​

            // ----- Replace the XML cell's text value with `exportValue`

            if ( 'value' in cellDataParsed ) {

               let exportValue = cellDataParsed.value;

               console.log( 'setting export value: ' + exportValue );

               if ( isNumeric(exportValue) ) {  // set as numeric cell for sorting

                  excelCell.attr('t', 'n');

                  $(this).empty();

                  $(this).append('<v>' + exportValue + '</v>');

               } else {

                  $('is t', this).text(exportValue);

               }

            }

​

            // ----- Add cell styling from `exportHighlight`

            if ( 'highlight' in cellDataParsed ) {

               let exportHighlight = cellDataParsed.highlight;

               console.log( 'setting export style = ' + exportHighlight );

               if ( exportHighlight === 'red' ) {

                  excelCell.attr('s', '39');

               } else if ( exportHighlight === 'yellow' ) {

                  $(this).attr('s', '22');

               }

            }

        });

      }

    }]

  });

​

​

} );