DataTables: problem with date and with excel

delph49 · October 2018

Hello,

I have a problem to sort my dates. I'm French so I wan't my dates in this format: DD/MM/YYYY but with that the sorting does not work. I tried differents methods but it doesn't works.

And I have another problem, it's with Excel export. It doesn't works. I have this message with F12:

    Uncaught TypeError: f.parseXML is not a function
        at a (buttons.html5.min.js:26)
        at _Api.action (buttons.html5.min.js:26)
        at v (dataTables.buttons.min.js:16)
        at HTMLButtonElement.<anonymous> (dataTables.buttons.min.js:17)
        at HTMLButtonElement.dispatch (jquery-3.3.1.slim.min.js:2)
        at HTMLButtonElement.v.handle (jquery-3.3.1.slim.min.js:2)
    a @ buttons.html5.min.js:26
    action @ buttons.html5.min.js:26
    v @ dataTables.buttons.min.js:16
    (anonymous) @ dataTables.buttons.min.js:17
    dispatch @ jquery-3.3.1.slim.min.js:2
    v.handle @ jquery-3.3.1.slim.min.js:2

I'm working with Chrome.

I have in my base.html:

                <meta charset="UTF-8" />
                <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
                <link rel="stylesheet" href="https://bootswatch.com/4/flatly/bootstrap.min.css">
                <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css">
                <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.4/css/buttons.dataTables.min.css">
                <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/responsive/2.2.3/css/responsive.dataTables.min.css">
                <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.4/css/buttons.dataTables.min.css">
                <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/1.2.7/css/select.dataTables.min.css">       
                <link rel="stylesheet" type="text/css" href="https://editor.datatables.net/extensions/Editor/css/editor.dataTables.min.css">
                <link rel="stylesheet" href="{{ asset('css/main.css') }}" />

        <script type="text/javascript"></script>
        <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>  
        <script src="https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>   
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/dataTables.buttons.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/buttons.colVis.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/buttons.flash.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/buttons.html5.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/buttons.print.min.js"></script>
        <script src="https://cdn.datatables.net/buttons/1.5.4/js/buttons.bootstrap4.min.js"></script>
        <script src="https://cdn.datatables.net/select/1.2.7/js/dataTables.select.min.js"></script>
        <script src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>   
        <script src="https://editor.datatables.net/extensions/Editor/js/dataTables.editor.min.js"></script>
        <script src="https://editor.datatables.net/extensions/Editor/js/editor.bootstrap4.min.js"></script>
        <script src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
        <script src="https://cdn.datatables.net/plug-ins/1.10.19/dataRender/datetime.js"></script>

My javascript:

 <script type="text/javascript">

     $(document).ready(function () {

            var table = $('#summary').DataTable({

                dom: 'Bfrtip',
                buttons: [
                    'excel',
                    'csv',
                    'pdf', 'print'],
                columnDefs: [ {
                       targets: 0,
                       render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )
    } ],

                "order": [0, 'desc'],

                "aoColumnDefs": [
                        {"bSearchable": false, "aTargets": [11, 12, 13, 14]}
                    ],
                language: {
                        processing: "Traitement en cours...",
                        search: "Rechercher:",
                        lengthMenu: "Afficher _MENU_ éléments",
                        info: "Affichage de l'élement _START_ à _END_ sur _TOTAL_ éléments",
                        infoEmpty: "Affichage de l'élement 0 à 0 sur 0 éléments",
                        infoFiltered: "(filtré de _MAX_ éléments au total)",
                        infoPostFix: "",
                        loadingRecords: "Chargement en cours...",
                        zeroRecords: "Aucun élément à afficher",
                        emptyTable: "Aucune donnée disponible dans le tableau",
                paginate: {
                            first: "Premier",
                            previous: "Précédent",
                            next: "Suivant",
                            last: "Dernier"
                        },
                aria: {
                            sortAscending: ": activer pour trier la colonne par ordre croissant",
                            sortDescending: ": activer pour trier la colonne par ordre décroissant"
                        }
                    }

                });

            });

    </script>

DAustin · October 2018

I'm from the UK and had the same problem!

Solution is to add a data-sort="yyyy-MM-dd" to the cell (Where "yyyy-MM-dd" is your formatted date)

This will ignore the cell's format of dd/MM/yyyy and sort by yyyy-MM-dd instead

eg:

<td data-sort="2018-10-10">10/10/2018</td>

colin · October 2018

This example here may also help,

Cheers,

Colin

delph49 · October 2018

Thank you very much DAustin, it solves my problem with dates.
I still have the problem with Excel now.

delph49 · October 2018

Finally I have another problem with the DAustin solution: The dates sort well when I arrive on the page, but I can not sort them after if I want them in the other direction.
And colin solution: that's what I did before

colin · October 2018

It sounds like there may be some formatting problems with the data perhaps. We're happy to take a look, but it would help, as per the forum rules, if you could link to a running test case showing the issue so we can offer some help. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

delph49 · October 2018

Thank you for your reply. I did a running test case here: http://live.datatables.net/dojobene/2/
Sorting is not done on the date.

delph49 · October 2018

And the Excel don't work too.

colin · October 2018

@DAustin meant to change the data-sort to be the date, i.e. data-sort="2018-04-05", but that would require a change to how the HTML is created.

Alternatively, this here should do the trick - it's using Moment.js to convert the date type.

Cheers,

Colin

delph49 · October 2018

Thank you, but on my application, it does not work. I don't understand.

colin · October 2018

When you say it doesn't work, what does that mean? Are you getting console errors (maybe moment.JS isn't included)? Does it display correctly but not sort?

It would be worth looking more into it those things, and if no joy, post a link to your application.

delph49 · October 2018

There aren't have any errors in console. It display but sorting doesn't work. I have include the same script and css that on my running test case.

colin · October 2018

Yep, understood, but if it's working on your test case that I modified, but not your application, then something must be different...

delph49 · October 2018

Yes it's sure but I don't see what.

delph49 · October 2018

Problem solve for dates. I put:

$(document).ready(function () {
$.fn.dataTable.moment( 'DD-MM-YYYY'); 
            var table = $('#summary').DataTable({
 "order": [0, 'desc']});

I had already tried without result and there it worked ...

For Excel, I have always the problem.

DAustin · October 2018

Excel's auto formatting can be a pain. I think extending the excel button to output that column within in quotes may help. I know inside excel you can add a single quote to the start of a string to stop it auto formatting.

Unfortunately I'm not familiar with customizing the button output though, sorry

delph49 · October 2018

Thank you for your reply. In the end, I put the CSV that works. There will be no excel at once.

DataTables: problem with date and with excel

DataTables: problem with date and with excel

Answers

Howdy, Stranger!

Categories

DataTables

DataTables: problem with date and with excel

DataTables: problem with date and with excel

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables