Answers

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2021

  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 Posts: 2Questions: 0Answers: 0

  September 2021

  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 Posts: 2Questions: 0Answers: 0

  October 2021

  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 Posts: 43Questions: 4Answers: 0

  November 2022 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();
• ASWS Posts: 43Questions: 4Answers: 0

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