Add Autofilter to Excel Column Headers on Export

jlock

I cannot figure out how to add autofilter to the column header in the excel worksheet when exported. Has anyone done this successfully and can share some code? I have tried adjusting the xlm but it does not work.

allan

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

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

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

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