Tuesday, October 30, 2012

Export HTML to excel using jQuery and asp.net

Here we will see how to export a HTML table content to excel using asp.net web form and C# using jQuery.

Let’s start with a small piece of code –
    <h2>
        Export to excel using jquery
    </h2>

    <a href="#" class="expToExcel">Export to excel</a>
    <div id="toReport">
    <table>
        <tr>
          <th>Name</th>
          <th>Age</th>
          <th>Email</th>
        </tr>
        <tr>
          <td>John</td>
          <td>44</td>
          <td>john@gmail.com</td>
        </tr>
        <tr>
          <td>Rambo</td>
          <td>33</td>
          <td>rambo@gmail.com</td>
        </tr>
        <tr>
          <td>It's hot</td>
          <td>33</td>
          <td>test@hotmail.com</td>
        </tr>
    </table>
    </div>
On click of "Export to excel" let do export the content to excel file using jQuery. We can do this in following steps-
  1. Get the HTML content.
  2. Encode the HTML content.
  3. Pass the HTML encoded content to an aspx page.
  4. Generate the excel file from code behind.
Step 1-
var data = $("#toReport").html();
data = escape(data);
Why we are escaping the HTML data and then passing to code behind. Answer is that we are going to pass the data to an aspx page using a dynamically created form. It is going to through "A potentially dangerous Request.Form value was detected from the client" error. That’s why we are escaping the HTML content.

Step 2-
$('body').prepend("<form method='post' action='exportPage.aspx' style='top:-3333333333px;' id='tempForm'><input type='hidden' name='data' value='" + data + "' ></form>");
$('#tempForm').submit();
$("tempForm").remove();
In this step we are adding an aspx page named exportPage.aspx. And creating a form tag on the fly, add the HTML data to a hidden field and submit the form using jQuery. And finally remove the added form tag.

Step 3 & 4-
        string data = Request.Form["data"];
        data = HttpUtility.UrlDecode(data);
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=report.xls");
        Response.Charset = "";
        Response.ContentType = "application/excel";
        HttpContext.Current.Response.Write( data );
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
In this step we are simply creating the excel file and flushing the result as excel. You may see a message saying corrupt excel file do you want to open the file. You can open the file not an issue. I am too lazy to fine out proper setting.

You can also download the code from here.

9 comments:

  1. i am using httphandler. how can i pass json values to my handler and generate pdf from there and call it in browser for download?

    ReplyDelete
    Replies
    1. Hi dis you find any thing on it ?/
      I am facing same issue

      Delete
    2. you can try changing content type and check

      Delete
  2. This code creates excel but we can't edit it. So how to make editable excel file.

    ReplyDelete
  3. Try using different type for the following-
    Response.AddHeader("content-disposition", "attachment;filename=report.xls");
    Response.Charset = "";
    Response.ContentType = "application/excel";

    ReplyDelete
  4. Thank you it's very important article. You saved my time :)

    ReplyDelete
  5. I am using MVC, I would be interested in an example doing the same in ASP.NET MVC, I am looking for an example in MVC which works in Chrome, Firefox and IE, so far I haven't found an example doing this. Anybody can provide a link or an example?

    ReplyDelete
    Replies
    1. you can try using FileResult with return File(filename, contentType,"Report.pdf");

      Delete
  6. Hi Anup, thank you for your effort :) At some point I will try to do this in ASP.NET MVC, Currently I don't know how to do it, so I am of course very interested in further info, links, and lovely code examples. So far in my search IE often seems to be a problem. It works in the other major browsers, Firefox, Chrome, but not in IE. I want to export as Excel, preferably as .xlsx, but .xls is also Ok.

    ReplyDelete