Format cells before export excel in jquery datatable

Format cells before export excel in jquery datatable

amritaduttaamritadutta Posts: 1Questions: 1Answers: 0

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.

Answers

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • lukenukemlukenukem Posts: 2Questions: 0Answers: 0

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

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • ajhulsebosajhulsebos Posts: 2Questions: 0Answers: 0

    Solution can be found here

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

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

  • deepudubeydeepudubey Posts: 5Questions: 0Answers: 0
    edited August 2018

    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

This discussion has been closed.