combining the use of customizeData and customize whilst extending excelhtml5 button

l0ckm4

I am extending the functionality of the excelhtml5 button to cater for UK formatted date times e.g. dd/mm/yyyy h:i:s format.

I have it working but read on here that customizeData is an old method. How could I combine the functionality of the code I have in the customizeData function into the customize function as i cannot get to the data (or at least I dont understand how to. Ideally I would love to be able to read the class of a cell to determine if it has the class of ukdatetime and then format the excel cell with the custom format I have added into buttons.html5.js

The relevant code is

    <script>
    
    $( function() {
            
        $( "#tabs" ).tabs();
        $.fn.dataTable.moment( 'DD/MM/YYYY HH:mm:ss' );
                
        var table_config =  {
            "dom": "Bfrtip",
            "scrollY": "700px",
            "scrollCollapse": true,
            "paging": false,
            "order": [[3, "desc"]],
            "select": true,
            "ajax": '<?=$_SERVER['PHP_SELF']?>?action=Ajax',
            "columns": [
                {"data":"message_id"}, 
                {"data":"sender_id"},
                {"data":"to"}, 
                {"data":"sent_at_f"}, 
                {"data":"delivered_at_f"}, 
                {"data":"price"}, 
                {"data":"status"}
            ],
            "columnDefs": [
                {"targets":[0],"className":"dt-body-left message_id"},
                {"targets":[1],"className":"dt-body-center sender_id"},
                {"targets":[2],"className":"dt-body-right to"},
                {"targets":[3],"className":"dt-body-right sent_at_f ukdatetime"},
                {"targets":[4],"className":"dt-body-right delivered_at_f ukdatetime"},
                {"targets":[5],"className":"dt-body-center price"},
                {"targets":[6],"className":"dt-body-right status"}
            ],
            "buttons": [
                "copyHtml5",
                {
                    "extend": 'excelHtml5',
                    "title": null,
                    "customizeData": function(data) {
                        for(var i = 0; i < data.body.length; i++) {
                            data.body[i][0] = '\u200C' + data.body[i][0]; // Force excel not to change long number
                            data.body[i][3] = dateToOADate(ukdatetime_to_date(data.body[i][3])); // Convert UK datetime string to excel date value
                            data.body[i][4] = dateToOADate(ukdatetime_to_date(data.body[i][4])); // Convert UK datetime string to excel date value
                        }
                    },
                    "customize": function( xlsx ) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        $('row', sheet).each(function() {
                            console.log($(this));
                        });
                        $('row c[r^="B"]:gt(0)',sheet).attr('s','51'); // Center Align
                        $('row c[r^="D"]:gt(0)',sheet).attr('s','74'); // Custom UK Date Format
                        $('row c[r^="E"]:gt(0)',sheet).attr('s','74'); // Custom UK Date Format
                        $('row c[r^="F"]:gt(0)',sheet).attr('s','51'); // Center Align
                    }
                },
                "csvHtml5",
                "pdfHtml5",
                "print"
            ]
        };

        var table = $('#mytable').DataTable(table_config);      
        table.on( 'click', 'tr', function () {
            if ( $(this).hasClass('selected') ) {
                $(this).removeClass('selected');
            } else {
                table.$('tr.selected').removeClass('selected');
                $(this).addClass('selected');
            }
        });
    });

    function ukdatetime_to_date(date) {
        //Assume date is in the format dd/mm/yyyy H:i:s
        var tmp = date;
        var year = tmp[6]+tmp[7]+tmp[8]+tmp[9];
        var month = (tmp[3]+tmp[4])-1;
        var day = tmp[0]+tmp[1];
        var hours = tmp[11]+tmp[12];
        var minutes = tmp[14]+tmp[15];
        var seconds = tmp[17]+tmp[18];
        return new Date(year,month,day,hours,minutes,seconds);
    }

    function dateToOADate (date) {
        var temp = new Date(date);
        // Set temp to start of day and get whole days between dates,
        var days = Math.round((temp.setHours(0,0,0,0) - new Date(1899, 11, 30)) / 8.64e7);
        // Get decimal part of day, OADate always assumes 24 hours in day
        var partDay = (Math.abs((date - temp) % 8.64e7) / 8.64e7).toFixed(10);
        return days + partDay.substr(1);
    }
        
    </script>   

Many thanks.

colin

There are two ways you can - you can use orthogonal data, or you can modify the exported data.

Hope one of those does the trick,

Colin