Exporting jquery datatable to excel with additional rows is not working IE

Question

I am trying to export datatable using jquery export button options to excel sheet. And i want additional rows to be added before the table data in excel file. I made a similar demo in fiddle https://jsfiddle.net/xevpdeo1/17/ . It is working fine in Chrome and Firefox but not in IE. In IE the additional rows before the table data is empty after exporting. Can someone help me out with this? Thanks in advance

$(document).ready(function() {
var xlsBuilder = {
filename: 'business-group-sharers-',
sheetName: 'business-group-sharers-',
customize: function(xlsx) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  var downrows = 4;
  var clRow = $('row', sheet);
  var msg;
  //update Row
  clRow.each(function() {
    var attr = $(this).attr('r');
    var ind = parseInt(attr);
    ind = ind + downrows;
    $(this).attr("r", ind);
  });

  // Update  row > c
  $('row c ', sheet).each(function() {
    var attr = $(this).attr('r');
    var pre = attr.substring(0, 1);
    var ind = parseInt(attr.substring(1, attr.length));
    ind = ind + downrows;
    $(this).attr("r", pre + ind);
  });

  function Addrow(index, data) {

    msg = '<row r="' + index + '">';
    for (var i = 0; i < data.length; i++) {
      var key = data[i].k;
      var value = data[i].v;
      msg += '<c t="inlineStr" r="' + key + index + '">';
      msg += '<is>';
      msg += '<t>' + value + '</t>';
      msg += '</is>';
      msg += '</c>';
    }
    msg += '</row>';
    return msg;
  }
  var r1 = Addrow(1, [{
    k: 'A',
    v: 'Export Date :'
  }, {
    k: 'B',
    v: '10-Jan-2017'
  }]);
  var r2 = Addrow(2, [{
    k: 'A',
    v: 'Account Name :'
  }, {
    k: 'B',
    v: 'Melvin'
  }]);
  var r3 = Addrow(3, [{
    k: 'A',
    v: 'Account Id :'
  }, {
    k: 'B',
    v: '021456321'
  }]);

  sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + r3 + sheet.childNodes[0].childNodes[1].innerHTML;
},
exportOptions: {
  columns: [0, 1, 2, 3]
}
}
$('#example').DataTable({
 dom: 'Bfrtip',
 buttons: [
    $.extend(true, {}, xlsBuilder, {
      extend: 'excel'
   })
 ]
 });
 });

Answer 1

here you have the solution. You have to manipulate with javascript. IE does not support innerHTML property

                     var sheet = xlsx.xl.worksheets['sheet1.xml'];
                       var numrows = 4;



                       //update Row
                       clR.each(function () {
                           var attr = $(this).attr('r');
                           var ind = parseInt(attr);
                           ind = ind + numrows;
                           $(this).attr("r", ind);
                       });

                       // Create row before data
                       $('row c ', sheet).each(function (index) {
                           var attr = $(this).attr('r');

                           var pre = attr.substring(0, 1);
                           var ind = parseInt(attr.substring(1, attr.length));
                           ind = ind + numrows;
                           $(this).attr("r", pre + ind);
                       });

                       function Addrow(index, data) {
                        var row = sheet.createElement('row');
                        row.setAttribute("r", index);               
                           for (i = 0; i < data.length; i++) {
                               var key = data[i].key;
                               var value = data[i].value;

                               var c  = sheet.createElement('c');
                               c.setAttribute("t", "inlineStr");
                               c.setAttribute("s", "2");
                               c.setAttribute("r", key + index);

                               var is = sheet.createElement('is');
                               var t = sheet.createElement('t');
                               var text = sheet.createTextNode(value)

                               t.appendChild(text);                                       
                               is.appendChild(t);
                               c.appendChild(is);

                               row.appendChild(c);                                                                                                                          
                           }

                           return row;
                       }


                       /*function Addrow(index, data) {
                           msg = '<row r="' + index + '">'
                           for (i = 0; i < data.length; i++) {
                               var key = data[i].key;
                               var value = data[i].value;
                               msg += '<c t="inlineStr" s="52"  r="' + key + index + '">';
                               msg += '<is>';
                               msg += '<t >' + value + '</t>';
                               msg += '</is>';
                               msg += '</c>';
                           }
                           msg += '</row>';
                           return msg;
                       }*/


                      var r1 = Addrow(1, [{ key: 'A', value: '' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);
                      var r2 = Addrow(2, [{ key: 'A', value: '' }, { key: 'B', value: 'Report Date' }, { key: 'C', value: '' : '') }]);                           
                      var r3 = Addrow(3, [{ key: 'A', value: '' }, { key: 'B', value: ':' }, { key: 'C', value: '' }]);


                      var r4 = Addrow(4, [{ key: 'A', value: '' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);             


                        var sheetData = sheet.getElementsByTagName('sheetData')[0];

                        sheetData.insertBefore(r4,sheetData.childNodes[0]);
                        sheetData.insertBefore(r3,sheetData.childNodes[0]);
                        sheetData.insertBefore(r2,sheetData.childNodes[0]);
                        sheetData.insertBefore(r1,sheetData.childNodes[0]);

Here you have the Solution to your other issue

  $(document).ready(function() {
  var xlsBuilder = {    filename: 'business-group-sharers-',
    sheetName: 'business-group-sharers-',
    customize: function(xlsx) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];

    var cellsColumnE = sheet.querySelectorAll('row c[r^="E"]');

    for(var i=1; i < cellsColumnE.length; i++){
        var attr = cellsColumnE[i].getAttribute('r');

        cellsColumnE[i].removeAttribute('s');
        cellsColumnE[i].setAttribute('t', "inlineStr");

        var value = (parseFloat(cellsColumnE[i].getElementsByTagName('v')[0].textContent) * 100) .toFixed(2);       
        var is = sheet.createElement('is');
        var t = sheet.createElement('t');
        var text = sheet.createTextNode(value + "%");

        cellsColumnE[i].removeChild(cellsColumnE[i].lastChild);

        t.appendChild(text);
        is.appendChild(t);
        cellsColumnE[i].appendChild(is);                 
    }

    },
    exportOptions: {
      columns: [0, 1, 2, 3, 5]
    }
  }
  $('#example').DataTable({
    dom: 'Bfrtip',
    buttons: [
      $.extend(true, {}, xlsBuilder, {
        extend: 'excel'
      })
    ]
  });
});