How do I show date col. in date format?

Akriti

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

allan

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

Caldus

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.

Caldus

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.

ASWS

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

ASWS

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();
                }
                }
                }
                },