Excel Export create new sheet example

kthorngren

I needed a way to export a second Datatable with main Datatable into multiple worksheets in the same file. I used @F12Magic 's example from this thread as the basis for this example:
http://live.datatables.net/qinoxinu/6/edit

It pulls the data from a second Datatable and places it in a second worksheet. It builds a simple sheet that mimics the basic sheet Datatables outputs. The goal with this example is to allow for ease of reuse in any page. Multiple sheets can easily be created.

The output can be customized to meet your solution requirements. For customization information please refer to the XML Spreadsheet Reference and Office Open XML doc.

Kevin

colin

Nice, that'll be really useful. Thanks for sharing

Vkay

Hi @kthorngren

I have tried to add one more table as example3 and was passing something like

addSheet(xlsx, '#example3', 'My Sheet3', 'Roles', '3');

to export it with other data, but i was un-successful the excel crashed and the file is stated to be corruppted in some form when i tried to open the exported excel.

any help on this???

kthorngren

The addSheet() function has some hard coded statements that need refactored to allow for more sheets to be added. I think there is a thread on the forum, can't find it right now, where someone fixed this code to allow for more than one sheet to be added.

My guess is the changes need to happen in this section of code:

    //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
    //=====================================================================
    var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
    var clone = source.cloneNode(true);
    clone.setAttribute('Id','rId3');  // remove hard coding
    clone.setAttribute('Target','worksheets/sheet2.xml');  // remove hard coding
    xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);
    
    //Add second sheet to xl/workbook.xml => <workbook><sheets>
    //=========================================================
    var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
    var clone = source.cloneNode(true);
    clone.setAttribute('name', name);
    clone.setAttribute('sheetId', sheetId);
    clone.setAttribute('r:id','rId3');  // remove hard coding
    xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);

I added the comment // remove hard coding of the places I think need changing.

Kevin

Vkay

Atlast I found a way with your solution, here

http://live.datatables.net/kuyayeto/9/edit with the solution given by @you2525