How to add blank rows or custom header in excel export file in datatables?

Question

How to add some blank rows in datatables exported excel file?

I am trying to add headers into an excel file and I tried many approaches but none of them are working for me.

If no one knows how to do that a hint how to add blank rows in excel would also be useful for me.

My simple code at the moment is below and I wanted to know what I can add to it to make it work for excel export as I've already found a solution for pdf and csv but not for excel file:

$('#invoice_data').dataTable( {
    ordering: true,"autoWidth": false,
    paging: true,
    searching: true,
    dom: 'Bftripl',
    buttons: [
        'excelHtml5',
        'csvHtml5',
        'pdfHtml5'
    ]
});

Following is another try, where I am trying to add blank row but it's not working:

var buttonCommon = {
    exportOptions: {
        format: {
            header: function ( data, row, column, node ) {
                // Strip $ from salary column to make it numeric
                return column === 1 ?
                    "llll "+row+" nd "+node :
                    data+"KKK "+row+" nd "+node;
            }
        }
    }
};
 $('#invoice_data').DataTable({
    ordering: true,"autoWidth": false,
    paging: true,
    searching: true,
    dom: 'Bftripl',
    buttons: [
        $.extend( true, {}, buttonCommon, {
            extend: 'copyHtml5'
        } ),
        $.extend( true, {}, buttonCommon, {
            extend: 'excelHtml5'
        } ),
        $.extend( true, {}, buttonCommon, {
            extend: 'pdfHtml5'
        } )
    ]
} );

Answer 1

So finally I have found a solution to this so my code is capable to place header in Excel, PDF, CSV file in datatable. As I have found it's really hard to find an answer for this, so I am placing my code here. So that anyone can use it.

Before placing below code, download and store the file for version 1.2.2. you can find link here: version 1.2.2 link and download following file //cdn.datatables.net/buttons/1.2.2/js/buttons.html5.js

So place following code line before my DataTable code (I have placed above downloaded file to js folder and then rename to "buttons_export_config_header.js"):

<script type="text/javascript" src="../js/buttons_export_config_header.js">

I have altered line number 1129 to 1131 where I have placed following code in above file:

if ( config.header) {
         var tablecaption = [config.message];
  addRow( tablecaption, rowPos );
         //addRow( "testing", "0" );
          addRow( "", rowPos );
        addRow( data.header, rowPos );
        //$('row c', rels).attr( 's', '2' ); // bold
    }

I am really thankful to following link: datatables post

$('#invoice_data').DataTable({
    ordering: true,"autoWidth": false,
    paging: true,
    searching: true,
    dom: 'Bftripl',
    buttons: [
    {
            extend: 'excelHtml5',

            title: 'Any title for file',
            message: "Any message for header inside the file. I am not able to put message in next row in excel file but you can use \n"


        },
        {
            extend: 'csvHtml5',
            title: 'Any title for the file',
             customize: function (csv) {
                 return "Any heading for the csv file can be separated with , and for new line use \n"+csv;
              }
        },
        {
            extend: 'pdfHtml5',
            title: 'Any title for file',
            customize: function ( doc ) {
                            doc.content.splice( 0, 0, {
                                text: "custom header\n my header"
                            } );
            }
        }

        //'excelHtml5',
        //'csvHtml5',
        //'pdfHtml5'
    ] 

}); 

I am glad that I have found solution by myself.:)

Answer 2

i Found solution

---

buttons: [
            {extend: 'excelHtml5',
            title: 'Report',
            text:'<i class="fa fa-table fainfo" aria-hidden="true" ></i>',
            titleAttr: 'Export Excel',
            "oSelectorOpts": {filter: 'applied', order: 'current'},
            exportOptions: {
                    modifier: {
                    page: 'all'
                    },
                        format: {
                            header: function ( data, columnIdx ) {
                                if(columnIdx==1){
                                return 'column_1_header';
                                }
                                else{
                                return data;
                                }
                            }
                        }
                }
            ]

---