Multiple DataTable Export in to Multiple worksheet

mujahidateeb

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

allan

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

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

Hi,

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

Allan

mukeshp

/*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

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

mukeshp

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

ayell

@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

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

@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

Updated file based on @mukeshp code

jt_san

This is how I got this working, in case anyone is having difficulties:

• Include the version of buttons.html5.js given by vladslav in the above post in your site (following the main datatables.js file)

• Initialise the table extending excelHtml5 to include the multitables option. The multitables option should include an array of the tables you want to export, with the table ID as the key and the value the sheet name as you want it to appear in the Excel export file. eg. here I am exporting tables with ID journalTable, bookTable and databaseTable, which will be exported into worksheets named 'Journals', 'Books' and 'Databases' respectively:

              buttons: [
                  { extend: 'excelHtml5', multitables:{journalTable:'Journals',bookTable:'Books',databaseTable:'Databases'} }
              ]
  

Hope that helps

lutpiero

Hi,
I'm trying your suggest @jt_san I got this error
this.processing is not a function.

$('#detail_table,#detail_table_design,#detail_table_review_design').dataTable({"pageLength": 50,dom: 'Bfrtip',buttons: [                
                {extend: 'excelHtml5', multitables:{detail_table:'Detail',detail_table_design:'Design',detail_table_review_design:'Review Design'}}
            ],
            columnDefs: [{
                targets: '_all',
                render: function ( data, type, row ) {
                    return type === 'display' && data.length > 10 ?
                        data.substr( 0, 10 ) +'…' : data;
                }
            }]
        });

minhal

Hi @jt_san ,

I tried the way you have told but I am not able to get it done correctly. Now I can export a empty excel file with errors to it.

The errors are as follows:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error160000_01.xml</logFileName>

<summary>Errors were detected in file 'C:\Users\VFC92816\Downloads\download.xlsx'</summary>

-<removedRecords>

<removedRecord>Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)</removedRecord>

</removedRecords>

-<repairedRecords>

<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>

</repairedRecords>

</recoveryLog>

swethas

Hi,
we have a requirement to download multiple sheets in single Excel file. I tried adding above code
buttons: [
{ extend: 'excelHtml5', multitables:{journalTable:'Journals',bookTable:'Books',databaseTable:'Databases'} }
], But each table data is getting downloaded as each individual excel file.
I want all tables data in single excel file, under different sheet

colin

Kevin did an example of that on this thread: https://datatables.net/forums/discussion/58998/excel-export-create-new-sheet-example#latest

Colin