Date format changed to sevral format while exporting to excel

General Web Dev
1

HI!
I am trying to convert my php based report into excel by using below code. everything is working fine, but the date is changed to different format. How can i fixed that issue. Can expert guide me about this ???
excel export code

<script>

    function html_table_to_excel(type)
    {
        var data = document.getElementById('employee_data');

        var file = XLSX.utils.table_to_book(data, {sheet: "sheet1"});

        XLSX.write(file, { bookType: type, bookSST: true, type: 'base64' });

        XLSX.writeFile(file, 'Procedure Wise Report.' + type);
    }

    const export_button = document.getElementById('export_button');

    export_button.addEventListener('click', () =>  {
        html_table_to_excel('xlsx');
    });

</script>
2

is it different in the raw file, or in excels presentation of it?

3

its in excel presentation

4

So if it looks one way in the raw XSLX file, and a different in the excel app, that’s not a problem in the script, it’s how excel is formatting the date. You can change that in excel.

5

its showing same output every where some thing like below image . As showing in image, first three top row its in mm-dd-yy format, after three row its in dd/mm/yyyy and so on
Capture

6

I’m going to guess thats because 10-12-23 is ambiguous.

It could be mm-dd-yy. (October 12th)
It could also be dd-mm-yy. (December 10th)

10-13-23 is not ambiguous, because there is no 13th month of the year, so it must be October 13th.

7

this is html/page page from where i export into excel format. The standard format in below html page is dd/mm/yyyy. Should I try to change the html date format into dd-mmm-yy ??? may be it can solve the issue ?

Capture

8

I will assume (because it hasnt been specified) that you are using SheetJS to convert this, because it’s the only function reference i can find in google that appears to match.

Based on the Documentation of this function, what happens if you change
var file = XLSX.utils.table_to_book(data, {sheet: "sheet1"});
to
var file = XLSX.utils.table_to_book(data, {sheet: "sheet1", cellDates: true});
?

9

out of suggested change by you. i am using javascript via this link

<script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script>

Capture

10

okay… lets try forcing the output…

var file = XLSX.utils.table_to_book(data, {sheet: "sheet1", cellDates: true, dateNF: "dd/mm/yyyy"});

1 Like
11

issue is solved! Thanks alot sir

1 Like