html5excel export - cell information error in very specific situation

dodgun

I have an DataTable that imports data via ajax through PHP. I have an export button that utilizes html5export. Most recent version via CDN.

One column is an arbitrary Model identifier. When exported excel says the file needs to be repaired. If I have excel repair the file- it opens but replaces the following two values with NaN.

3301-12-32 and 3302-12-32

First I thought it was hyphens causing the issue, but hundreds of others exist. Through trial and error I noticed if I change the number 1 in "-12-" to any other number the export works fine. Any idea what is causing this behavior?

If I convert the xlsx file into a .zip and extract the XML data- I see that the value is exported as NaN.

Code:
for table:

        $('#dtable').DataTable({
                dom: 'rtilp',
                colReorder: true,
                select: false,
                pageLength: 25,
                lengthMenu: [[25, 50, -1], [25, 50, "All"]],
                lengthChange: true,
                deferRender: true,
                order: [[0, 'asc'], [1, 'asc'], [4, 'asc']],
                buttons: [
                    {
                        extend: 'excelHtml5',
                        exportOptions: {
                            orthogonal: 'export',
                        },
                        title: null,
                        filename: 'export_file'
                    }
                ],
                columns: [
                    {
                        title: "Model",
                    },
                    {
                        title: "Metric 1",
                    },
                    {
                        title: "Metric 2",
                    },
                    {
                        title: "Variance",
                        render: function(data, type, row) {
                            return type === 'export' ? data : Math.floor(data * 100) + '%';
                        }
                    },
                ]
        });

I tried changing to:

    title: "Model",
    type: 'string'

which had no effect. Sorry code formatting isn't working- I've never posted here before. Will try to fix.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

dodgun

Nevermind. Figured it out here: https://datatables.net/forums/discussion/71307/excel-export-error

I changed this file: https://github.com/DataTables/Buttons/blob/f70c766dab0566279e0189cccb48e05039997174/js/buttons.html5.js#L786

line 786 to: { match: /^[\d]{4}-[01][\d]-([0-9]|[1-2][0-9]|3[0-1])$/, style: 67, fmt: function (d) {return Math.round(25569 + (Date.parse(d) / (86400 * 1000)));}}