Multiple DataTable Export in to Multiple worksheet

mujahidateeb · December 2016

How to export multiple datatables in to multiple worksheets in single Excel workbook on single click using Datatable export buttons. Please help me.

allan · December 2016

You would need to use the customize callback of the excelHtml5 button type to modify the XLSX XML that Buttons creates. Buttons does not create multiple worksheets out of the box.

Allan

mukeshp · June 2017

I tried tweaking the buttons.html5.js successfully to export multiple datatables in to multiple worksheets in single Excel workbook on single click using Datatable export buttons. Before posting the code here, I would like to have it reviewed by Allan to see if it is correct and if yes, can it be included in the future releases for the benefit of the community

allan · June 2017

Hi,

Paste it in here, and I'll be happy to take a look

Allan

mukeshp · June 2017

/*I've tested exporting 6 datatables to 6 separate worksheets in a single workbook.My Buttons config has an extra option 'multitables' specified for the solution to work which basically has TableId:Sheetname and looks like this:
extend: 'excelHtml5',
multitables:{PL:'Profit',CL:'Liabilities',BS:'Assets',FF:'FundFlow',WC:'WkCap',FP:'FinPos'},
Since there seems to be a limitation on the characters allowed in this message unable to paste the entire contents of file buttons.html5.js, I am pasting the entire class DataTable.ext.buttons.excelHtml5 where the changes have been made. Also changed Relationship Id="rId0" instead of rId2 in excelStrings array which is not pasted here due to limitation on pasting max chars here
*/
//====Start of code pasted from buttons.html5.js===
DataTable.ext.buttons.excelHtml5 = {
className: 'buttons-excel buttons-html5',

available: function () {
    return window.FileReader !== undefined && _jsZip() !== undefined && ! _isDuffSafari() && _serialiser;
},

text: function ( dt ) {
    return dt.i18n( 'buttons.excel', 'Excel' );
},

action: function ( e, dt, button, config ) {
    this.processing( true );

    var that = this;
    var rowPos = 0;
    var getXml = function ( type ) {
        var str = excelStrings[ type ];

        //str = str.replace( /xmlns:/g, 'xmlns_' ).replace( /mc:/g, 'mc_' );

        return $.parseXML( str );
    };

    var relsarray={};

    var xlsx = {
        _rels: {
            ".rels": getXml('_rels/.rels')
        },
        xl: {
            _rels: {
                "workbook.xml.rels": getXml('xl/_rels/workbook.xml.rels')
            },
            "workbook.xml": getXml('xl/workbook.xml'),
            "styles.xml": getXml('xl/styles.xml'),
            "worksheets": {
                //"sheet1.xml": rels1
            }

        },
        "[Content_Types].xml": getXml('[Content_Types].xml')
    };
    var counter =0;
    var tabledata = {};
    var currTable;
    if( config.multitables!==undefined && $.isPlainObject(config.multitables) ){            
        for(var key in config.multitables){
            if(undefined === (currTable = $('#'+key).DataTable().buttons.exportData( config.exportOptions )) )
                continue;

            tabledata[key] =  currTable;
            counter++;
            if(1===Object.keys(tabledata).length){
                $("[r\\:id='rId1']",xlsx['xl']['workbook.xml']).attr("name",config.multitables[key]);
            }
            else{
                var t = $("[r\\:id='rId1']",xlsx['xl']['workbook.xml']).clone();
                t.attr("name",config.multitables[key])
                t.attr("sheetId",counter);
                t.attr("r:id","rId"+counter);
                $("sheets",xlsx['xl']['workbook.xml']).append(t);

                var t1 = $("[Id='rId1']",xlsx['xl']['_rels']['workbook.xml.rels']).clone();
                t1.attr("Id","rId"+counter);
                t1.attr("Target","worksheets/sheet"+counter+".xml");
                $("Relationships",xlsx['xl']['_rels']['workbook.xml.rels']).append(t1);

                var t2 = $("[PartName='/xl/worksheets/sheet1.xml']",xlsx['[Content_Types].xml']).clone();
                t2.attr("PartName","/xl/worksheets/sheet"+counter+".xml");
                $("Types",xlsx['[Content_Types].xml']).append(t2);
            }               
        }
    }
    else{
        //$("[r\\:id='rId1']",xlsx['xl']['workbook.xml']).attr("name",dt.table().node().id);  this starts putting the TableId as the sheet name which might not be preferable
        tabledata[dt.table().node().id]=dt.buttons.exportData( config.exportOptions );
    }

    var currentRow, rowNode;
    var addRow = function ( row,rels,relsGet ) {
        currentRow = rowPos+1;
        rowNode = _createNode( rels, "row", { attr: {r:currentRow} } );

        for ( var i=0, ien=row.length ; i<ien ; i++ ) {
            // Concat both the Cell Columns as a letter and the Row of the cell.
            var cellId = createCellPos(i) + '' + currentRow;
            var cell = null;

            // For null, undefined of blank cell, continue so it doesn't create the _createNode
            if ( row[i] === null || row[i] === undefined || row[i] === '' ) {
                continue;
            }

            row[i] = $.trim( row[i] );

            // Special number formatting options
            for ( var j=0, jen=_excelSpecials.length ; j<jen ; j++ ) {
                var special = _excelSpecials[j];

                // TODO Need to provide the ability for the specials to say
                // if they are returning a string, since at the moment it is
                // assumed to be a number
                if ( row[i].match && ! row[i].match(/^0\d+/) && row[i].match( special.match ) ) {
                    var val = row[i].replace(/[^\d\.\-]/g, '');

                    if ( special.fmt ) {
                        val = special.fmt( val );
                    }

                    cell = _createNode( rels, 'c', {
                        attr: {
                            r: cellId,
                            s: special.style
                        },
                        children: [
                            _createNode( rels, 'v', { text: val } )
                        ]
                    } );

                    break;
                }
            }

            if ( ! cell ) {
                if ( typeof row[i] === 'number' || (
                    row[i].match &&
                    row[i].match(/^-?\d+(\.\d+)?$/) &&
                    ! row[i].match(/^0\d+/) )
                ) {
                    // Detect numbers - don't match numbers with leading zeros
                    // or a negative anywhere but the start
                    cell = _createNode( rels, 'c', {
                        attr: {
                            t: 'n',
                            r: cellId
                        },
                        children: [
                            _createNode( rels, 'v', { text: row[i] } )
                        ]
                    } );
                }
                else {
                    // String output - replace non standard characters for text output
                    var text = ! row[i].replace ?
                        row[i] :
                        row[i].replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');

                    cell = _createNode( rels, 'c', {
                        attr: {
                            t: 'inlineStr',
                            r: cellId
                        },
                        children:{
                            row: _createNode( rels, 'is', {
                                children: {
                                    row: _createNode( rels, 't', {
                                        text: text
                                    } )
                                }
                            } )
                        }
                    } );
                }
            }

            rowNode.appendChild( cell );
        }

        relsGet.appendChild(rowNode);
        rowPos++;
    };

        //$( 'sheets sheet', xlsx.xl['workbook.xml'] ).attr( 'name', _sheetname( config ) );

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

        mergeCells.append( '<mergeCell ref="A'+row+':'+createCellPos(colspan)+row+'" />' );
        mergeCells.attr( 'count', mergeCells.attr( 'count' )+1 );
        $('row:eq('+(row-1)+') c', rels).attr( 's', '51' ); // centre
    };

    var addHdrBodyFtrWidth = function (data,rels,relsGet) {

        rowPos=0;

        if ( config.customizeData ) {
            config.customizeData( data );
        }

    // Title and top messages
    var title = _title( config );
    if ( title ) {
        addRow( [title], rels, relsGet, rowPos );
        mergeCells( rowPos, data.header.length-1,rels );
    }

    var messageTop = _message( dt, config.messageTop, 'top' );
    if ( messageTop ) {
        addRow( [messageTop], rels, relsGet, rowPos );
        mergeCells( rowPos, data.header.length-1,rels );
    }

    // Table itself

        if ( config.header ) {
            addRow( data.header, rels, relsGet, rowPos );
            $('row:last c', rels).attr( 's', '2' ); // bold
        }

        for ( var n=0, ie=data.body.length ; n<ie ; n++ ) {
            addRow( data.body[n], rels, relsGet, rowPos );
        }

        if ( config.footer && data.footer ) {
            addRow( data.footer, rels, relsGet, rowPos);
            $('row:last c', rels).attr( 's', '2' ); // bold
        }

    // Below the table
    var messageBottom = _message( dt, config.messageBottom, 'bottom' );
    if ( messageBottom ) {
        addRow( [messageBottom], rowPos );
        mergeCells( rowPos, data.header.length-1,rels );
    }

        // Set column widths
        var cols = _createNode( rels, 'cols' );
        $('worksheet', rels).prepend( cols );

        for ( var i=0, ien=data.header.length ; i<ien ; i++ ) {
            cols.appendChild( _createNode( rels, 'col', {
                attr: {
                    min: i+1,
                    max: i+1,
                    width: _excelColWidth( data, i ),
                    customWidth: 1
                }
            } ) );
        }
    };
    counter = 0;
    for(key in tabledata){
        counter++;
        relsarray[key] = getXml('xl/worksheets/sheet1.xml');
        var relsGet = relsarray[key].getElementsByTagName( "sheetData" )[0];
        addHdrBodyFtrWidth(tabledata[key], relsarray[key], relsGet);
        xlsx['xl']['worksheets']['sheet'+counter+'.xml'] = relsarray[key];
    }

    // Let the developer customise the document if they want to
    if ( config.customize ) {
        config.customize( xlsx );
    }

    var jszip = _jsZip();
    var zip = new jszip();
    var zipConfig = {
        type: 'blob',
        mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    };

    _addToZip( zip, xlsx );

    if ( zip.generateAsync ) {
        // JSZip 3+
        zip
            .generateAsync( zipConfig )
            .then( function ( blob ) {
                _saveAs( blob, _filename( config ) );
                that.processing( false );
            } );
    }
    else {
        // JSZip 2.5
        _saveAs(
            zip.generate( zipConfig ),
            _filename( config )
        );
        this.processing( false );
    }
},

filename: '*',

extension: '.xlsx',

exportOptions: {},

header: true,

footer: false,

title: '*',

messageTop: '*',

messageButton: '*'

};

====End of code pasted from buttons.html5.js===

mukeshp · June 2017

//The "xl/workbook.xml": of the excelStrings object has:
//===
'<sheets>'+
'<sheet name="sheet1" sheetId="1" r:id="rId1"/>'+
'</sheets>'+
//===

mukeshp · June 2017

And also, the "xl/_rels/workbook.xml.rels":
'<Relationship Id="rId0" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'+

ayell · October 2017

@mukeshp hello. how does this work? I copied your modified code and followed your instructions but I still don't know how to produce multiple worksheets in one .xlsx file.

mukeshp · January 13

Did you copy the new buttons.html5.js with the excelStrings changes?
Do you have your code or a sample fiddle to analyse the issue you are facing?

rumble1701a · June 13

@mukeshp Howdy. Before I begin, I'd like to state that I am a beginner JS programmer. Only recently am I becoming aware of the more advanced programming aspects of Datatables. Please bare with me.
I found this piece of code recently and had a couple of questions. I have implemented the DataTable.ext...into buttons.html5.js but I get an error. JS says Datatable is undefined. My first thought was to define Datatable the same way 'p' is defined. In buttons.html5.js, p = e.fn.datatables. However, this also produces an error, stating that jsZip is not defined. I wondered how this error could be corrected?

Second question deals with the setup on the button. Below is the code I currently have and would like to make sure that I set it up correctly per your instructions:

var table = $('#htmlTable').DataTable({

            "order": [[0, "asc"]], //this ordering is done the initial load of the page only. Afterward, it is user controlled by clicking on the desired column
            "pageLength": 25, //number of data rows per page
            "tableId": 'Sheetname',
            "extend": 'excelHtml5',
            "multitables": { PL: 'Profit', CL: 'Liabilities', BS: 'Assets', FF: 'FundFlow', WC: 'WkCap', FP: 'FinPos' },

            dom: 'lBfrtip',
            buttons: [

                //'copyHtml5',
                'excelHtml5',
               {
                    text: 'Reset Data Table',
                    action: function (e, dt, node, config) {
                        currentList = 'RFI_APPLICATION';
                        appListData(currentList)();
                    }
                }
                //'csvHtml5',
                //'pdfHtml5'
            ]
        });//End Datatable

Thank you for your time.

vladslav · July 9

Updated file based on @mukeshp code

Multiple DataTable Export in to Multiple worksheet

Multiple DataTable Export in to Multiple worksheet

Answers

Howdy, Stranger!

Categories

DataTables