Exporting Data Table or Data Grid to Excel sheet in ASP.NET

After reading this post you would be able to do the following tasks.

1. Different ways of exporting Data to Excel Sheet.
2. Exporting Excel sheet in http as well as secured mode (https with SSL).
3. Format the data in Excel sheet like Dates etc.

Lets start with first task.

1. Different ways of exporting Data to Excel sheet

• Export the Data to Excel sheet from Data Grid

This is one way of exporting data to Excel sheet, This is the easiest way of doing. lets write the code for this in c#.net.

	HttpResponse response = HttpContext.Current.Response;

	//Clear your your response.
	response.Clear();
	response.Charset = "";

	this.EnableViewState = false;

	//Add Content-Disposition,Property Attachment. This will prompt the user to open/save always.
	Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);

	//You content type, for Excel it is vnd.ms-excel.
	response.ContentType = "application/vnd.ms-excel";

	System.IO.StringWriter sw = new System.IO.StringWriter();
	HtmlTextWriter htw = new HtmlTextWriter(sw);

	//Your data grid has RenderControl property, whcich will render your grid to html text writer.
	yourdatagrid.RenderControl(htw);
	Response.Write(sw.ToString());

	Response.End();

Lets read the main part of above code,

Before adding content to Response object we should make sure that Response object is empty so we have cleared the response object using response.clear().

Then adding header to our response object, We have added ‘Content-disposition’ to the header object which holds high importance of our task. This attribute tells the browse whether it should open the exported excel sheet in the same window or should prompt the user to open/save/cancel the operation. If you are interested in knowing more about Content – disposition, go to this thread.

Data grid control have RenderControl method which actually takes the html code of our data grid and will write it to HtmlTextWriter.  Then write our Table to  Response object. so now our response object have the data in html table format.  But here we want it to be in the excel sheet, so we are declaring the content Type of our response object as excel sheet. if you want to export to html change the content type to html.  To know contentType for different file types, read this page.

2. Export the data using Data Table

Exporting Data Table to excel sheet can be done in two ways,  First one is create a new instance of a data grid and the bind the data table to data grid and the use the Method one ‘Export the Data to Excel sheet using data Table’.

            DataGrid dg = new DataGrid();
            dg.DataSource = DataTable;

This is straight forward way of exporting data table to excel sheet.  I know one more way of exporting data table to Excel sheet using more options of htmlTextWriter. Lets write the code for that one.

	HttpResponse response = HttpContext.Current.Response;

            // First let's clean up the response.object
            response.Clear();
            response.Charset = "";
            this.EnableViewState = false;

            // set the response MIME type for excel
            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

            // create a string writer
            using (StringWriter sw = new StringWriter())
            {
                using (System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw))
                {
                    htw.RenderBeginTag(HtmlTextWriterTag.Table); //Start Html Table
                    htw.RenderBeginTag(HtmlTextWriterTag.Tr);//Writing Report Column Headings
                    foreach (DataColumn dc in dt.Columns)
                    {
                        htw.RenderBeginTag(HtmlTextWriterTag.Th);
                        htw.Write(dc.ColumnName);
                        htw.RenderEndTag();
                    }
                    htw.RenderEndTag();
                    foreach (DataRow dr in dt.Rows)
                    {
                        htw.RenderBeginTag(HtmlTextWriterTag.Tr);//Writing Data 

                        foreach (DataColumn dc in dt.Columns)
                        {
                            htw.RenderBeginTag(HtmlTextWriterTag.Td);
                            htw.Write(dr[dc]);
                            htw.RenderEndTag();
                        }

                        htw.RenderEndTag();
                    }
                    response.Write(sw.ToString());
                    response.End();
                }
            }

We are not doing any thing new there, it’s the similar way to the first method , but here we are not taking help of data grid just using our HtmlTextWriter.  We will read the main part of the above code now.

First we lines i had already explained above, in the later part i am just creating an html table using HtmlTextWriter and inserting data from our data table in HtmlTextWriter table. once our table is finished we are writing that string to Response object which will take care of exporting the format mentioned using content – type.

I hope i am clear with the above two methods.  There are some others ways of doing it, but i am not going explain them as of now.

Now we will get into second task ‘Exporting Excel sheet in http as well as secured mode (https with SSL). ‘

Exporting Excel sheet https (secured)

The above explained methods should work with HTTP & HTTPS , but we have observed that some users could not open/save exported excel sheet in HTTPS environment.  The reasons for this problem would be many, i will list out some of those here , if you face this problem please check this list.

1. The Do not save encrypted pages to disk check box is selected in Internet Explorer. If this options is selected make sure that it is unchecked. you can find this option at

Internet Explorer –> options –>Advanced –>  under Security

2. The Automatic prompting for file downloads option is set to Enable, if it set to disable then when you click Export button you don’t see anything. if this is the scenario make sure that that option is set to Enable.. You can find this option at

Internet Explorer –> Options  -> security – > Custom Level – > Downloads

3. Make sure that you server is not sending the following headers.

• "Cache-Control: No Store" header.
•  "Cache-Control: No Cache" header.

if it sending the above two headers then Clear your Response headers before exporting the data.

response.ClearHeaders();

Now I will finish this post with our final task ‘Formatting the data in the exported Excel Sheet’ .

Format the data in Exported Excel sheet

In some scenario’s we have to format the data in exported excel sheet, may be you want column names to bold, bigger font with different color or if you have Date column in your data table you just want to have short date in the exported excel or you just don’t want time in seconds etc. so how to achieve all these when we are exporting data from Data Grid or Data Table?

One way of applying you styles to the data in excel is apply data to your data grid and then export the using renderControl write that html table to HtmlTextWriter. This will ensure that your styles are get reflected in the Exported excel sheet.

Other way is while building our table using HtmlTextWriter we can apply our styles. In the second example i mentioned above, i am rewriting the code here by applying few styles.

	using (StringWriter sw = new StringWriter())
            {
                using (System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw))
                {
                    htw.AddAttribute(HtmlTextWriterAttribute.Border, "1");
                    htw.RenderBeginTag(HtmlTextWriterTag.Table); //Start Html Table 

                    htw.RenderBeginTag(HtmlTextWriterTag.Tr); //Inserting Title of the Report
                    htw.AddAttribute(HtmlTextWriterAttribute.Colspan, dt.Columns.Count.ToString());
                    htw.AddAttribute(HtmlTextWriterAttribute.Align,"Center");
htw.AddAttribute(HtmlTextWriterAttribute.Style,"font-family:Arial;font-size:12pt;font-weight:bold;");
                    htw.RenderBeginTag(HtmlTextWriterTag.Td);
                    htw.Write(reportHeading);
                    htw.RenderEndTag();
                    htw.RenderEndTag();

                    htw.RenderBeginTag(HtmlTextWriterTag.Tr);//Writing Report Column Headings
                    foreach (DataColumn dc in dt.Columns)
                    {
                        htw.AddAttribute(HtmlTextWriterAttribute.Style, "font-family:Arial;font-size:8pt;");
                        htw.RenderBeginTag(HtmlTextWriterTag.Th);
                        htw.Write(dc.ColumnName);
                        htw.RenderEndTag();
                    }
                    htw.RenderEndTag();
                    foreach (DataRow dr in dt.Rows)
                    {
                        htw.RenderBeginTag(HtmlTextWriterTag.Tr);//Writing Data 

                        foreach (DataColumn dc in dt.Columns)
                        {
                              	htw.AddAttribute(HtmlTextWriterAttribute.Style, @"font-family:Arial;font-size:7pt;mso-number-format:\@;");
                            htw.RenderBeginTag(HtmlTextWriterTag.Td);
                            htw.Write(dr[dc]);
                            htw.RenderEndTag();
                        }

                        htw.RenderEndTag();
                    }
                    response.Write(sw.ToString());
                    response.End();
                }
            }

In the above code i have applied border of size ‘1’ to my table and then i have created a Title to my Excel sheet which will be my first row of excel sheet then headings with bigger fonts & bolded ( i have applied styles to TD (table date), it’s just like applying styles your html table but you are doing through HtmlTextWriter).

if you see this line of code

htw.AddAttribute(HtmlTextWriterAttribute.Style, @"font-family:Arial;font-size:7pt;mso-number-format:\@;");

i have applied style to my data in exported excel sheet, mso-number-format:\@; make sure that excel don’t interpret any data format it will take everything as basic text ( it can be date or it can be number everything will be considered as text).

By using the above formats you can control how want to your data to be in the exported excel sheet.