Date Format Change Issue #3035

New Issue

rajufodse · 2023-11-25T04:54:45Z

rajufodse commented

2023-11-25 04:54:45 +00:00

I am using SheetJS's 'xlsx.full.min.js' Library from last 2 Years for exporting HTML Table data to Excel (.xlsx) file. Now suddenly I am facing Date issue for Example if My HTML Table Contains Date '01/04/2023' (e.g. 1st April 2023)Then it will export as 04/01/2023. Please help me to rectify the problem. Following is Script and Libraries I use for exporting data to Excel File.

<script>
    function ExportToExcel(type, fn, dl) {
        var elt = document.getElementById('table_id');
        var wb = XLSX.utils.table_to_book(elt, { sheet: "einvoice" });
        return dl ?
            XLSX.write(wb, { bookType: type, bookSST: true, type: 'base64' }) :
            XLSX.writeFile(wb, fn || ('MySheetName.' + (type || 'xlsx')));
    }
</script>

I am using SheetJS's **'xlsx.full.min.js'** Library from last 2 Years for exporting HTML Table data to Excel (.xlsx) file. Now suddenly I am facing Date issue for Example if My HTML Table Contains Date '01/04/2023' (e.g. 1st April 2023)Then it will export as 04/01/2023. Please help me to rectify the problem. Following is Script and Libraries I use for exporting data to Excel File. <script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script> <script> function ExportToExcel(type, fn, dl) { var elt = document.getElementById('table_id'); var wb = XLSX.utils.table_to_book(elt, { sheet: "einvoice" }); return dl ? XLSX.write(wb, { bookType: type, bookSST: true, type: 'base64' }) : XLSX.writeFile(wb, fn || ('MySheetName.' + (type || 'xlsx'))); } </script>

ExporteInvoice.txt

36 KiB

sheetjs commented

2023-11-25 07:56:58 +00:00

Parsing dates has changed over the years. If you want to ensure that the correct dates are passed, there are two approaches:

A) pass the option raw: true. This ensures that each cell is parsed as if they were strings (avoiding value interpretation). This is explained in https://docs.sheetjs.com/docs/api/utilities/html#create-new-sheet

B) Override the values https://docs.sheetjs.com/docs/api/utilities/html#value-override

For a date object, you can use the following attributes to the td element:

data-t="d" ensures that the cell is interpreted as a date
data-v="2023-11-25T07:59:00.000Z" the date value is specified as an ISO date. You can get this value using date.toISOString() from JavaScript code (or let the backend generate the string)
data-z="dd-mm-yyyy" forces the number format.

After setting the attributes in your table, call table_to_book with the option UTC: true

Parsing dates has changed over the years. If you want to ensure that the correct dates are passed, there are two approaches: A) pass the option `raw: true`. This ensures that each cell is parsed as if they were strings (avoiding value interpretation). This is explained in https://docs.sheetjs.com/docs/api/utilities/html#create-new-sheet B) Override the values https://docs.sheetjs.com/docs/api/utilities/html#value-override For a date object, you can use the following attributes to the `td` element: - `data-t="d"` ensures that the cell is interpreted as a date - `data-v="2023-11-25T07:59:00.000Z"` the date value is specified as an ISO date. You can get this value using `date.toISOString()` from JavaScript code (or let the backend generate the string) - `data-z="dd-mm-yyyy"` forces the number format. After setting the attributes in your table, call `table_to_book` with the option `UTC: true`

sheetjs closed this issue

2023-11-25 07:56:58 +00:00

Sign in to join this conversation.