Excel export throwing error in MS excel

simark95

var prodajax = "<?php echo admin_url('admin-ajax.php?action=skap_prod_table');?>";
    var prodtable = $('#product-table').DataTable({
        dom: 'Blfrtip',
        pageLength: 20,
        ajax: prodajax,
        rowId: 'id',
        columns: [
            { "data": "product_number" },
            { "data": "name" },
            { "data": "product_tab" },
            { "data": "purchase_price" },
            { "data": "no_price" },
            { "data": "nor_price_tax" },
            { "data": "product_status" },
            { "data": "producturl"},
            { "data": "producturl2"},
            {
                "className":      'details-control-2',
                "orderable":      false,
                "data":           null,
                "defaultContent": '<a href="#" class="edit-prod">'+
                                        '<i class="fa fa-pencil"></i>'+
                                    '</a>'+
                                    '<a href="#" class="quick-view"><i class="fa fa-chevron-circle-down" aria-hidden="true"></i></a>'
            }
        ],
        buttons: [ {
            extend: 'excelHtml5',
            exportOptions: {
                    columns: [ 0, 1, 2, 4,7,8 ]
                },
            title: '',
            customize: function( xlsx ) {

                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                $('row c', sheet).each( function () {

                    if ( $('is t', this).text().indexOf("http") === 0 ) {
                        $(this).append('<f>' + 'HYPERLINK("'+$('is t', this).text()+'","'+$('is t', this).text()+'")'+ '</f>');
                        $(this).attr( 's', '2' );
                    }
                });
                }
        } ],
        columnDefs: [
            {
                "targets": [ 7 ],
                "visible": false,
            },
            {
                "targets": [ 8 ],
                "visible": false
            }, { type: numbersType, targets: 0 }
        ],
        order: [[0, 'asc']]
    });

As it can be seen there seems to be a parsing error with file whenever I open it in MS excel (works fine in WPS, google sheets). I have some URLs in my file that the client wants directly clickable as they are just strings/text otherwise. So I included the customize function which seems to be the cause of the issue, because if i comment out that function, it works and opens up fine.

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. 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

simark95

buttons: [ {
            extend: 'excelHtml5',
            title: '',
            customize: function( xlsx ) {
 
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                $('row c', sheet).each( function () {
 
                    if ( $('is t', this).text().indexOf("http") === 0 ) {
                        $(this).append('<f>' + 'HYPERLINK("'+$('is t', this).text()+'","'+$('is t', this).text()+'")'+ '</f>');
                        $(this).attr( 's', '2' );
                    }
                });
                }
        } ],

I am trying to convert text to hyperlinks in excel so that they are clickable directly. The above snippet throws an error as I have mentioned earlier in MS office. (screenshots attached)