how to merge cells in excel when exporting datatables?

how to merge cells in excel when exporting datatables?

elen11elen11 Posts: 6Questions: 4Answers: 0

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' );
        }

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 56,819Questions: 1Answers: 9,029 Site admin
    Answer ✓

    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

  • elen11elen11 Posts: 6Questions: 4Answers: 0

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

  • ManiPSManiPS Posts: 28Questions: 12Answers: 0
    edited May 2018

    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
        };
    
  • ManiPSManiPS Posts: 28Questions: 12Answers: 0

    @allan and @elen11

    How to use mergeCells method. Any example pls

  • 60959596095959 Posts: 1Questions: 0Answers: 0
            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;
                            }
    

    }

  • harish01harish01 Posts: 6Questions: 1Answers: 0

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

  • colincolin Posts: 14,439Questions: 1Answers: 2,459

    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

  • harish01harish01 Posts: 6Questions: 1Answers: 0

    Hi,
    Thanks for the reply.
    here is the fiddle

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

  • kthorngrenkthorngren Posts: 16,746Questions: 25Answers: 3,966

    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

  • harish01harish01 Posts: 6Questions: 1Answers: 0

    @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.

  • veerayveeray Posts: 1Questions: 0Answers: 0
    edited October 2020
    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.

  • colincolin Posts: 14,439Questions: 1Answers: 2,459

    @veeray nice - thanks for posting,

    Colin

This discussion has been closed.