Format cells before export excel in jquery datatable

amritadutta

I have implemented jquery datatable in my application.
But the problem is when I export the excel from datatable , then by default the type of all columns is text/General.
Is there any option to change the format of the column from string to date (MM/dd/YYYY) before export the excel?
This is our client requirement and I am not able to do that .
Please help me to resolve this .

Below are my sample code.

$(document).ready(function () {
            $.fn.dataTable.moment('D/MM/YYYY');
            $('#assetleakage').on('init.dt', function () {
                $("#assetleakage").wrap("<div class='table-wraper'></div>");
            }).dataTable({
                "columnDefs": [
                {
                    targets: [16, 17],
                    orderable: false
                },
                {
                    targets: [0, 1, 2, 3, 4, 9, 14],
                    searchable: false
                }],
                dom: 'lBfrtip',
                buttons: [
                    {
                        extend: 'csv',
                        exportOptions: {
                            columns: [6, 7, 8, 9, 2, 11, 12, 13, 14, 3, 5, 16]
                        }
                    },
                    {
                        extend: 'excel',
                        exportOptions: {
                            columns: [6, 7, 8, 9, 2, 11, 12, 13, 14, 3, 5, 16]
                        }
                    },
                    {
                        extend: 'pdf',
                        pageSize: 'A2',
                        exportOptions: {
                            columns: [6, 7, 8, 9, 2, 11, 12, 13, 14, 3, 5, 16]
                        }
                    }],
                "language": {
                    "lengthMenu": "Display records per page _MENU_",
                    "zeroRecords": "No records available",
                    "infoEmpty": "No records available",
                    "infoFiltered": "(filtered from _MAX_ total records)"
                }
            });
        });

Thanks in advance.

allan

I presume you mean you want it as a date cell in Excel, not just a string cell with a date in it?

The Excel output doesn't currently do anything special with dates. Excel will see some as dates itself automatically (ISO8601 dates for example), but not that format I think since it is potentially ambiguous.

If you wanted to add this feature, you would do so at this point in the code. Add a regex to match the format you need and also a style for that date format. I'm not sure how Excel's XML stores dates, so you'd need to research that as well.

Regards,
Allan

lukenukem

hello. Was anyone able to implement the regex and style to export ISO8601 dates in date format?

allan

Not as far as I am aware. The point in the code I linked to above is where you would need to modify the code if you want to add that ability.

Allan

ajhulsebos

Solution can be found here

https://datatables.net/forums/discussion/40660

But you might have to change the date regex to match yours

deepudubey

Hello,

I was able to export ISO8601 dates in date format. According to requirement, I am looking for below cases
1. When user see date values in YYYY-MM-DD format in page then same should be exported to excel in date format.
2. When user see date values in MM-DD-YYYY format in page then same should be exported to excel in date format.
3. When user see date values in DD-MM-YYYY format in page then same should be exported to excel in date format.

I've tried for multiple cases but no luck. Your help would be much appreciated and would be very helpful.

Thanks
PD