How to export Merged column header in datatables

Export Merged column header in datatables

If you looking for a post where you need to export a datatable with merged column header then you are at right place. Below is the detailed example to execute this. You can implement this in your BAU requirements.

Please follow below steps - 

1. Add 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;

};

2. Find "DataTable.ext.buttons.excelHtml5" and Change the code in DataTable.ext.buttons.excelHtml5 in buttons.html5.js file to :-

if ( config.header ) {//search 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

    }

Output :-