I am getting date columns as general type in excel

Karan_Motwani

{
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

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

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

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