How can I get the export of 2 different datatables to be exported into a single excel file

minhal · May 2019

I have 2 different datatables on my page and they both work fine but they both export the data on different files. I want to add both those files into different worksheets in a single excel file. Is there a way to do it?
Thanks!

colin · May 2019

Hi @minhal ,

Not as far as I know, as each of the exports will be independent of each other.

Cheers,

Colin

minhal · May 2019

Hey @colin ,

Thanks for your reply. Is there a way that I can export both the files into a new datatable and export it from that datatable to get all the data in a single excel file but in 2 different worksheets.

colin · May 2019

Hi @minhal ,

I don't know an easy way, but there are a few threads in this forum discussing that, see this thread here for example,

Cheers,

Colin

kthorngren · May 2019

This is not a builtin feature of Datatables. It will require custom code. Unfortunately it is complicated enough that simple example would not be easy to provide.

Creating multiple worksheets with the Excel export is tricky. There is an example in this thread of how to do this:
https://datatables.net/forums/discussion/49457

Checkout this thread for options to export data from other sources:
https://datatables.net/forums/discussion/comment/133433/#Comment_133433

You can use rows().data() to get the data from the other table. My suggestion would be to start with the fiddle provided in the first thread, add a second table, fetch the data from that table to place in the second worksheet. This way if you have difficulties we can take a look.

Kevin

minhal · May 2019

Hi @kthorngren,

Thanks for your reply. By using the above link I was able to create another worksheet in my excel file. But I am still unable to export both the excel files into a single excel file.

kthorngren · May 2019

Like I said we would need to see an example of what you are doing. Please post a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

minhal · May 2019

@kthorngren ,

I am sorry but I am not sure how to provide a test case here, as my data is pulled from a sql database and when I am trying to run my code on jsfiddle the data can not show up in the results

minhal · May 2019

https://jsfiddle.net/ng3mh25c/

This is what I can get. But While exporting the excel file I am getting the data and the added worksheet to it but it is empty.
I want to add the file exported from my other data table into this worksheet.

minhal · May 2019

Hi @colin,

I have tried some of the ways discussed in that thread but not been successful using it. I am getting some errors.

kthorngren · May 2019

I am getting some errors.

In your example you are getting this error:
Uncaught ReferenceError: $ is not defined at window.onload ((index):33)

You haven't loaded the jQuery, Datatables and Buttons JS/CSS.

I am sorry but I am not sure how to provide a test case here, as my data is pulled from a sql database

Your specific data is not needed for this question. You can simulate the data. You can use one of these templates as a starter:
https://datatables.net/manual/tech-notes/9

Make sure you include all the appropriate JS and CSS files by either using the Add Library option of the JS BIN page or generating a CDN using the Download Builder.

Kevin

minhal · May 2019

@kthorngren

I have tried again. here is the link for my code its now working as its not pulling the data but this code generates another empty worksheet when you export the data.

http://live.datatables.net/cexedile/1/edit

minhal · June 2019

Hi @kthorngren ,

I was able to create a new worksheet within my excel export. Here is an example for that. http://live.datatables.net/dalibuya/1/edit

But I am looking to export 2 excel files in a single file like in different worksheets in a single file. Like in the below example I have 2 different datatables and I am looking to create a custom excel export where both the datatables are exported in a same file. http://live.datatables.net/lugoyado/1/edit

I hope you can get what I am trying to say.

Thanks

kthorngren · June 2019

Good job. It looks like what you would need to do is use rows().data() to get the data from the other table then var newSheet is the XML that is needed for the new sheet. This XML will be built using the data from the other table. The XML format is explained here:
http://officeopenxml.com/anatomyofOOXML-xlsx.php

You would need to loop through each row of data to build each row of XML output for the second sheet.

Kevin

minhal · June 2019

@kthorngren ,

Can you provide a example from my example?

http://live.datatables.net/wusagaqi/1/edit

Thanks

minhal · June 2019

@kthorngren ,

I have tried the way of getting the data from the other table using rows().data() but after clicking the export to excel button it keeps on loading.

Can you please have a look at it. I really appreciate that.

http://live.datatables.net/sijuyosa/1/edit

Thanks,

kthorngren · June 2019

You are getting an error. Take a look at the browser's console and you will see:

Uncaught ReferenceError: table is not defined

The variable table is not in scope in the customize function for this statement:

                        var data = table
                        .rows()
                        .data();

Try this instead:

                        var data = $('#example').DataTable()
                        .rows()
                        .data();

You may want to look at using rows().every(). Look at the examples in the doc. It will allow you to easily iterate over each row and append to a string the XML you want to build.

Kevin

minhal · June 2019

@kthorngren ,

I tried it with rows.every() and the error is been resolved but it is not getting the data from the other table.

Here is the example
http://live.datatables.net/qubemopi/1/edit

Thanks

kthorngren · June 2019

Thats a good start. See if this gets you going:
http://live.datatables.net/qubemopi/2/edit

It only does one column. Will leave it to you to populate the other columns :-)

Kevin

minhal · June 2019

Hi @kthorngren ,

Thank you so much for your help. I was able to get everything done perfectly because of you. I really appreciate that.

Here is the example:
http://live.datatables.net/gulogela/1/edit

Thanks

KeiSenpai · April 2021

Anyway to view these examples that was used? I tried and they all come up not found.

kthorngren · April 2021

I just tried the last two and they work:
http://live.datatables.net/qubemopi/2/edit
http://live.datatables.net/gulogela/1/edit

Maybe a firewall issue or something that is causing them to not open for you. Try incognito mode in case something in the browser is causing the issue.

Kevin

How can I get the export of 2 different datatables to be exported into a single excel file

How can I get the export of 2 different datatables to be exported into a single excel file

Answers

Howdy, Stranger!

Categories

DataTables

How can I get the export of 2 different datatables to be exported into a single excel file

How can I get the export of 2 different datatables to be exported into a single excel file

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables