Excel Export, Built in styles documentation is incomplete

rf1234

I had the requirement to make Excel recognize exported date columns to be able to do ordering by date.
I found out in some older forum thread that there is a built-in style for that and how to use it.

This is style 67 which is missing in the docs (the docs end with style 66).
https://datatables.net/reference/button/excelHtml5

Could you update this please @allan. Many thanks!

I use this (probably outdated) version of buttons: https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.js

This is style 67:

'<xf numFmtId="14" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'

And this is the matching function in "_excelSpecials":

{ match: /^[\d]{4}\-[\d]{2}\-[\d]{2}$/, style: 67, fmt: function (d) {return Math.round(25569 + (Date.parse(d) / (86400 * 1000)));}} //Date yyyy-mm-dd

And this is how I use it:
As part of the "customize" method:

var dateCols = ['C', 'F', 'I'];       
for ( i=0; i < dateCols.length; i++ ) {
    $('row c[r^='+dateCols[i]+']', sheet).attr( 's', '67' );
}

As part of "exportOptions.format.body":

if ( $.inArray(column, targetColsDates) >= 0 ) {
    if (data <= "---") {
        return "";
    }
    //if we have a timestamp field
    if ( ( data.substr(0, 10).split("-").length - 1 ) === 2 ) {
        return data;
    }
    return moment(data, 'L').format('YYYY-MM-DD');
}

So, I basically don't care whether the "_excelSpecials" function recognizes timestamps - because they are orderable anyway even if not recognized as dates. All the "real" dates are converted to "YYYY-MM-DD" and can be matched by "_excelSpecials".

allan

Many thanks for pointing out the missing style in the docs! That's the commit made to add it and I'll deploy it to the site soon.

Allan