How to export the jquery datatable data to excel ?

Question

1.00/5 (1 vote)

See more:

Hi
I want to export my datatable data to excel or pdf . At any point when i click on my requirement means it should work so .

I goggled it but i was unable to find good articles which supposed to explain things in better prospective

CODE EDIT :

JavaScript

<script  type="text/javascript">

    $(document).ready(function () {

 
        $("#btnExport").click(function(e) {
            window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('#myDataTable').html()));
            e.preventDefault();
        });

        $.datepicker.regional[""].dateFormat = 'dd/mm/yy';
        $.datepicker.setDefaults($.datepicker.regional['']);

        var tab=  $('#myDataTable').dataTable({
           
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "Company/DataProvider",
            "bJQueryUI": true,
            "aoColumns": [
                         {
                             "sName": "ID" 
                         },
                         { "sName": "COMPANY_NAME" },
                         { "sName": "ADDRESS" },
                         { "sName": "TOWN" }
            ]

        });
       

        tab.makeEditable({
            "aoColumns": [
                null,
                null,
                null,
                {
                indicator: 'Saving...',
                tooltip: 'Click to select town',
                loadtext: 'loading...',
                type: 'select',
                onblur: 'submit',
                loadurl: 'AjaxDataProvider1'
            
            }]
        });

    });

        </script>

}

<table id="myDataTable" class="display">
                    <thead>

                         <tr>
                            <th>ID</th>
                            <th>Company name</th>
                            <th>Address</th>
                           <th>Town</th>
                        </tr>
    
                    </thead>
                        
                    <tbody> 
                   </tbody>

                </table>

    <input type="button" id="btnExport" value="Export to Excel" />

Well i used one of the link and tried like this I dont why same way i tried in other system its showing me some columns in excel but now its simply throwing me error like:
CHROME : Showing just table headings thats it , columns not getting loaded
IE 10 : JavaScript critical error at line 47, column 12 in http://localhost:63553/Company/Index
SCRIPT1014: Invalid character

Thing is when i removed the piece of code i done for exporting to excel the data to table getting loaded successfully .

Hoping for better suggestions

Regards

Posted 13-Apr-14 9:22am

sunil gutta

Updated 3-Jun-18 19:21pm

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-04-13T10:06:00

Solution 1

Please see this product: http://excelbuilderjs.com[^].

If you do Web search, you will find advice to use ActiveX through the browser. Avoid it by all means. Not only it is not supported by all browsers, it is also utterly unsafe. Some security-savvy users won't come back to your site if they see that you are using such dirty approaches.

Alternatively, you should rather do the export on the server side. As you mentioned MVC, I can guess you are using .NET. If this is so, you can use Open XML SDK. Please see my past answer which references my other answers:
How to add microsoft excel 15.0 object library from Add Reference in MS Visual Studio 2010[^].

This is another .NET-based alternative: http://npoi.codeplex.com[^].

—SA

Posted 13-Apr-14 10:06am

Sergey Alexandrovich Kryukov

Comments

Manas Bhardwaj 13-Apr-14 16:19pm

+5!

See my answer. Some of them are quite simple and impressive.

Sergey Alexandrovich Kryukov 13-Apr-14 16:29pm

Thank you, Manas.
—SA

sunil gutta 14-Apr-14 7:22am

Hey i heard table tools will working fine which provides to export to different sources excel , pdf etc . but i am unable to understand things clearly . any opinion mates

Sergey Alexandrovich Kryukov 14-Apr-14 10:43am

Every activity assume some required prerequisites. Asking questions, too. It is assumed that you are able to understand thing clearly, and, in case of some unclear items, are able to ask clear questions on them. And so on...
—SA

sunil gutta 14-Apr-14 12:43pm

ok mate i will . and sorry for inconvenience provided and i will look into the links you given . tried one of those links , static html table content wont help me much but i tried that option on my jquery datatable i am getting just columns but no records .

Sergey Alexandrovich Kryukov 14-Apr-14 12:54pm

It's hard to see what is screwed up without seeing your code.
—SA

sunil gutta 14-Apr-14 15:18pm

once please look into the code and issues which i updated into my post Thanks friend for your time

Sergey Alexandrovich Kryukov 14-Apr-14 16:01pm

What's the problem with this difference code?
—SA

sunil gutta 15-Apr-14 2:19am

Well i dont know excatly but my code works fine when i remove the lines of code inserted for exporting to excel . i guess there may be a problem ? but i donno where ? can you please quickly take a look . I assure you the code is fine but exporting is not happening .

Sergey Alexandrovich Kryukov 15-Apr-14 10:58am

Can you use the debugger? It will show you everything.
—SA

Guruprasad.K.Basavaraju 15-Apr-14 16:46pm

These are really good SA.

Sergey Alexandrovich Kryukov 15-Apr-14 17:01pm

Thanks you, Guruprasad.
—SA

Manas Bhardwaj · Accepted Answer · 2014-04-13T10:18:00

Solution 2

In addition to what SA said, you might want to look at these example plugins:

http://www.jquerybyexample.net/2012/10/export-table-data-to-excel-using-jquery.html[^]

http://jsfiddle.net/lesson8/wVejP/[^]

http://wsnippets.com/export-html-table-data-excel-sheet-using-jquery/[^]

Posted 13-Apr-14 10:18am

Manas Bhardwaj

Comments

Sergey Alexandrovich Kryukov 13-Apr-14 16:29pm

Sure, 5ed.
—SA

Guruprasad.K.Basavaraju · Accepted Answer · 2014-04-15T10:21:00

I couldn't look into your code but the below should work.. replace #btnGuru with your button name.

JavaScript

$("#btnGuru").click(function () {
    tableToExcel('myDataTable', 'W3C Example Table');
});


var tableToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,'
      , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
      , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
      , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    return function (table, name) {
        if (!table.nodeType) table = document.getElementById(table)
        var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
        window.location.href = uri + base64(format(template, ctx))
    }
})()