Answers

• allan Posts: 63,542Questions: 1Answers: 10,476 Site admin

  May 2018 Answer ✓

  I'm not aware of anyone having done this I'm afraid. You would need to use the customize callback of the excelHtml5 button type to modify the xlsx file's XML to add that.

  I think you would probably need to reverse engineer a solution from an Excel file that uses an Autofilter.

  Allan
• jlock Posts: 14Questions: 4Answers: 0

  May 2018

  Found this from @F12Magic and it worked perfectly, thank you!

  DataTables initialization: Use the customize option to add AUTO FILTERS to the column headers. Required is the function createCellPos (n) ,copied from buttons.html5.js, which you need to insert in your own script. We can't call it directly, because it's a private function in that plugin script.

  $('#myTable').DataTable( {
      buttons: [
          {
              extend: 'excelHtml5',
              text: 'Save as Excel',
              customize: function( xlsx ) {
                  var sheet = xlsx.xl.worksheets['sheet1.xml'];
                  var lastCol = sheet.getElementsByTagName('col').length - 1;
                  var colRange = createCellPos( lastCol ) + '1';
                  //Has to be done this way to avoid creation of unwanted namespace atributes.
                  var afSerializer = new XMLSerializer();
                  var xmlString = afSerializer.serializeToString(sheet);
                  var parser = new DOMParser();
                  var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                  var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
                  var filterAttr = xmlDoc.createAttribute('ref');
                  filterAttr.value = 'A1:' + colRange;
                  xlsxFilter.setAttributeNode(filterAttr);
                  sheet.getElementsByTagName('worksheet')[0].appendChild(xlsxFilter);
              }
          }
      ]
  } );
  
  function createCellPos( n ){
      var ordA = 'A'.charCodeAt(0);
      var ordZ = 'Z'.charCodeAt(0);
      var len = ordZ - ordA + 1;
      var s = "";
  
      while( n >= 0 ) {
          s = String.fromCharCode(n % len + ordA) + s;
          n = Math.floor(n / len) - 1;
      }
  
      return s;
  }
  
• ChrisGR Posts: 2Questions: 0Answers: 0

  August 2018 edited August 2018

  I have made some small changes in the source code of the autoFilter function in order to work for me and to suit my needs:

  ,customize: function(xlsx) {
      var sheet = xlsx.xl.worksheets['sheet1.xml'];
      var lastCol = sheet.getElementsByTagName('col').length - 1;
      var colRange = createCellPos( lastCol ) + '2';//AutoFilter in the second row
      //Has to be done this way to avoid creation of unwanted namespace atributes.
      var afSerializer = new XMLSerializer();
      var xmlString = afSerializer.serializeToString(sheet);
      var parser = new DOMParser();
      var xmlDoc = parser.parseFromString(xmlString,'text/xml');
      var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
      var filterAttr = xmlDoc.createAttribute('ref');
      filterAttr.value = 'A2:' + colRange;
      xlsxFilter.setAttributeNode(filterAttr);
      var sheetDataNode = sheet.getElementsByTagName('worksheet')[0].getElementsByTagName("sheetData")[0];
      sheet.getElementsByTagName('worksheet')[0].insertBefore(xlsxFilter, sheetDataNode.nextSibling);
  }
  

  It may be helpful to someone....
• allan Posts: 63,542Questions: 1Answers: 10,476 Site admin

  September 2018

  I've decided to go ahead and provide the ability to enable this feature in Buttons itself. There is now a autoFilter option which can be used to enable the auto filter feature in Excel:

  $('#myTable').DataTable( {
      buttons: [
          {
              extend: 'excelHtml5',
              autoFilter: true
          }
      ]
  } );
  

  Commit

  This will be in 1.5.4 or you can grab the latest source from GitHub or the nightly which will be up to date shortly.

  Allan