DataTables: problem with date and with excel

DataTables: problem with date and with excel

delph49delph49 Posts: 24Questions: 4Answers: 0

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>

Answers

  • DAustinDAustin Posts: 16Questions: 0Answers: 1
    edited 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>

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    This example here may also help,

    Cheers,

    Colin

  • delph49delph49 Posts: 24Questions: 4Answers: 0

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

  • delph49delph49 Posts: 24Questions: 4Answers: 0
    edited 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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • delph49delph49 Posts: 24Questions: 4Answers: 0

    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.

  • delph49delph49 Posts: 24Questions: 4Answers: 0

    And the Excel don't work too.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    @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

  • delph49delph49 Posts: 24Questions: 4Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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.

  • delph49delph49 Posts: 24Questions: 4Answers: 0

    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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

  • delph49delph49 Posts: 24Questions: 4Answers: 0

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

  • delph49delph49 Posts: 24Questions: 4Answers: 0
    edited 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.

  • DAustinDAustin Posts: 16Questions: 0Answers: 1

    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

  • delph49delph49 Posts: 24Questions: 4Answers: 0
    edited October 2018

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

This discussion has been closed.