Multiple DataTable Export in to Multiple worksheet

Multiple DataTable Export in to Multiple worksheet

mujahidateebmujahidateeb Posts: 3Questions: 1Answers: 0

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

Answers

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • mukeshpmukeshp Posts: 8Questions: 0Answers: 0

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    Hi,

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

    Allan

  • mukeshpmukeshp Posts: 8Questions: 0Answers: 0

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

  • mukeshpmukeshp Posts: 8Questions: 0Answers: 0

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

  • mukeshpmukeshp Posts: 8Questions: 0Answers: 0

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

  • ayellayell Posts: 1Questions: 0Answers: 0

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

  • mukeshpmukeshp Posts: 8Questions: 0Answers: 0

    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?

  • rumble1701arumble1701a Posts: 1Questions: 0Answers: 0

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

  • vladslavvladslav Posts: 1Questions: 0Answers: 0

    Updated file based on @mukeshp code

  • jt_sanjt_san Posts: 1Questions: 0Answers: 0
    edited January 2019

    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

  • lutpierolutpiero Posts: 1Questions: 0Answers: 0

    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;
                    }
                }]
            });
    
  • minhalminhal Posts: 76Questions: 12Answers: 0

    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>

  • swethasswethas Posts: 1Questions: 0Answers: 0

    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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
This discussion has been closed.