Merge Cell when Export Excel Datatables

maria10005

Hi Everyone,

I am trying to mergecell in my excel after Export to Excel Datatables.
How to do it? Can anyone help please.
Thank you in advance

I want to create like this

                 "pageLength": 10,
                "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                "scrollX": true,
                "dom": 'Blfrtip',
          
      "buttons": [{

                    extend: 'excel',
                    title: null,
                    filename:    function () {
                        var d = new Date();
                        var date;
                        var month;
                        var year;

          footer: true,
                    header: true,
                    sheetName: 'Report MSCOTT',
                    exportOptions: {
                        columns: [0,6,7,8,9,10,11]
                    },
                    //customize: _customizeExcelOptions
                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];
                        var numrows = 8;
                        var downrows = 7;
                        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) {
                            msg='<row r="'+index+'">'
                            for(i=0;i<data.length;i++){
                                var key=data[i].key;
                                var value=data[i].value;
                                msg += '<c t="inlineStr" r="' + key + index + '">';
                                msg += '<is>';
                                msg +=  '<t>'+value+'</t>';
                                msg+=  '</is>';
                                msg+='</c>';
                            }
                            msg += '</row>';
                            return msg;
                        }
                        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;
                        }
                        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
                        };
                    
                        

                        var table = $('#mscottTable').DataTable();
                        var data = table.rows().data();
                        var b = data[0];
                        var c = b[5];
                        var d = b[1];
                        var e = b[2];

                        //setting max row + 2 if include header and footer table
                        var max_row = table.column(0).data().length;// + 2;
                        //alert(max_row);

                        //insert
                        var r1 = Addrow(1, [{ key: 'B', value: 'APPENDIX 1' }]);
                        var r2 = Addrow(2, [{ key: 'E', value: 'COVER MEMO' }]);
                        var r3 = Addrow(3, [{ key: 'B', value: 'FROM :' }, { key: 'C', value: d }, { key: 'G', value: 'Contact No.'+ c }]);
                        var r4 = Addrow(4, [{ key: 'B', value: 'TO :' }, { key: 'C', value: e }]);
                        var r5 = Addrow(5, [{ key: 'B', value: 'DATE :' }, { key: 'C', value: date }]);
                        var r6 = Addrow(6, [{ key: 'B', value: 'BATCH :' }, { key: 'C', value: y}]);
                        var r7 = Addrow(7, [{ key: 'B', value: 'Type of Instruction :' }, { key: 'C', value: 'OTT' }]);
                        var r8 = Addrow(8, [{ key: 'A', value: '' }]);

                        //insert footer
                        //+2 space footer table to report footer
                        var r9 = Addrow(numrows + 2 + max_row + 2, [{ key: 'B', value: 'Checked by:' }, { key: 'E', value: 'Confirmed by:' }]);
                        //+5 space row 1 report footer to row 2 report footer
                        var r10 = Addrow(numrows + 2 + max_row + 2 + 5, [{ key: 'B', value: 'SS AUTHORIZERS' }, { key: 'E', value: 'CMS' }]);


                        sheet.childNodes[0].childNodes[1].innerHTML = r1 + r2 + r3 + r4 + r5 + r6 + r7 + r8 + sheet.childNodes[0].childNodes[1].innerHTML + r9 + r10;

      

colin

There are a few threads that may help, such as here and here, hopefully one of those will set you on the right path,

Colin