Customize excel file before exporting using jQuery DataTables

I need to format excel data before it gets exported to excel. Not much help from official documentation. So I thought of sharing few formatting tricks which are easy to do.

1) Setting file name, title and button name:

buttons: [
{
    extend: 'excelHtml5',
    filename: "File Name You want",
    title: "some title or heading for excel",
    text: 'Export'  //By default it is excel. if you want to change it set "text" value.
}]

2) Setting background for your title:

buttons: [
{
    extend: 'excelHtml5',
    customize: function (xlsx) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        // s = for styling and 5 will make it left align with grey background
        $('row:first c', sheet).attr('s', '5');   
    }
}]

3) Setting rows background color alternatively. Add below code inside customize function:

var sheet = xlsx.xl.worksheets['sheet1.xml'];
var loop = 0;
$('row', sheet).each(function () {
    if (loop % 2 == 0 && loop > 1) {
        $(this).find("c").attr('s', '5');
    }
    loop++;
});

4) Setting background color for specific text in a column of the sheet

$('row c[r^="B"]', sheet).each(function () {        // c : look for column, r (Row) == B
    // Get the value
    var columnValue = $('is t', this).text().toLowerCase();
    if (columnValue.indexOf("total") != -1) {
        $(this).attr('s', '7');
        $(this).siblings().attr('s', '7');
    }
});

5) Applying border to each cell

var loop = 0;
$('row', sheet).each(function () {
  $(this).find("c").attr('s', '25');
  loop++;
});

6) Excluding specific column to be exported. For excluding last column you can do like this:

exportOptions: {
 columns: 'th:not(:last-child)'
}

Reference and more information you can get from: Documentation This is only a brief summary of how to customise the XLSX files. Full details of the XLSX file format and its features are outside the scope of this documentation. Please refer to the Microsoft and Office Open XML documentation for details.




Your feedbacks are most welcome..