How to Export Multiple Row Headers in jQuery Datatables?

indu picture indu · Oct 28, 2016 · Viewed 20.6k times · Source

Hi I am using jQuery Datatables 1.10. I am trying to export Datatable multiple header rows but not getting. But it is Exporting only second header row. I am using Buttons:

 buttons: [{
        extend: 'excel',
        header: true

    }, {
        extend: 'print',
        header: true
    }],

My table Structure like

<table id="example" style="color: black;" class="display compact cell-border" cellspacing="0">
    <thead>
        <tr>
            <th rowspan="2">Sl.No</th>
            <th rowspan="2">Zone</th>
            <th colspan="2">Allotted</th>
            <th colspan="2">Vacant</th>
            <th colspan="2">Amenities</th>
            <th colspan="2">Total</th>
        </tr>
        <tr>
            <th>No Of Plots</th>
            <th>Area</th>
            <th>No Of Plots</th>
            <th>Area</th>
            <th>No Of Plots</th>
            <th>Area</th>
            <th>No Of Plots</th>
            <th>Area</th>
        </tr>
    </thead>
</table>                           

Answer

Ronnie picture Ronnie · Mar 1, 2017

The mentioned solution on the DataTable-forum is not working for the latest version. I have adapted it in a way it works for me. I added a local function to buttons.html5.js:

var _fnGetHeaders = function(dt) {
    var thRows = $(dt.header()[0]).children();
    var numRows = thRows.length;
    var matrix = [];

    // Iterate over each row of the header and add information to matrix.
    for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
        var $row = $(thRows[rowIdx]);

        // Iterate over actual columns specified in this row.
        var $ths = $row.children("th");
        for ( var colIdx = 0;  colIdx < $ths.length;  colIdx++ )
        {
            var $th = $($ths.get(colIdx));
            var colspan = $th.attr("colspan") || 1;
            var rowspan = $th.attr("rowspan") || 1;
            var colCount = 0;

            // ----- add this cell's title to the matrix
            if (matrix[rowIdx] === undefined) {
                matrix[rowIdx] = [];  // create array for this row
            }
            // find 1st empty cell
            for ( var j = 0;  j < (matrix[rowIdx]).length;  j++, colCount++ ) {
                if ( matrix[rowIdx][j] === "PLACEHOLDER" ) {
                    break;
                }
            }
            var myColCount = colCount;
            matrix[rowIdx][colCount++] = $th.text();

            // ----- If title cell has colspan, add empty titles for extra cell width.
            for ( var j = 1;  j < colspan;  j++ ) {
                matrix[rowIdx][colCount++] = "";
            }

            // ----- If title cell has rowspan, add empty titles for extra cell height.
            for ( var i = 1;  i < rowspan;  i++ ) {
                var thisRow = rowIdx+i;
                if ( matrix[thisRow] === undefined ) {
                    matrix[thisRow] = [];
                }
                // First add placeholder text for any previous columns.                 
                for ( var j = (matrix[thisRow]).length;  j < myColCount;  j++ ) {
                    matrix[thisRow][j] = "PLACEHOLDER";
                }
                for ( var j = 0;  j < colspan;  j++ ) {  // and empty for my columns
                    matrix[thisRow][myColCount+j] = "";
                }
            }
        }
    }

    return matrix;
};

Then I changed the code in DataTable.ext.buttons.excelHtml5 in the same file to:

    if ( config.header ) {
                /* ----- BEGIN changed Code ----- */ 
                var headerMatrix = _fnGetHeaders(dt);
                for ( var rowIdx = 0;  rowIdx < headerMatrix.length;  rowIdx++ ) {
                    addRow( headerMatrix[rowIdx], rowPos );
                }
                /* ----- OLD Code that is replaced: ----- */    
                //addRow( data.header, rowPos );
                /* ----- END changed Code ----- */  
                $('row c', rels).attr( 's', '2' ); // bold
    }