将 jquery 数据表导出到带有附加行的 excel 无法正常工作 IE [英] Exporting jquery datatable to excel with additional rows is not working IE

问题描述

我正在尝试使用 jquery 导出按钮选项将数据表导出到 Excel 工作表.我希望在 excel 文件中的表数据之前添加额外的行.我在 fiddle https://jsfiddle.net/xevpdeo1/17/ 中做了一个类似的演示.它在 Chrome 和 Firefox 中运行良好，但在 IE 中不起作用.在 IE 中，表数据之前的附加行在导出后为空.有人可以帮我解决这个问题吗?提前致谢

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'
   })
 ]
 });
 });

推荐答案

这里有解决方案.您必须使用 javascript 进行操作.IE 不支持 innerHTML 属性

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'
      })
    ]
  });
});

这篇关于将 jquery 数据表导出到带有附加行的 excel 无法正常工作 IE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！