Customize excel file before exporting using jQuery DataTables

Posted on September 4, 2018 by Suresh Kamrushi in Javascript

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 and title:

buttons: [
{
    extend: 'excelHtml5',
    filename: "File Name You want",
    title: "some title or heading for excel"
}]

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

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.