DataTable-输出Excel添加额外行的实现

Datatable Excel 输出

这个方法对主流浏览器适用，特别是 IE Edge

有个需求需要在 Datatable 输出的 Excel 顶端添加几行数据, 看了下 Datatable 官方的实现, 作者似乎也没啥好主意, 不过一些用户提供了方法。

基于Button.Customize参数实现:

jQuery(document).ready(function($) {
  $('table#datatable').dataTable({
    buttons: [{
      extend: 'excelHtml5',
      render: function ( data, type, full, meta ) {
            return '<a href="'+data+'">Download</a>'; //change the button text here
      },
      customize: function(xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        var numrows = 4;

        // add styles for the column header, these row will be moved down
        var clRow = $('row', sheet);
        $(clRow[0]).find('c').attr('s', 32);
          
        //update Row
        clRow.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].k;
            var value = data[i].v;

            var c  = sheet.createElement('c');
            c.setAttribute("t", "inlineStr");
            c.setAttribute("s", "2");  /*set specific cell style here*/
            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);
            debugger;
          }

          return row;
        }

        //add data to extra rows
        var countryStateList = 'asd';
        var agencyValue = 'asd';
        var reportGroupList = 'asd';

        var r1 = addRow(1, [{ k: 'A', v: 'Report Filter Criteria:' }, { k: 'B', v: '' }]);   //add one cell for row 1 
        var r2 = addRow(2, [{ k: 'A', v: 'Country/State:' }, { k: 'B', v: countryStateList }]); //add two cells for row 2-4
        var r3 = addRow(3, [{ k: 'A', v: 'Agency:' }, { k: 'B', v: agencyValue }]);
        var r4 = addRow(4, [{ k: 'A', v: 'Report Group:' }, { k: 'B', v: reportGroupList }]);

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

      }
    }]
  });
});

参考文献

Excel Export Add Rows and Data