jquery datatable excel export colspan, rowspan, multiple headers

2021. 3. 31. 11:08JavaScript/JQuery

datatables.net/forums/discussion/36045/excel-export-add-rows-and-data

        dom: 'Bfrtip',
        buttons: [{
                extend: 'excelHtml5',
                header: false,
                footer: true, //autoFilter: true,
                title: '타 이 틀',
                customize: function(xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];

                    // colspan
                    var mergeCells = $('mergeCells', sheet);

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

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

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

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

                    // rowspan
                    mergeCells[0].appendChild( _createNode( sheet, 'mergeCell', {
                      attr: {
                        ref: ['A2:A3']
                      }
                    }));

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

                    mergeCells.attr( 'count', mergeCells.attr( 'count' )+1 );
     
                    // 서식 입히기. 서식번호 참고--> https://datatables.net/reference/button/excelHtml5#Examples
					// 한꺼번에 여러개의 서식입히기 안되는 것 같음
                    $('row[r=1] c', sheet).attr( 's', '47');
                    $('row[r=2] c', sheet).attr( 's', '47');
                    $('row[r=3] c', sheet).attr( 's', '47');


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

                },
                customizeData: function(data) {
                    console.log('data: ');
                    console.log(data);
                    //We want the first line so we disabled the header above. Let's add in our descriptions. Then we're going to add them to the top of the body and do the bolding ourselves with the customize function.
                    var desc = [
                        ['연/월/일', '','', '내용'],
                    ]; // colspan 갯수대로 '' 넣어줌
                    data.body.unshift(data.header);
                    for (var i = 0; i < desc.length; i++) {
                        data.body.unshift(desc[i]);
                    };
                },

                filename: '타 이 틀',
                text: '<i class="material-icons left" style="font-size: 1.3rem; line-height: inherit;">download</i><span style="font-size: 14px;">다운로드 버튼</span>',
                exportOptions: {
                    orthogonal: 'export',
                    // columns: []    
                },
            },
        ],