how to merge cells in excel when exporting datatables?

elen11

When I export datatables.net to excel file I need to merge few cells. How to do it?
I tried code below, but it doesn't work:

customize: function( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];

            $('row c[r^="C2"]', sheet).attr( 'rowspan', '3' );
            $('row c[r^="A5"]', sheet).attr( 'colspan', '2' );
        }

allan

There is no rowspan or colspan in Open Spreadsheet XML files - you need to use mergeCells instead. This is how Buttons does it although for full details you'd need to refer to the Open Spreadsheet specifications - that is beyond the scope of DataTables.

Allan

elen11

Allan, thank you for your answer. I will try with mergeCells

ManiPS

Allan and elen11
What should be passed in mergeCells method arguments are row and colspan?
How to call this method? Can you pls provide me the single line of sample code for it.

var mergeCells = function ( row, colspan ) {
var mergeCells = $('mergeCells', rels);

        mergeCells[0].appendChild( _createNode( rels, 'mergeCell', {
            attr: {
                ref: 'A'+row+':'+createCellPos(colspan)+row
            }
        } ) );
        mergeCells.attr( 'count', mergeCells.attr( 'count' )+1 );
        $('row:eq('+(row-1)+') c', rels).attr( 's', '51' ); // centre
    };

ManiPS

@allan and @elen11

How to use mergeCells method. Any example pls

6095959

        customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        var mergeCells = $('mergeCells', sheet);

                        mergeCells[0].appendChild( _createNode( sheet, 'mergeCell', {
                          attr: {
                            ref: 'A1:E1'
                          }
                        } ) );

                        mergeCells.attr( 'count', mergeCells.attr( 'count' )+1 );

                        function _createNode( doc, nodeName, opts ) {
                          var tempNode = doc.createElement( nodeName );

                          if ( opts ) {
                            if ( opts.attr ) {
                              $(tempNode).attr( opts.attr );
                            }

                            if ( opts.children ) {
                              $.each( opts.children, function ( key, value ) {
                                tempNode.appendChild( value );
                              } );
                            }

                            if ( opts.text !== null && opts.text !== undefined ) {
                              tempNode.appendChild( doc.createTextNode( opts.text ) );
                            }
                          }

                          return tempNode;
                        }

}

harish01

HI Allan,
I am getting an exception (Uncaught TypeError: mergeCells.appendChild is not a function).
How can i solve it?

colin

Hi @harish01 ,

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

harish01

Hi,
Thanks for the reply.
here is the fiddle

https://jsfiddle.net/xs30jz1c/1/

kthorngren

Your test case is not running. You are getting this error in the console:

Uncaught ReferenceError: $ is not defined
at window.onload

Doesn't look like you added the jquery and datatables in JS and CSS include file. Please add those and so we can see your code generate the Uncaught TypeError: mergeCells.appendChild is not a function error.

Kevin

harish01

@kthorngren
Hi,
Thank you for the reply.
I am having Jquery and datatables in js and css.
i want to know what this code does.

var mergeCells = $('mergeCells', sheet);
mergeCells[0].appendChild(_createNode(sheet, 'mergeCell', {
attr: {
ref: 'A1:E1'
}
}));

Thank you.

veeray

customize: function ( xlsx ){
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var colRange = sheet.getElementsByTagName('col').length - 1;
                    //var colRange = createCellPos( lastCol ) + '1';
                    var numrows = 3;
                    var clR = $('row', sheet);

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

                    // Create row before data
                    $('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 + numrows;
                        $(this).attr("r", pre + ind);
                    });

                    function Addrow(index,data, colRange, sheet) {
                        msg='<row r="'+index+'">'
                        for(i=0;i<data.length;i++){
                            var key=data[i].key;
                            var value=data[i].value;
                            var style=data[i].style;
                            mergeCells( index, colRange, sheet );
                            msg += '<c t="inlineStr" s="'+ style +'" r="' + key + index + '">';
                            msg += '<is>';
                            msg +=  '<t>'+value+'</t>';
                            msg+=  '</is>';
                            msg+='</c>';
                        }
                        msg += '</row>';
                        return msg;
                    }

                    var mergeCells = function ( row, colspan, sheet ) {
                        var mergeCells = $('mergeCells', sheet);

                        mergeCells[0].appendChild( _createNode( sheet, 'mergeCell', {
                            attr: {
                                ref: 'A'+row+':'+createCellPos(colspan)+row
                            }
                        } ) );
                        //mergeCells.attr( 'count', mergeCells.attr( 'count' )+1 );
                        //$('row:eq('+(row-1)+') c', sheet).attr( 's', '51' ); // centre
                    };

                    function createCellPos( n ){
                        var ordA = 'A'.charCodeAt(0);
                        var ordZ = 'Z'.charCodeAt(0);
                        var len = ordZ - ordA + 1;
                        var s = "";
                     
                        while( n >= 0 ) {
                            s = String.fromCharCode(n % len + ordA) + s;
                            n = Math.floor(n / len) - 1;
                        }
                     
                        return s;
                    }
                    function _createNode( doc, nodeName, opts ) {
                        var tempNode = doc.createElement( nodeName );

                        if ( opts ) {
                            if ( opts.attr ) {
                                $(tempNode).attr( opts.attr );
                            }

                            if ( opts.children ) {
                                $.each( opts.children, function ( key, value ) {
                                    tempNode.appendChild( value );
                                } );
                            }

                            if ( opts.text !== null && opts.text !== undefined ) {
                                tempNode.appendChild( doc.createTextNode( opts.text ) );
                            }
                        }
                        return tempNode;
                    }

                    //insert
                    var r1 = Addrow(1, [{ key: 'A', value: '', style: '51' }], colRange, sheet);
                    var r2 = Addrow(2, [{ key: 'A', value: '', style: '51' }], colRange, sheet);
                    var r3 = Addrow(3, [{ key: 'A', value: '', style: '51' }], colRange, sheet);

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

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

colin

@veeray nice - thanks for posting,

Colin