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 · October 21

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 · October 21

Hi,

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

Thanks,
Allan

Wernfried · October 21

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 · October 21

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);
                  });
               }
            }
         ]
      }
   }

});

allan · October 22

I've just put your code into this bin and it will generate a file okay. I've managed to open it in both LibreOffice and Excel (web version) without any issue:

I don't have Excel native on this machine I'm afraid (Linux / KDE), so I can't test that. However, if you are getting "a problem with some content" from your custom XML, it would be a case of inspecting what Excel actually needs and seeing what is different.

Normally I would do that by allowing it to correct the error, then change the .xlsx file to a .zip, unzip and inspect the XML it created. That might also need to be cross referenced to the open spreadsheet spec. I'm afraid that for custom exports, that is something that is outside of the free support that I can provide.

Allan

Wernfried · October 22

Yes, on my computer at home it displays

But it is still a string, not a date. When you try =A3+1 then it should return the next day, but I get a #VALUE! error.
No worry, I will find it out.

Wernfried

I am getting date columns as general type in excel

I am getting date columns as general type in excel

Answers