Some data is missing in exported excel file from datatable

Some data is missing in exported excel file from datatable

sahisahi Posts: 1Questions: 1Answers: 0
edited July 2021 in DataTables

Downloading a .xlsx file from datatable gives "We found a problem with some content in 'FileName.xlsx'.Do you want us to try to recover as much as we can?
for few data it is working fine but for the large data only it happens
My code is as follows,

 $('#summary-tbl').DataTable({
        searching: true,
        paging: true,
        info: true,
        responsive: true,
        ordering:true,
        "lengthMenu": [[10,25, 100, -1], [10,25, 100, "All"]],
        "ajax": "xxxxxxxxx",
        "columns": [
            {"data": "member_ref_id"},
            {"data": "member_id"},
            {"data": "name"},
        ],
        "bPaginate": true,
        "order": [[ 0, "desc" ]],
        "bProcessing": false,
        "bServerSide": true,
        dom: 'Blfrtip',
        buttons: [
            {
                extend: 'excelHtml5',
                className: 'btn excel',
                title:null,
                customizeData: function (xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var downrows = 3;
                    var clRow = $('row', sheet);

                    //update Row
                    clRow.each(function () {
                        var attr = $(this).attr('r');
                        var ind = parseInt(attr);
                        ind = ind + downrows;
                        $(this).attr("r", ind);
                    });

                    // Create row before data
                    $('row c ', sheet).each(function (index) {
                        var attr = $(this).attr('r');
                        var pre = attr.substring(0, 2);
                        var ind = parseInt(attr.substring(2, attr.length));
                        ind = ind + downrows;
                        $(this).attr("r", pre + ind);
                    });

                    function Addrow(index, data) {
                        var row = sheet.createElement('row');
                        row.setAttribute("r", index);
                        for (i = 0; i < data.length; i++) {
                            var key = data[i].key;
                            var value = data[i].value;

                            var c  = sheet.createElement('c');
                            c.setAttribute("t", "inlineStr");
                            c.setAttribute("s", "2");
                            c.setAttribute("r", key + index);

                            var is = sheet.createElement('is');
                            var t = sheet.createElement('t');
                            var text = sheet.createTextNode(value)

                            t.appendChild(text);
                            is.appendChild(t);
                            c.appendChild(is);

                            row.appendChild(c);
                        }

                        return row;
                    }

                    //insert
                    var r1 = Addrow(1, [{ key: 'A', value: 'xxxxxxxxxxx' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);
                    var r2 = Addrow(2, [{ key: 'A', value: 'xxxxxxxxxxxxxx' }, { key: 'B', value: '' }, { key: 'C', value: ''}]);
                    var r3 = Addrow(3, [{ key: 'A', value: '' }, { key: 'B', value: '' }, { key: 'C', value: '' }]);

                    var sheetData = sheet.getElementsByTagName('sheetData')[0];

                    sheetData.insertBefore(r3,sheetData.childNodes[0]);
                    sheetData.insertBefore(r2,sheetData.childNodes[0]);
                    sheetData.insertBefore(r1,sheetData.childNodes[0]);
                },
                filename: 'Member Summary Report',
            },
        ]
    });

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Answers

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

    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

Sign In or Register to comment.