Excel Export Add Rows and Data

uniqueginun

hi @allan i tried to use your code to add 3 rows before table header but i got error on the cosole : Uncaught SyntaxError: Unexpected identifier

    $(document).ready(function() {

 
    $('#table').DataTable( {
        dom: 'Bfrtip',
        buttons: [
           {
            extend: 'excelHtml5',
            filename: 'test',
            text: 'Save as Excel',
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');
            var pre = attr.substring(0, 1);
            var ind = parseInt(attr.substring(1, 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+ r4+ sheet.childNodes[0].childNodes[1].innerHTML;
    }
        }
        ],
        
    } );
} );

my js files

https://code.jquery.com/jquery-3.3.1.js
https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.jshttps://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.18/b-1.5.2/b-html5-1.5.2/datatables.min.js
https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js
https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/pdfmake.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.32/vfs_fonts.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js
https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js

kthorngren

Uncaught SyntaxError: Unexpected identifier

That is a syntax error in your JS code. Which line is the error referring to?

I quick look and the above looks ok, maybe its in another area of your code.

Kevin

Lance08

sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2+ r3+ r4+ sheet.childNodes[0].childNodes[1].innerHTML;

maybe erase that r4.

because you only have r1 - r3 , not r4.

Vz

Pats2kDynasty

This relates to creating a row but I want to create column instead. Why?... because DataTables does not export child rows to Excel.

In customizeData I have already found my matching "id"s from what is displayed and my master data array, so I have the data from the child rows. Now I just need to add an Excel column.

How do you create a column?

customizeData: function(data) {
    $(data.body).each(function (i) {
        var toFind= $(this)[0];
        var arrFound = arrMainData.filter(function(el) {
              return el.id === toFind;
        });
                                
        if(arrFound) { 
            //add and move columns around
            //add required data
        }
    });
}

LiamG

HI, is there the possibility to add new rows (not as header) and shift the rest of the content?
Thank in advance.

LiamG

Here my Jsbin --> https://jsbin.com/menujozine/1/edit?html,output

PLEASE HELP ME!!!

colin

See Allan's comment on the first page of this thread:

You can use the customize option to modify the created XLSX document. Basically you would need to move all cells down one row and then insert your message in the top row. Probably not trivial to do I'm afraid.

Colin

beenu_tyagi

I want to merge and center the rows. Can anyone tell how to do this in datatable export excel?

colin

It's very fiddly to do - you'll need to modify the XML in the customize callback,

Colin