jQuery export table data into MS Excel

26. October 2012 Anil Kumar Asp.Net , jQuery Comments (0)

We can use window.open() to export table data into Excel or any required format. It is the easiest and simplest way of creating a file with data without server side trip. window.open() requires 2 input parameters for this-

  1. the required MIMEtype  and 
  2. element that contains our data table.

If you want to export your Gridview data, then place that Gridview inside a container (like DIV etc.) and supply the DIV to winodw.open(). Also, be noted that window.open() has its scope and limitations in terms of browser vendors and customizing output file name.

Recently, I got a thread on Microsoft's asp.net forum regarding exporting GridView/table/div data into MS Excel at client side. You can refer that link - http://forums.asp.net/t/1854204.aspx/1?Export+to+Excel 

So, I decided to write this post in context of helping other needy toubleshooters. 

This is the second version you are reading as first was to help a forum user only. You know GridView gets rendered into "table" format. So, if you are using asp.net and looking for your GridView then keep your GridView inside a div element to make it easy.

If you are just looking for code snippet that do magic job for our Excel-export then it is single line code as below -

 

window.open('data:application/vnd.ms-excel,' +  $('div[id$=divTableDataHolder]').html());

Understanding how it works: 
To export our table data, we are going to use window.open(). I know most of you would be definitely familiar with this version of the window.open()-

window.open(URL, name , specs , replace )

But here you will see its another version : 

window.open(MIMEtype, dataContainerItem)
 
 
window.open (MIMEtype   ,  replace )      //replace=> dataContainerItem 
                      |                          |________  It takes the element from window opener
 
                      |___________   Default MIME type is "text/html"



We have to just specify our required MIMEtype and it does the job for us! So for converting into MS Excel, we will specify data:application/vnd.ms-excel as MIME type. 

Replace is the item taken from opener-window whose html we are going to send into Excel sheet. This container element shouldn't have special characters. If it contains then we have to encode those characters using JavaScript's encodeURIComponent(). For more details on encodeURIComponent() refer - http://www.w3schools.com/jsref/jsref_encodeuricomponent.asp 

So we will create a simple html table inside a div as -

 

<button id="myButtonControlID">Export Table data into Excel</button>
<div id="divTableDataHolder">
<table>
    <tr><th>ColumnOne </th><th>ColumnTwo</th></tr>
<tr>
<td>row1ColValue1</td><td>row1ColValue2</td>
</tr>
<tr>
<td>row2ColValue1</td><td>row2ColValue2</td>
</tr>
</table>
</div>

Now, we will write our magic jQuery/JavaScript code to get done our job. In this snippet I am using encodeURIComponent() to handle special characters. You can use without it also as I have mentioned in top of this post.

 

$("[id$=myButtonControlID]").click(function(e) {
    window.open('data:application/vnd.ms-excel,' + encodeURIComponent( $('div[id$=divTableDataHolder]').html()));
    e.preventDefault();
});​

I am giving you the ready-reference snippet that contains a single line of code window.open() to export table data into MS excel  :)

You can get more details on jQuery website: http://forum.jquery.com/topic/anyway-to-export-html-table-to-excel

 

Blog Extended On: 21-July-2013

window.open() has its scope and limitations(ex IE not working & filename) that has been well explained in this post: Export to CSV using jQuery and html

In terms of huge data export, I have already tested with 2500 rows and it works fine and I am sure it will work for more than this figure also. You can see it live : http://jsfiddle.net/AnilAwadh/wJyWm/

 

Tags: , , ,