Export column string as date to xlsx

Export column string as date to xlsx

keith.abramokeith.abramo Posts: 37Questions: 6Answers: 0

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!

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

    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.abramokeith.abramo Posts: 37Questions: 6Answers: 0

    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.

  • mrmccracmrmccrac Posts: 6Questions: 1Answers: 0

    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.

  • mrmccracmrmccrac Posts: 6Questions: 1Answers: 0

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mrmccracmrmccrac Posts: 6Questions: 1Answers: 0

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • seascanseascan Posts: 3Questions: 1Answers: 0
    edited May 2017

    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;
                                        }
                                    }
    
                                }
                            }
                        }
                    ]
    
  • lukenukemlukenukem Posts: 2Questions: 0Answers: 0

    Hello. any updates?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
  • ajhulsebosajhulsebos Posts: 2Questions: 0Answers: 0

    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.

  • AlviiAlvii Posts: 15Questions: 4Answers: 2

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

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

    Allan

  • AlviiAlvii Posts: 15Questions: 4Answers: 2

    @jfabian i'll do! Thanks!

  • deepudubeydeepudubey Posts: 5Questions: 0Answers: 0

    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

  • deepudubeydeepudubey Posts: 5Questions: 0Answers: 0
    edited October 2018

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • gitamrajeevgitamrajeev Posts: 3Questions: 0Answers: 0

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • deepudubeydeepudubey Posts: 5Questions: 0Answers: 0

    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

  • deepudubeydeepudubey Posts: 5Questions: 0Answers: 0

    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.

  • gitamrajeevgitamrajeev Posts: 3Questions: 0Answers: 0
    edited November 2018

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

    gitamrajeev
    gitamrajeev at gmail.com

This discussion has been closed.