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

文章最初发表于szhshp的第三边境研究所
转载请注明

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