Export column string as date to xlsx

keith.abramo

Hello!

I'm using the html5export button to export my datatable data which is supplied via dom to and xslx. This works great except when I open the excel document and try to format the column which contains my dates it does not work. This seems to be because excel thinks the column is a string.

However, when I double click on a cell in the column in excel, the date string goes from left aligned to right aligned (which is why I think excel thinks this column is a string format) and then when I try to format that cell with a date format, everything works fine.

Is there a way to explicitly tell datatables, when exporting data, make sure this column is formatted as a date in the xlsx document?

Thanks!

allan

Is there a way to explicitly tell datatables, when exporting data, make sure this column is formatted as a date in the xlsx document?

The built in methods will detect ISO8601 dates automatically. Anything else would need to be added using custom code in the customize callback of the excelHtml5 button type.

Allan

keith.abramo

Thanks Allan!

I think I'm passing back the full DateTime from my C# .Net json response. I'll look into chopping off the time component and seeing if that does the trick.

mrmccrac

I have columns that I believe are in a valid ISO8601 format (ex. "2017-05-15"), but when exporting to excel, they aren't getting formatted as Dates. Currency and Number formatting seems to be getting picked up for me, though.

mrmccrac

@keith.abramo were you able to get this to work?

allan

Are you using the latest version of Buttons (1.3.1)?

If you post a link to a test case showing the issue we can take a look into it.

Allan

kthorngren

The example here as the same problem. The format of the date cells is general not date.

https://datatables.net/extensions/buttons/examples/html5/simple.html

Kevin

allan

Thanks Kevin!

I had thought I'd put this feature in already, but apparently not. This is the code where Buttons does its special Excel formatting. It has code for various number types, but not dates yet.

Allan

mrmccrac

@allan FWIW, I tried many many different tweaks of this example diff to try and get it working:

$ diff ~/datatables.js www/shared/javascript/datatables.js
29325c29325
<           '<numFmts count="6">'+
---
>           '<numFmts count="7">'+
29331a29332
>               '<numFmt numFmtId="170" formatCode="yyyy/mm/dd;@"/>'+
29416c29417
<           '<cellXfs count="67">'+
---
>           '<cellXfs count="68">'+
29495a29497
>               '<xf numFmtId="170" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'+
29522c29524,29525
<   { match: /^\-?[\d,]+\.\d{2}$/,  style: 64 }  // Numbers with 2 d.p. and thousands separators
---
>   { match: /^\-?[\d,]+\.\d{2}$/,  style: 64 },  // Numbers with 2 d.p. and thousands separators
>   { match: /^\d{4}\-\d{2}\-\d{2}$/, style: 67, fmt: function (d) { return d.replace(/\-/g, "/"); } } // Dates

When opening the document in Excel, I get a warning about it needing to be repaired. After having it do so, the columns are formatted as Dates, but I can't figure out why Excel doesn't like what I'm giving it. I also tried using the built-in numFmitId 14 and changing around the formatCode for it. Note that this will only work for incoming dates as YYYY-MM-DD which is all I really cared about in my use case / testing.

allan

That looks like a diff of the Javascript. I was meaning a diff of the XML. Rename your xlsx file as .zip and unzip it. Then you will be able to access the raw xml as text files and do a diff on it. That's the only way I know how to do it.

Allan

seascan

Hi. Was wondering if there has been any solution to this issue?

For instance where I have "SOMETHING HERE", is there something I can add to force the cell (or column) to be a date format?

 buttons: [
                    {
                        extend: 'excelHtml5',
                        customize: function (xlsx) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];
                            //$('c[r^="C"]', sheet).attr('s', '55');
                        },
                        exportOptions: {
                            format: {
                                body: function (data, row, column, node) {
                                    if (column == 2) {
                                        return **SOMETHING HERE??**
                                    } else {
                                        return data;
                                    }
                                }

                            }
                        }
                    }
                ]

lukenukem

Hello. any updates?

allan

@lukenukem - See your other thread here.

Allan

ajhulsebos

Was able to get it to work. It requires updating file 'buttons.html5.js' in 3 places in order to get it done:

First in the styles section, we have to add a date style to this and this will become, in my case, style number 67:
look for "xl/styles.xml":
Add 1 to current count so from 67 to 68:

            '<cellXfs count="68">'+

and in the end add one line to the style list, key is the property numFmtID=15 which will result in the next cell format 'd-mmm-yy.'
others are:
14 = 'mm-dd-yy';
15 = 'd-mmm-yy';
16 = 'd-mmm';
see also:
https://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value

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

Second update is pattern match in
var _excelSpecials = [...

Add next match value

    { //Date match dd/MMM/yyyy
        match: /^[\d]{1,2}\/[\w]{3}\/[\d]{4}$/, style: 67, fmt: function (d) {
            //First need to get javascript date value, then we have to convert it into excel date value
            var dval = Date.parse(d);
            dval = Math.round(25569 + (dval / (86400 * 1000)));
            console.log(d, dval);
            return dval;
        }
    }

Important is that you convert JavaScript date value into an excel date value!

Third and last update is in the loop for _excelSpecials
// Special number formatting options

Currently any dates that start with a zero (eq 01/Jan/2018) will not be processed due to the next condition:
!row[i].match(/^0\d+/)

In order to process the date match we have to update the condition.
Done this by the next lines:

                    if (row[i].match && (!row[i].match(/^0\d+/) || special.style>=67) && row[i].match(special.match)) {
                        var val = row[i];
                        if (special.style<67) val=val.replace(/[^\d\.\-]/g, '');

Also have to alter the assignment to value val otherwise your date string will get messed up, so made the replace also depending on the special.style<67.

You would be able to add other date formats to the styles accordingly, the standard style are 1 to 66 so you are able to add others like 67. 68 ...

Might be better way to match the date format or to convert the date to Excel but at least this will get the ball rolling.

Alvii

Hey @allan, any update in detecting the date automatically when exporting? Great work overall, keep it up!

allan

No sorry - that's not something I've had a chance to work on yet.

Allan

jfabian

@Alvii check this
https://datatables.net/forums/discussion/50616/what-is-the-correct-format-to-export-dates-with-html5-to-excel

Alvii

@jfabian i'll do! Thanks!

deepudubey

Hello,

I was able to export ISO8601 dates in date format. According to requirement, I am looking for below cases
1. When user see date values in YYYY-MM-DD format in page then same should be exported to excel in date format.
2. When user see date values in MM-DD-YYYY format in page then same should be exported to excel in date format.
3. When user see date values in DD-MM-YYYY format in page then same should be exported to excel in date format.

I've tried for multiple cases but no luck. Your help would be much appreciated and would be very helpful.

Thanks
PD

jfabian

@deepudubey check this
https://datatables.net/forums/discussion/50616/what-is-the-correct-format-to-export-dates-with-html5-to-excel

deepudubey

Hello,
I can convert any date format to date value while exporting the excel. There is only one problem As per my requirement, While exporting the data to excel, I would like to send one variable value as well so that it would help me to identify the format of date whether it is DD-MM-YYYY or MM-DD-YYYY. Based on this I can split the column data and parse it to date accordingly. I've tried multiple time for jquery options through which I can easily identify the format by it gets failed when date is 01-12-2017 as system is not able to identify that what is the correct format.
Thanks,
Pradeep.

allan

I don't think there is any software "trick" to know if it is month-day-year or day-month-year. That's down to the locale settings of the computer being used. You'd need to get that locale information. That said, if you just use ISO8601 format, then Excel (or any other software) should be able to do its own formatting based on the locale.

Allan

gitamrajeev

hi all
I have YYYY-MM-DD on my html tabl e, could anyone help me exporting into my excel sheet as the cell formatted like Short date instead of General

allan

We don't currently provide that ability built in. The default formatted used are here. You could expand that in the code or use the customize` callback to add it yourself to the Excel spreadsheet.

Allan

deepudubey

Thanks Allan, Its becoming hard like where I need to make changes in the js file. Would be great help if I can have few input

deepudubey

Hello Allan,
Please see attached example where I've made changes to the code (buttons.html5.min) as you have suggested. The pattern which I put is being validated by the regEx. When I tried to extract the value from the string, export to excel button stops working.
Thanks,
Pradeep.

gitamrajeev

hi Pradeep,
could you get your date formatted to date when you tried your code finally.

gitamrajeev
gitamrajeev at gmail.com