I am getting date columns as general type in excel

Karan_Motwani · January 21

{
extend: 'excelHtml5',
title: pg_name,
exportOptions: {
columns: ':not(.no-print):visible',
orthogonal: null,
format: {
body: function (data, row, column, node) {
var $node = $(node);
if ($node.find("span.clsColsDateStr").length) {
$node.find("span.clsColsDateStr").remove();
}
var text = $node.text().trim();
var m = moment(text, ['DD-MMM-YYYY'], true);
if (m.isValid()) {
return m.format('YYYY-MM-DD');
}
return text || "";
}
}
},
customize: function (xlsx) {
}
}

allan · January 21

Can you link to a test case showing the issue please? ISO8061 formatted date strings should be dates in Excel after the export.

Allan

Wernfried · January 22

Inserting date values into Excel can be tricky. The internal storage format is number of days since 1899-12-30T00:00:00Z.

Based on that, my solution looks like this:

render: (data, type, row, meta) => {
  const d = DateTime.fromISO(data;
  if (type == 'exportExcel') {
     return d.diff(DateTime.fromISO('1899-12-30', { zone: 'utc' }), 'days').days;
  } else {
     return d.toLocaleString(DateTime.DATETIME_SHORT);
  }
}




{
  extend: "excelHtml5",
  exportOptions: { orthogonal: 'exportExcel' },
  customize: function (xlsx, config, dt) {
     const styles = xlsx.xl['styles.xml'];
     const numFmts = $('numFmts', styles);
     const nextId = (function () {
        const ids = $('numFmts numFmt', styles).map(function () {
           return +this.getAttribute('numFmtId') || -1;
        }).toArray();
        return (ids.length ? Math.max.apply(Math, ids) : 163) + 1;
     })();
     const fmt = `<numFmt numFmtId="${nextId}" formatCode="dd.mm.yyyy hh:mm"/>`;
     if (numFmts.length) {
        numFmts.append(fmt).attr('count', (+numFmts.attr('count') || 0) + 1);
     } else {
        $('styleSheet', styles).prepend(`<numFmts count="1">${fmt}</numFmts>`);
     }

     const cellXfs = $('cellXfs', styles);
     const attr = `numFmtId="${nextId}" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"`;
     cellXfs.append(`<xf ${attr}/>`).attr('count', (+cellXfs.attr('count')) + 1);
     const styleIndex = $('xf', cellXfs).length - 1;

     const sheet = xlsx.xl.worksheets['sheet1.xml'];
     for (let c of ['planStartDate', 'planEndDate'])
        $(`row c[r^="${String.fromCharCode("A".charCodeAt(0) + columns.map(x => x.data).indexOf(c))}"]`, sheet).attr('s', styleIndex);
  }
}

I am using Luxon rather than outdated moments.js. But I think, it should be no big deal to convert it.

allan · January 22

Many thanks. Interesting to see how you've done it.

For interest, this is how Buttons does it. It only supports YYYY-MM-DD at the moment.

Allan

Wernfried · 8:59AM

Looks like there is a problem in version 3. In version this worked fine, now I see in Excel the numeric value, e.g. 40991.541666666664

My custom render function is this:

render: (data, type, row, meta) => {
  const d = DateTime.fromISO(data);
  if (type == 'display') {
     return `${d.toLocaleString(DateTime.DATE_MED)}<br>${d.toLocaleString(DateTime.TIME_SIMPLE)}`;
  } else if (type == 'responsive') {
     return `${d.toLocaleString(DateTime.DATE_MED)} ${d.toLocaleString(DateTime.TIME_SIMPLE)}`;
  } else if (type == 'exportExcel') {
     return d.diff(DateTime.fromISO('1899-12-30', { zone: 'utc' }), 'days').days;
  } else {
     return d.toLocaleString(DateTime.DATETIME_SHORT);
  }
}

I suspect the dt export-button exports a string, despite d.diff(DateTime.fromISO('1899-12-30', { zone: 'utc' }), 'days').days is a number.

In XML data, a numeric column looks like this:
<row r="3"> <c t="n" r="A3"> <v>62201</v> </c>

whereas my date field is:
<c t="inlineStr" r="H3" s="68"> <is> <t xml:space="preserve">40991.541666666664</t> </is> </c>

I would rather expect
<c r="H3" s="68"> <v>40991.541666666664</t> </c>

or something similar.

Any ideas?

allan · 9:04AM

Hi,

Can you link to a test case showing the issue so I can look into it in detail please?

Thanks,
Allan

Wernfried · 9:42AM

I tried this workaround:

for (let c of ['planStartDate', 'planEndDate']) {
  const col = String.fromCharCode('A'.charCodeAt(0) + columns.map(x => x.data).indexOf(c)-1);
  $(`row c[r^="${col}"]`, sheet).each(function () {
     const cell = $(this);
     const value = $('is t', cell).text();
     if (!value)
        return;
     cell.removeAttr('t');
     cell.empty();
     cell.append(`<v>${value}</v>`);
     cell.attr('s', styleIndex);
  });
}

It works in principle, i.e when I open the sheet, then Excel complains about "a problem with some content" and is able to correct it. But certainly not the final solution.

Wernfried · 10:16AM

I did not manage it to make it running on https://live.datatables.net but this code should work:

const DateTime = luxon.DateTime;

const table = new DataTable('#example', {
   columns: [{
      name: 'startDate', title: 'Start Date', data: 'startDate',
      columnControl: ['order'],
      render: function (data, type, row, meta) {
         var d = DateTime.fromISO(data);
         if (type == 'exportExcel') {
            return d.diff(DateTime.fromISO('1899-12-30', { zone: 'utc' }), 'days').days;
         } else {
            return d.toLocaleString(DateTime.DATETIME_SHORT);
         }
      }
   }],
   data: [{ startDate: '2026-10-21T12:30:45' }],
   layout: {
      bottomStart: {
         buttons: [
            {
               extend: 'excelHtml5',
               customize: function (xlsx) {
                  const styles = xlsx.xl['styles.xml'];
                  const numFmts = $('numFmts', styles);
                  const nextId = (function () {
                     const ids = $('numFmts numFmt', styles).map(function () {
                        return +this.getAttribute('numFmtId') || -1;
                     }).toArray();
                     return (ids.length ? Math.max.apply(Math, ids) : 163) + 1;
                  })();
                  const fmt = `<numFmt numFmtId="${nextId}" formatCode="dd.mm.yyyy hh:mm"/>`;
                  if (numFmts.length) {
                     numFmts.append(fmt).attr('count', (+numFmts.attr('count') || 0) + 1);
                  } else {
                     $('styleSheet', styles).prepend(`<numFmts count="1">${fmt}</numFmts>`);
                  }

                  const cellXfs = $('cellXfs', styles);
                  const attr = `numFmtId="${nextId}" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"`;
                  cellXfs.append(`<xf ${attr}/>`).attr('count', (+cellXfs.attr('count')) + 1);
                  const styleIndex = $('xf', cellXfs).length - 1;

                  const sheet = xlsx.xl.worksheets['sheet1.xml'];
                  const col = 'A';
                  $(`row c[r^="${col}"]`, sheet).each(function () {
                     const cell = $(this);
                     cell.attr('s', styleIndex);
                  });
               }
            }
         ]
      }
   }

});

I am getting date columns as general type in excel

I am getting date columns as general type in excel

Answers