Add row then Export datatable to excel

Lance08

Hi guys,

To the point, i am add 3 row before get my data from database.

The export data to excel is fine if the column between A - Z, but if the column higher than Z, like AA1, AB1. If the column is null or empty , the data will move to beside column or first AA column.

Example if first data , the data from column A1- AD1, and my column AA1 and AC1 is empty/null.
So when i export to excel, column AA1 and AC1 got data from AB1 and AD1 and make column AB1 & AD1 empty / null.

Im trying to make a condition IF and its work, but that make my code so slow to convert to excel.

My code is

customize: function (xlsx) {
        console.log(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);
        });
 
        // Update  row > c
        $('row c ', sheet).each(function () {
            var attr = $(this).attr('r');
    if (attr.length == 3)
            {
                  if (attr.substring(1, 2) >= 0) 
        {

                   pre = attr.substring(0, 1);
                   var ind = parseInt(attr.substring(1, attr.length));
                }
                 else 
                {
                   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) {
            msg='<row r="'+index+'">'
            for(i=0;i<data.length;i++){
                var key=data[i].k;
                var value=data[i].v;
                msg += '<c t="inlineStr" r="' + key + index + '" s="42">';
                msg += '<is>';
                msg +=  '<t>'+value+'</t>';
                msg+=  '</is>';
                msg+='</c>';
            }
            msg += '</row>';
            return msg;
        }
 
        //insert
        var r1 = Addrow(1, [{ k: 'A', v: 'ColA' }, { k: 'B', v: '' }, { k: 'C', v: '' }]);
        var r2 = Addrow(2, [{ k: 'A', v: '' }, { k: 'B', v: 'ColB' }, { k: 'C', v: '' }]);
        var r3 = Addrow(3, [{ k: 'A', v: '' }, { k: 'B', v: '' }, { k: 'C', v: 'ColC' }]);
        
        sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2+ r3+ sheet.childNodes[0].childNodes[1].innerHTML;
    }

Is there any way more faster to add 3 row first then get the data from data base?
cause if i export 1000 row, the export will freeze.

Thanks,

allan

So to summarise, if you don't have a customize function, but you do have more than 26 columns, it fails? Is that correct? Can you give me a link to a demo showing that issue?

Allan

Lance08

Hi Allan,

you do have more than 26 columns, it fails? Is that correct? -> the data still got export to excel, but the data in wrong column. cause of empty column, so the data move to a empty column.

I fix it with modifty this code,
pre = attr.substring(0, 1);
var ind = parseInt(attr.substring(1, attr.length));

But if you export many data to excel, the program got freeze when export.

i just want to know, is there any way to to add 3 row before get data from database when export to excel ?

Thanks.