What is the correct format to export dates with html5 to excel?

What is the correct format to export dates with html5 to excel?

jfabianjfabian Posts: 13Questions: 3Answers: 0

These are my dates in datatables (dd/mm/yyyy).

Then I export these dates with the excel button.

As you can see the dates are in the left side and excel treats them as a general format.

Then if I double click each row excel treats theam as date format (right side of the column)

and now I can use the date filters correctly

I tried dd/mm/yyyy and yyyy-mm-dd formats and it does not work I always need to double click on each row.
The only thing I need is not to use the double click again each row.

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 2,748Questions: 0Answers: 480

    Hi @jfabian ,

    These two threads here and here should help, it looks like people got it working in the past,

    Cheers,

    Colin

  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    Hi @colin neither of the two worked. I don't mind using ISO8601 dates
    but it does not automatically detect them. It seems that @ajhulsebos or @mrmccrac are the closest but I can't get it to work.

  • colincolin Posts: 2,748Questions: 0Answers: 480

    Hi @jfabian ,

    Would you be able to link to the page with the problem, or create a test case that reproduces the problem? That would help to understand the issue,

    Cheers,

    Colin

  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    @colin @allan I find the answer !!! (after a week).

    First we have this line.

    <cellXfs count="67">
    

    We change it to this

    <cellXfs count="68">
    

    Then we have this second line.

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

    We add the line beetween. The number 14 is really important because is the date format.

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

    It also needs to change _excelSpecials.

    var _excelSpecials = [
        { match: /^\-?\d+\.\d%$/,       style: 60, fmt: function (d) { return d/100; } }, // Precent with d.p.
        { match: /^\-?\d+\.?\d*%$/,     style: 56, fmt: function (d) { return d/100; } }, // Percent
        { match: /^\-?\$[\d,]+.?\d*$/,  style: 57 }, // Dollars
        { match: /^\-?£[\d,]+.?\d*$/,   style: 58 }, // Pounds
        { match: /^\-?€[\d,]+.?\d*$/,   style: 59 }, // Euros
        { match: /^\-?\d+$/,            style: 65 }, // Numbers without thousand separators
        { match: /^\-?\d+\.\d{2}$/,     style: 66 }, // Numbers 2 d.p. without thousands separators
        { match: /^\([\d,]+\)$/,        style: 61, fmt: function (d) { return -1 * d.replace(/[\(\)]/g, ''); } },  // Negative numbers indicated by brackets
        { match: /^\([\d,]+\.\d{2}\)$/, style: 62, fmt: function (d) { return -1 * d.replace(/[\(\)]/g, ''); } },  // Negative numbers indicated by brackets - 2d.p.
        { match: /^\-?[\d,]+$/,         style: 63 }, // Numbers with thousand separators
        { match: /^\-?[\d,]+\.\d{2}$/,  style: 64 }  // Numbers with 2 d.p. and thousands separators
    ];
    

    We add at the end one line don't forget the comma. If the regex inside match is wrong or there is no match with the other regex excel will alert that the file is damage. d inside Date.parse(d) must be ISOdate(example: "2018-07-06 "). So at the end the match returns a number with a date format . If we give the date "2018-07-06" it will give to excel the number "43287" with date format so excel will show "06/07/2018".

    var _excelSpecials = [
        { match: /^\-?\d+\.\d%$/,       style: 60, fmt: function (d) { return d/100; } }, // Precent with d.p.
        { match: /^\-?\d+\.?\d*%$/,     style: 56, fmt: function (d) { return d/100; } }, // Percent
        { match: /^\-?\$[\d,]+.?\d*$/,  style: 57 }, // Dollars
        { match: /^\-?£[\d,]+.?\d*$/,   style: 58 }, // Pounds
        { match: /^\-?€[\d,]+.?\d*$/,   style: 59 }, // Euros
        { match: /^\-?\d+$/,            style: 65 }, // Numbers without thousand separators
        { match: /^\-?\d+\.\d{2}$/,     style: 66 }, // Numbers 2 d.p. without thousands separators
        { match: /^\([\d,]+\)$/,        style: 61, fmt: function (d) { return -1 * d.replace(/[\(\)]/g, ''); } },  // Negative numbers indicated by brackets
        { match: /^\([\d,]+\.\d{2}\)$/, style: 62, fmt: function (d) { return -1 * d.replace(/[\(\)]/g, ''); } },  // Negative numbers indicated by brackets - 2d.p.
        { match: /^\-?[\d,]+$/,         style: 63 }, // Numbers with thousand 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 Math.round(25569 + (Date.parse(d) / (86400 * 1000)));}}//Date yyyy-mm-dd
    ];
    
  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    If anyone wants to change the format input you only need to change the regex expression and give Date.parse an ISOdate or if you want to change the format output you need to change the number inside numFmtId.

  • colincolin Posts: 2,748Questions: 0Answers: 480
    Answer ✓

    Hi @jfabian ,

    Thanks for reporting back, that's good information. Glad you got it working!!

    Cheers,

    Colin

  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    if someone want to change the input format to "dd/mm/yyyy" use this line.

    { match: /^[\d]{2}\/[\d]{2}\/[\d]{4}$/, style: 67, fmt: function (d) {return Math.round(25569 + (Date.parse(d.substring(4,8)+"-"+d.substring(2, 4)+"-"+d.substring(0, 2)) / (86400 * 1000)));}}//Date dd/mm/yyyy
    
  • AlviiAlvii Posts: 7Questions: 0Answers: 2

    @jfabian working like a charm! I have one question, since i'm not familiarized with regex, is it too dificult to obtain the same but for dd/mm/yyyy hh:mm?

  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    @Alvii for that format you must give excel a float and change the value 14 for the correct format (I don´t know which number is but it exist in excel).

    The regex expresion should be something like this:
    /^[\d]{2}\/[\d]{2}\/[\d]{4}[\s]{1}[\d]{2}\:[\d]{2}$/

    you need to change the function because Date.parse() won´t give you the float you need.

  • AlviiAlvii Posts: 7Questions: 0Answers: 2
    edited July 31 Answer ✓

    Thanks for all @jfabian! I got it working. For anyone with the same problem, the numFmtId is 22, and the match line should be like this (in my case):

    { match: /^[\d]{2}\/[\d]{2}\/[\d]{4}[\s]{1}[\d]{2}\:[\d]{2}$/, style: 68, fmt: function (d) { return Math.round(25569 + (Date.parse(d.substring(4, 8) + "-" + d.substring(2, 4) + "-" + d.substring(0, 2)) / (86400 * 1000))) + d.substring(8, 10) / 24 + d.substring(10, 12) / 1440; } },//Date dd/mm/yyyy hh:mm
    
  • AlviiAlvii Posts: 7Questions: 0Answers: 2

    @jfabian, sorry to bother you again, but i encountered a problem and maybe you can help me. I'm using your code for "dd/mm/yyyy" dates, and it works well except for dates that start with a 0, for example, 02/08/2018. Are you having the same problem?

  • AlviiAlvii Posts: 7Questions: 0Answers: 2
    Answer ✓

    @jfabian, i found a solution! By following what @ajhulsebos said in another post, i had to change this:

    if ( row[i].match && ! row[i].match(/^0\d+/) && row[i].match( special.match ) ) {
    

    for this:

    if (row[i].match && (!row[i].match(/^0\d+/) || special.style >= 67) && row[i].match(special.match)) {
    

    It's all working perfectly now, thank you both!

  • jfabianjfabian Posts: 13Questions: 3Answers: 0

    Thanks @Alvii I didn´t see that one. Sorry for the late response.

Sign In or Register to comment.