How to Export Multiple Row Headers in jQuery Datatables?

Question

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>

Ronnie · Accepted Answer · 2017-03-01 15:33:57Z

17

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
    }

answered Mar 1, 2017 at 15:33

Ronnie

1991 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Saeed Ansari Over a year ago

Could you please specify where is DataTable.ext.buttons.excelHtml5

MaliEbbae Over a year ago

This only work for CSV export. Still have an issue on PDF export. Anyway thanks for your sharing.

JuJoGuAl Over a year ago

Work Great for me, i just add some lines to PDF export and Print, Thanks!

JuJoGuAl Over a year ago

Fot multiple header in BOLD in excel i just change the code: $('row:last c', rels).attr( 's', '2' ); // bold to $('row:not(:first-child) c', rels).attr( 's', '2' ); // bold

CyberNinja Over a year ago

@Ronnie This worked well, but how can I use this with hidden columns on display but shown on export?

|

wittich · Accepted Answer · 2020-03-11 17:50:34Z

Adding to the solution that was given by Ronnie, since most of us got confused how exactly the approach works, would like to take it in a detail.

To get Excel button working add buttons.html5.js, dataTables.buttons.min.js, jszip.min.js.

For multiple row header table export, add below function in buttons.html5.js at the bottom of the document

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;
};

With in the same file find DataTable.ext.buttons.excelHtml5 and replace the code block

if(config.header){
    /*Existing code*/
}

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
}

That's it. you would be able to export the multiple header rows.

And adding to the above if you want to export merged cells then below code would help you.

Add this to buttons customize function.

buttons: [
    {
        extend: 'excel',
        customize: function (xlsx) {
            //Apply styles, Center alignment of text and making it bold.
            var sSh = xlsx.xl['styles.xml'];
            var lastXfIndex = $('cellXfs xf', sSh).length - 1;

            var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
            var s2 = '<xf numFmtId="0" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="0" applyBorder="0" xfId="0" applyAlignment="1">' +
                    '<alignment horizontal="center"/></xf>';

            sSh.childNodes[0].childNodes[0].innerHTML += n1;
            sSh.childNodes[0].childNodes[5].innerHTML += s2;

            var greyBoldCentered = lastXfIndex + 1;

            //Merge cells as per the table's colspan
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            var dt = $('#tblReport').DataTable();
            var frColSpan = $(dt.table().header()).find('th:nth-child(1)').prop('colspan');
            var srColSpan = $(dt.table().header()).find('th:nth-child(2)').prop('colspan');
            var columnToStart = 2;

            var mergeCells = $('mergeCells', sheet);
            mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {
                attr: {
                    ref: 'A1:' + toColumnName(frColSpan) + '1'
                }
            }));

            mergeCells.attr('count', mergeCells.attr('count') + 1);

            var columnToStart = 2;

            while (columnToStart <= frColSpan) {
                mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {
                    attr: {
                        ref: toColumnName(columnToStart) + '2:' + toColumnName((columnToStart - 1) + srColSpan) + '2'
                    }
                }));
                columnToStart = columnToStart + srColSpan;
                mergeCells.attr('count', mergeCells.attr('count') + 1);
            }

            //Text alignment to center and apply bold
            $('row:nth-child(1) c:nth-child(1)', sheet).attr('s', greyBoldCentered);
            for (i = 0; i < frColSpan; i++) {
                $('row:nth-child(2) c:nth-child(' + i + ')', sheet).attr('s', greyBoldCentered);
            }

            function _createNode(doc, nodeName, opts) {
                var tempNode = doc.createElement(nodeName);
                if (opts) {
                    if (opts.attr) {
                        $(tempNode).attr(opts.attr);
                    }
                    if (opts.children) {
                        $.each(opts.children, function (key, value) {
                            tempNode.appendChild(value);
                        });
                    }
                    if (opts.text !== null && opts.text !== undefined) {
                        tempNode.appendChild(doc.createTextNode(opts.text));
                    }
                }
                return tempNode;
            }

            //Function to fetch the cell name
            function toColumnName(num) {
                for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
                    ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
                }
                return ret;
            }
        }
    }
]

Export merged cells customize function is not working for me.

Kanishk Diwakar · Accepted Answer · 2017-07-21 07:34:46Z

5

Answer by @ronnie https://stackoverflow.com/a/42535830/5835910 is working. To make it work download the files from jquery datatable download builder https://datatables.net/download/index.

Please do not use files from example pages.

answered Jul 21, 2017 at 7:34

Kanishk Diwakar

511 silver badge3 bronze badges

2 Comments

John Hayes-Reed Over a year ago

An answer that just says someone elses answer is working, and add some advice on the side, would be better suited as a comment, as your are not proposing an alternative solution to the question asked.

Kanishk Diwakar Over a year ago

@JohnHayes-Reed i don't have enough reputation to leave a comment on other user answer.

Joel Luevano · Accepted Answer · 2017-08-22 15:42:04Z

1

Hi maybe I'm a little late but I hope my answer can help someone else. I exported all rows using an extra library, in my case, table2excel. I just copied the header rows using html() function and getting all the rows with .DataTable() function. The code looks as follows:

$("#exportExcel").click(function(){
    $('<table>')
    .append(
         $("#table1 thead").html()
     )
     .append(
        $("#table1").DataTable().$('tr').clone()
     )
     .table2excel({
        exclude: "",
        name: "casting",
        filename: "ExportedData.xls" 
     });
    });

It solved the problem in my case.

answered Aug 22, 2017 at 15:42

Joel Luevano

1211 gold badge2 silver badges10 bronze badges

1 Comment

JuJoGuAl Over a year ago

is better to use the dataTable lib for that u need just add some code to work exactly the lib that you have

dsn raghavendra rao · Accepted Answer · 2016-10-28 10:33:57Z

0

Better to check this datatable documentation.Find below URL Export multiple row headers

answered Oct 28, 2016 at 10:33

dsn raghavendra rao

1961 silver badge6 bronze badges

1 Comment

indu Over a year ago

How can i include header in buttons only.

Emerson Moretto · Accepted Answer · 2020-02-20 15:24:39Z

0

I've created a custom buttons.html5.js (based in Ronnie's solution) that allows Multiple Headers and Footers in Excel export

https://gist.github.com/emersonmoretto/41993309f74a4b09f8e90c0a541de342

answered Feb 20, 2020 at 15:24

Emerson Moretto

1311 silver badge3 bronze badges

1 Comment

ScreamQueen Over a year ago

Hello, how would I get this working for CSV export?

MjP · Accepted Answer · 2021-02-11 05:49:56Z

0

Hello I'm late but I found a simple solution by https://stackoverflow.com/a/56370447 . Yes it is not dataTable lib but hope it helps some dev's who are finding another solution.

I just changed the value from 'my_id_table_to_export'

<a href="#" onclick="download_table_as_csv('my_id_table_to_export');">Download as CSV</a>

To 'example' (dataTable ID)

<a href="#" onclick="download_table_as_csv('example');">Download as CSV</a>

and then i called the function (just look above solution)

answered Feb 11, 2021 at 5:49

MjP

1

Comments

RL85 · Accepted Answer · 2021-07-09 15:25:43Z

Since this is a top search result for this issue, I wanted to share my work-around.

The database export functions will take from last header row. It will do this even if the last row is hidden!

So an extremely simple solution to this issue is to add one more row to the header, give it the column names you want exported, and then hide that row.

As in:

<thead>
  <tr>...Your first header row...</tr>
  <tr>...Your second header row...</tr>
  <tr style="display: none;">
    <th>Column 1 export-friendly name</th>
    <th>Column 2 export-friendly name</th>
    <th>etc..</th>
  </tr>
</thead>

It still only exports a single header row, but this way you can at least control what the export headers say.

Edit: Just realized that sorting is also applied to the last column, so if it's hidden you won't be able to sort. Adding "bSortCellsTop: true" to the config should fix that.

Edit 2: Sorry, nevermind. Adding bSortCellsTop: true completely undermines the entire solution. Wow, what a train wreck.

Aamir Anwar · Accepted Answer · 2022-12-05 09:46:30Z

Use customize function in button while printing. Because DataTable support header only one header row.

{
        extend: 'print',
        exportOptions: {
            columns: [ 0, 1, 2, 3, 4, 5, 6, 7, 8,9 ]
        },
        text: 'Print',
        header: true,
        className: 'btn btn-default btn-xs',
        sheetName: 'data',
        attr: {
            id: 'print'
        },
        filename: function() {
            return $('#print').data('filename');
        },
        customize: function ( win ) {
            $(win.document.body).find( 'thead' ).prepend(`
                <tr class="">
                    <th colspan="5" >Title: {{$title}}</th>
                    <th colspan="2">Date: {{Request::get('from')}}</th>
                    <th colspan="2">Total guests: {{$guests_list->count()}}</th>
                    <th></th>
                    <th></th>
                </tr>
            `);
        }

fammi farendra · Accepted Answer · 2023-04-09 12:46:50Z

Use extend 'excelHtml5', and simply modify customizeData function

In the customizeData function, there is 3 parameter of array, which is header,body, and fotter

Default action is the header parameter takes the last our table header, if our table has multi header then we put replace header parameter with the first header , and put the rest header into body parameter

extend: 'excelHtml5',
customizeData: function(data) {
    var namatabel = "myTable";
    var colLength = $("#" + namatabel + " thead:first tr:last th").length;
    var jmlheader = $('#'+namatabel+' thead:first tr').length;

    if (jmlheader > 1) {
        data.body.unshift(data.header);
        data.header=[""];
        var j=0,rspan=[];
        for(j=0;j<jmlheader;j++){
                rspan[j]=[];
            for(var i=0;i<colLength;i++){
                rspan[j][i]=0;
            }
        }
        var colSpan=0,rowSpan=0;
        var topHeader = [],thisHead=[],thiscol=0,thisrow=0,jspan=0;
        for(j=1;j<=(jmlheader-1);j++){
            thisHead=[],thiscol=0;jspan=0;
            $('#'+namatabel).find("thead:first>tr:nth-child("+j+")>th").each(function (index, element) {
                colSpan = parseInt(element.getAttribute("colSpan"));
                rowSpan = parseInt(element.getAttribute("rowSpan"));
                jspan=jspan+colSpan;
                if(rspan[thisrow][thiscol]>0){
                    for(var i=0;i<rspan[thisrow][thiscol];i++){
                        thisHead.push("");    
                    }
                }
                if(rowSpan>1){
                    jspan=jspan-colSpan;
                    for (var i=thisrow+1; i < jmlheader; i++) {
                        rspan[i][jspan]=colSpan;   
                    }
                }
                thisHead.push(element.innerHTML.toUpperCase());
                for (var i = 0; i < colSpan - 1; i++) {
                    thisHead.push("");
                }
                thiscol++;
            });
            thisrow++;
            if(j==1){
                data.header=thisHead;
            }else{
                topHeader.push(thisHead);
            }
            
        };
        thiscol=topHeader.length;
        for(j=(thiscol-1);j>=0;j--){
            data.body.unshift(topHeader[j]);
        };    
    }
},
},

That code support rowspan and colspan too

And for print button :

extend: 'print',
customize:function(win) {
    var namatabel = "myTable";
    var jmlheader = $('#'+namatabel+' thead:first tr').length;
    if(jmlheader>1){
        $(win.document.body).find("table").find("thead").empty();
        $(win.document.body).find("table").find("thead").append($('#'+namatabel+' thead:first').html());
        }

    },

Maneesh reddy · Accepted Answer · 2024-07-08 17:40:48Z

To Export multiple headers, simple add the extra headers to data option and move to them to top of the data and add styling to that rows(headers) to stay on top.

//moving rows to top
var table = $('#example').DataTable();
var rowsToMove = [];
var remainingRows = [];

table.rows().every(function () {
  var data = this.data();
  if (condition to select the rows to move top) {
  rowsToMove.push(data);
  }
   else {
  remainingRows.push(data);
  }
  });
  table.clear();

  //Moving rows to the top of the table
  rowsToMove.forEach(function (row) {
  table.row.add(row);
  });

//adding remaining rows to the table
  remainingRows.forEach(function (row) {
  table.row.add(row);
  });

//get the top row and add styling to the row
var rowNode = table.row(0).node();
$(rowNode).addClass('styling')

.styling{
  position: sticky !important;
  top: 95px;
  z-index: 99;
  background-color: white
}

In this way we can export the multiple headers in the jQuery Datatables.

Collectives™ on Stack Overflow

How to Export Multiple Row Headers in jQuery Datatables?

11 Answers 11

8 Comments

1 Comment

2 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

8 Comments

1 Comment

2 Comments

1 Comment

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related