How do I show date col. in date format?

How do I show date col. in date format?

AkritiAkriti Posts: 1Questions: 1Answers: 0

In my datatable I have a column with date format (ex. "15/05/2020") but when export to excel the format date is General.
Is there a way to explicitly tell datatables, when exporting data, make sure Date column is formatted as a date in the xlsx document?
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

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    At the moment, our Excel export only supports ISO8601 format as a "proper" date / time - e.g. 2020-05-15 would be exported as a date.

    If you are interested this is the part of the code in question. Potentially other formats could be added there if you needed to.

    Allan

  • CaldusCaldus Posts: 2Questions: 0Answers: 0

    Adding this comment here in case anyone else runs into this problem in the future. I had a similar problem as Akriti except the dates in my app are formatted as "MM/DD/YYYY". I added code to convert those dates to a ISO date only when converting to Excel. In the dataTables buttons option, add the following code under the options for the Excel button (only the format code below is needed, but adding the other code for context):

    {
      extend: 'excel', title: finalTitle,
        exportOptions: {
          columns: ':visible',
          orthogonal: null,
          format: {
            body: function (data, row, column, node) {
              var momentDate = moment(data, 'MM/DD/YYYY', true);
              if (momentDate.isValid()) {
                return momentDate.format('YYYY-MM-DD');
              }
              else {
                return data;
              }
            }
          }
       }
    }
    

    Note: This requires installing the moment.js library in your app. Basically, the code checks if the cell contains a certain date value (in my case, a MM/DD/YYYY date). If so, then convert to a ISO date. Now the Excel file will use the Date format for these cells. Not the cleanest solution but this solved the problem for our app.

  • CaldusCaldus Posts: 2Questions: 0Answers: 0

    In addition to my post above, we ended up running into another issue where some columns were not stripping HTML tags due to the changes I made. Update line 13 above with $.fn.dataTable.Buttons.stripData(data); and that should fix the issue.

  • ASWSASWS Posts: 43Questions: 4Answers: 0
    edited November 2022

    returning data stripped from html, as said above cc line 13, can be done like this also;
    return $(data).find('.title').remove().end().text();

  • ASWSASWS Posts: 43Questions: 4Answers: 0
    edited November 2022

    Thx Caldus, inspired me to do something similar like this, works fine (exported data in column 8/9 gets auto-detected by excel as date:

    ...
    exportOptions: {    
     format: {
                    body: function (data, row, column, node) {
                    if (column === 1 || column === 2) {
                    data = data.replace(/[,]/g, '.');
                    return $(data).find('.title').remove().end().text();
                    }
    
                    if (column === 6 || column === 7)
                    {
                    data = data.replace(/[€.]/g, '');
                    data = data.replace(/[,]/g, '.');
                    return $(data).find('.title').remove().end().text();
                    }
    
                   if (column === 8 || column === 9 || column === 10)
                   {
                   data = $(data).find('.title').remove().end().text();
                   data = data.replace(/[.]/g, '/');
                   var thisDate = moment(data, 'DD/MM/YYYY', true);
                   if (thisDate.isValid()) {
                   data = moment(thisDate).format('YYYY-MM-DD');
                   return data;
                    }
                    else
                    {
                    return $(data).find('.title').remove().end().text();
                    }
                    }
                   else
    
                    {
                    return $(data).find('.title').remove().end().text();
                    }
                    }
                    }
                    },
    
Sign In or Register to comment.