Export to Excel from ASP.NET using HTTP Handler

Export to Excel is one of the most common functionality which is required for data driven websites so the users of the website can download the data and can use it for their purposes like creating chart, graphs out of that data or performing various analysis on the data.

HTTP Handlers comes handy when you want to create custom end points for your website.

Using HTTP Handler for Export to Excel or any other format is very efficient and provides you greater control over the functionality that you would like to develop.

There are two ways you can create HTTP Handlers:

1)      ASP.Net provide template for Generic Handler (.ashx) that can be used to write handler code. Benefit of using this template is that ASP.NET register this on its own so once you have built your logic ASP.NET will take care of everything and you can call it from your application anytime.

2)      By writing your own custom class and implementing IHTTPHandler interface where output that you want to write should be written inside ProcessRequest method. In this case, you will have to register your custom handler in web.config file as well as with IIS. (We are not going to implement this step because Step 1 will serve our purpose)

To get started, we will add new Generic Handler in our website with name “ExportToExcelHandler.ashx” this will create .ashx file and provide us some basic code of Hello World.

Lets delete existing code inside ProcessRequest method. Once you have deleted that your code should look like this:

<%@ WebHandler Language="C#" Class="ExportToExcelHandler" %>

 

using System;

using System.Web;

 

public class ExportToExcelHandler : IHttpHandler {

   

    public void ProcessRequest (HttpContext context) {

     

    }

 

    public bool IsReusable {

        get {

            return false;

        }

    }

 

}

 

 

 

 

 

Now we will start putting our export to excel logic inside ProcessRequest method:

1)      We will start by adding following references:

 

using System.IO;

using System.Web.UI;

using System.Web.UI.WebControls;

 

2)      In order to export data to excel we will have to add “content-disposition” in the response header so browser can prompt user the download dialog box then set MIME type to “application/ms-excel” like shown below:

 

context.Response.AddHeader("content-disposition","attachment; filename=excelData.xls");

context.Response.ContentType = "application/ms-excel";

 

3)      Create HttpRequest and Response objects:

 

HttpRequest request = context.Request;

HttpResponse response = context.Response;

 

4)      Lets create a method which will return formatted string HTML table

 

string exportContent = ExportToExcel();

 

5)      Implementation of this method will create HTML Table control and add data rows and cells to the table. For demo purposes, I have used nested loop to create table row and table cell and added them to the table but you can fetch data from database and populate this table with the records from database. Following is the code for writing data to HTML Table:

private string ExportToExcel()

    {

       //String which will return content

        string _exportContent="";

       // Create StringWriter object to pass in HTML Writer

        using (StringWriter sb = new StringWriter())

        {

            using (HtmlTextWriter htmlWriter = new HtmlTextWriter(sb))

            {

                // Creating table for holding data

                Table table = new Table();

                table.GridLines = GridLines.Horizontal;

                table.BorderWidth = new Unit(1);

                for (int i = 0; i < 100; i++)

                {

                     TableRow row = new TableRow();

row.BackColor = (i%2==0)? System.Drawing.Color.BlanchedAlmond:

                     System.Drawing.Color.BurlyWood;

                      

                    for (int j = 0; j < 5; j++)

                      {

                         TableCell cell = new TableCell();

                         cell.Text="Row: "+i+ " :: Cell:"+j;

                         row.Cells.Add(cell);

                      }

                    table.Rows.Add(row); 

                }

                //Render Output of table content to provided HtmlWriter

                table.RenderControl(htmlWriter);

                _exportContent = sb.ToString();

            }

        }

return _exportContent;

             }

 

6)      At the end add this string to response object:

response.Write(exportContent);

7)      After following steps your code of .ashx file should look something like this:

<%@ WebHandler Language="C#" Class="ExportToExcelHandler" %>

 

using System;

using System.Web;

using System.IO;

using System.Web.UI;

using System.Web.UI.WebControls;

 

public class ExportToExcelHandler : IHttpHandler {

   

    public void ProcessRequest (HttpContext context) {

        context.Response.AddHeader("content-disposition","attachment; filename=excelData.xls");

        context.Response.ContentType = "application/ms-excel";

        HttpRequest request = context.Request;

        HttpResponse response = context.Response;

        string exportContent = ExportToExcel();

        response.Write(exportContent);

    }

 

    private string ExportToExcel()

    {

        string _exportContent="";

        using (StringWriter sb = new StringWriter())

        {

            using (HtmlTextWriter htmlWriter = new HtmlTextWriter(sb))

            {

                Table table = new Table();

                table.GridLines = GridLines.Horizontal;

                table.BorderWidth = new Unit(1);

                for (int i = 0; i < 100; i++)

                {

                   TableRow row = new TableRow();

                    row.BackColor = (i%2==0)? System.Drawing.Color.BlanchedAlmond:

                                               System.Drawing.Color.BurlyWood;

                      

                    for (int j = 0; j < 5; j++)

                                 {

                                    TableCell cell = new TableCell();

                           cell.Text="Row: "+i+ " :: Cell:"+j;

                           row.Cells.Add(cell);

                                 }

                    table.Rows.Add(row); 

                }

                table.RenderControl(htmlWriter);

                _exportContent = sb.ToString();

            }

        }

        return _exportContent;

       

       

       

    }

 

    public bool IsReusable {

        get {

            return false;

        }

    }

 

}

 

8)      That’s it, you can call this handler as follows in any .aspx file:

<asp:HyperLink runat="server" NavigateUrl="~/ExportToExcelHandler.ashx">Click Here to Export</asp:HyperLink>

 

Note: When you export data to Excel, sometimes excel remove leading zeros from the data. This can be fixed by adding style attribute to cell of the row:

cell.Attributes.Add("style", @"mso-number-format:\@;");

 

Your updated code inside ExportToExcel method should look like this now:

    private string ExportToExcel()

    {

        string _exportContent="";

        using (StringWriter sb = new StringWriter())

        {

            using (HtmlTextWriter htmlWriter = new HtmlTextWriter(sb))

            {

                Table table = new Table();

                table.GridLines = GridLines.Horizontal;

                table.BorderWidth = new Unit(1);

                for (int i = 0; i < 100; i++)

                {

                   TableRow row = new TableRow();

                    row.BackColor = (i%2==0)? System.Drawing.Color.BlanchedAlmond:

                                               System.Drawing.Color.BurlyWood;

                       

                    for (int j = 0; j < 5; j++)

                                 {

                                    TableCell cell = new TableCell();

                           cell.Text=i+""+j;

cell.Attributes.Add("style", @"mso-number-format:\@;");

 

                           row.Cells.Add(cell);

                                 }

                    table.Rows.Add(row); 

                }

                table.RenderControl(htmlWriter);

                _exportContent = sb.ToString();

            }

        }

        return _exportContent;

       

    }

 

Thank you!!!

Tags: ,

ASP.NET