Excel Export create new sheet example

Excel Export create new sheet example

kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

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

Replies

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

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

  • VkayVkay Posts: 3Questions: 0Answers: 0

    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???

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

    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

  • VkayVkay Posts: 3Questions: 0Answers: 0

    Atlast I found a way with your solution, here

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

Sign In or Register to comment.