Click here to Skip to main content
15,867,310 members
Articles / Web Development / ASP.NET
Article

Export large data from Gridview and Datareader to an Excel file using C#

Rate me:
Please Sign up or sign in to vote.
4.27/5 (26 votes)
21 Jun 20075 min read 258.5K   3.9K   115   33
An article on exporting large data to Excel files

Screenshot - ExportLargeDataInExcel.jpg

Introduction

A good way to display data is to show it in a grid view. However, it becomes difficult to manipulate and filter large amounts of data in this way. Exporting data to an Excel file is a great solution for handling large amounts of data because Excel has many features -- such as sorting, searching and filtering -- that do not require you to write a single line of code. In this example, I will show:

  1. How to pull data from a database and display it in the grid.
  2. How to export data from the grid to the Excel file.
  3. How to export data from a data reader to the Excel file.
  4. How to handle large amounts of data and combat different types of errors.

Using the code

This sample uses ASP.NET 2.0, C# and SQL Server 2005. I am using a simple form of the database table to avoid unnecessary overhead. Let us assume that we have a database named UniversityManager and that it has a table named Student. The structure of the table is as follows:

Column NameData Type
Roll varchar(10)
Name varchar(50)
Marks int

I am using the ASP.NET SqlDataSource control to pull data from the database. SqlDataSource can be used as a data source that can fetch data from databases. It can be bound to an ASP.NET control. For showing this data to a grid, I am using the ASP.NET GridView control. The GridView control is the successor to the DataGrid control. Like the DataGrid control, the GridView control was designed to display data in an HTML table. When bound to a data source, the DataGrid and GridView controls each display a row from a DataSource as a row in an output table. The code at the ASP side would look like this:

HTML
<asp:GridView ID="grdStudentMarks" runat="server" 
    DataSourceID="dsStudentMarks">
    <EmptyDataTemplate>
        No Data Found
    </EmptyDataTemplate>
    <RowStyle CssClass="ClsOddRow" />
    <AlternatingRowStyle CssClass="ClsEvenRow" />
    <HeaderStyle CssClass="ClsHeaderRow" /> 
</asp:GridView>

<asp:SqlDataSource ID="dsStudentMarks" runat="server" ConnectionString=
  "Data Source=.;Initial Catalog=UniversityManager;Integrated Security=True;"
    SelectCommand="
    (
        SELECT *FROM STUDENT 
    ) 
    "> 
</asp:SqlDataSource>

<asp:Button ID="btnExportFromDatagrid" runat="server" 
    Text="Export From Grid" OnClick="btnExportFromDatagrid_Click" />

<asp:Button ID="btnExportFromDataset" runat="server" 
    Text="Export From Data set" />

When the ASP.NET page is rendered, grdStudentMarks will be populated by the data from the Student table of the UniversityManager database. Hence, Windows authentication is used. Besides this, I have taken two buttons named btnExportFromDatagrid and btnExportFromDataset. Now we have a grid full of data from the database. Our next objective is to export this data from the data grid to Excel. We have written this code at the Click event of btnExportFromDataGrid.

C#
protected void btnExportFromDatagrid_Click(object sender, EventArgs e)
    {
        ExportGridToExcel(grdStudentMarks, "StudentMarks.xls");   
    }
    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", 
            string.Format("attachment;filename={0}.xls", fileName));
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";

        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        grdGridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

When the button is clicked, we invoke the function ExportGridToExcel with the parameters as Gridview and the name of the file that we wish to save. A header is added to the HttpResponse stream. It will force the user to download the file instead of displaying it embedded in the browser. Then with Response.ContentType we are setting an HTTP MIME Type of the output stream as application/vnd.xls. Then the RenderControl method of the GridView class is used, which outputs server control content to the provided HtmlTextWriter, htmlWrite. Finally, this is written to the Response stream.

The job is supposed to be done at this stage! However, were you to load the page and click the button, you would probably see the following error:

Control 'grdStudentMarks' of type 'GridView' must be placed inside a form tag with runat=server.

To resolve this error, you should override the VerifyRenderingInServerForm method. Just write the following:

C#
public override void VerifyRenderingInServerForm(Control control)
    {

    }

That's it! The data of the grid view will be exported to the Excel file, which should be saved to the desktop. However, there are some problems with this solution. First, if you use paging in your grid view, then only the data of an individual page will be exported instead of the data of whole grid. That means only those data which are rendered within the page will be exported. Second, if the data of the grid view is huge, you will probably get the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This is a System.Data.SqlClient.SqlException. This exception occurs when the timeout period elapses prior to completion of the operation. So to solve this type of problem, we would use the following solution.

Timeout solution

I am going to use the SQLCommand class because by using so, we can set a timeout property. The CommandTimeout property sets the wait time before terminating any execute command. The data is taken in SqlDataReader and each row of the data reader is written in the Response stream. One thing to mention is that the value of each cell -- i.e. a particular column of a row -- is followed by a comma delimiter so that the CSV file can format it. The code is as follows:

C#
protected void btnExportFromDataset_Click(object sender, EventArgs e)
{
    ExportToExcel(dsStudentMarks, "StudentMarks");
}
 
public void ExportToExcel(SqlDataSource dataSrc, string fileName)
{
    //Add Response header 
    Response.Clear();
    Response.AddHeader("content-disposition", 
        string.Format("attachment;filename={0}.csv", fileName));
    Response.Charset = "";
    Response.ContentType = "application/vnd.xls";
    //GET Data From Database                
    SqlConnection cn = new SqlConnection(dataSrc.ConnectionString);
    string query = 
        dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");
        
    SqlCommand cmd = new SqlCommand(query, cn);
        
    cmd.CommandTimeout = 999999 ;
    cmd.CommandType    = CommandType.Text;
    try
    {
        cn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        StringBuilder sb = new StringBuilder();            
        //Add Header          
        for (int count = 0; count < dr.FieldCount; count++)
        {
            if (dr.GetName(count) != null)
                sb.Append(dr.GetName(count));
            if (count < dr.FieldCount - 1)
            {
                sb.Append(",");
            }
        }
        Response.Write(sb.ToString() + "\n");
        Response.Flush();            
        //Append Data
        while (dr.Read())
        {
            sb = new StringBuilder();
               
            for (int col = 0; col < dr.FieldCount - 1; col++)
            {
                if (!dr.IsDBNull(col))
                    sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
                    sb.Append(",");
            }
            if (!dr.IsDBNull(dr.FieldCount - 1))
                sb.Append(dr.GetValue(
                dr.FieldCount - 1).ToString().Replace(",", " "));
            Response.Write(sb.ToString() + "\n");
            Response.Flush();
        }
        dr.Dispose();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        cmd.Connection.Close();
        cn.Close();
    }
    Response.End();
}

This solves the problem of downloading large amounts of data.

The problem of the SQLClient timeout exception is now solved, but there is still a possibility of getting an HtttpRequest timeout exception. To solve this problem, put the following lines inside <system.web> tag of the web.config file:

ASP.NET
<httpRuntime maxRequestLength="209715" executionTimeout="3600" />

That's it!

Points of interest

So, we have learned how to pull data from a database and show it in the grid view control. We have learned how to export data from grid view to an Excel file, how to export huge amounts of data from the data reader and how to export that data to an Excel file. I hope that this example helps you. Feel free to give me any suggestions regarding this article. Happy coding!

History

This demo was first uploaded on June 21st, 2007.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior) Nascenia Ltd. (www.nascenia.com)
Bangladesh Bangladesh
Fuad Bin Omar is co-founder and COO of Nascenia Ltd . () Prior to joining in Nascenia he worked as a "Senior Software Engineer" at the offshore development office of Code71,Inc
Fuad have more than five years of experience in developing business applications using .NET and Ruby on Rails technologies.


He is an MBA in the Department of Finance at University of Dhaka, Bangladesh .
He is a Computer Science and Engineering graduate from Khulna University of Engineering & Technology (KUET), Bangladesh.

His articles published in the codeproject:

Multi-color DropDownList using C#

Export large data from Gridview and Datareader to an Excel file using C#

Mailto Fuad at fuadcse@yahoo.com

Comments and Discussions

 
GeneralMy Vote of 5 Pin
Priyanka Sundaraj31-Mar-14 19:46
Priyanka Sundaraj31-Mar-14 19:46 
Questionnot able to open in MS Excel Pin
nikunjmochi18-Oct-11 2:12
nikunjmochi18-Oct-11 2:12 
QuestionUpdate Panel Problem Pin
someonesays3-Aug-11 22:59
someonesays3-Aug-11 22:59 
GeneralMy vote of 5 Pin
chandan Panda2-Nov-10 3:52
chandan Panda2-Nov-10 3:52 
QuestionData being truncated to the new row ? Pin
AsianRogueOne9-Aug-10 5:38
AsianRogueOne9-Aug-10 5:38 
GeneralMy vote of 1 Pin
UL UL ALBAB28-Apr-10 21:18
UL UL ALBAB28-Apr-10 21:18 
GeneralGreat Article Bro. Pin
nakash20505-Jul-09 4:41
nakash20505-Jul-09 4:41 
GeneralMultiple Page GridView Pin
Frosty335-Jan-09 9:49
Frosty335-Jan-09 9:49 
GeneralRe: Multiple Page GridView Pin
gargvijay18-Mar-09 20:45
gargvijay18-Mar-09 20:45 
GeneralRe: Multiple Page GridView Pin
Dan Madden24-Nov-09 2:11
Dan Madden24-Nov-09 2:11 
GeneralRe: Multiple Page GridView Pin
Dan Madden24-Nov-09 2:12
Dan Madden24-Nov-09 2:12 
Generalimport an Excel file Pin
Laxmi R5-Jan-09 0:37
Laxmi R5-Jan-09 0:37 
QuestionHow to import an excel file which is export from gridview to excel Pin
Laxmi R5-Jan-09 0:34
Laxmi R5-Jan-09 0:34 
AnswerRe: How to import an excel file which is export from gridview to excel Pin
CikaPero23-Feb-10 22:39
CikaPero23-Feb-10 22:39 
GeneralExport Gridview in different tabs(worksheet) in excel file using c# Pin
Umesh873818-Nov-08 19:05
Umesh873818-Nov-08 19:05 
Generalplz help me..i want to export data from datagridview to excel sheet in vb.net windows application Pin
dilipmca0420-Oct-08 18:43
dilipmca0420-Oct-08 18:43 
QuestionHow to Import CSV Pin
Syed Adeel Zaidi28-Aug-08 11:21
Syed Adeel Zaidi28-Aug-08 11:21 
QuestionStoring file in XL format Pin
rajaron10-Jul-08 3:00
rajaron10-Jul-08 3:00 
AnswerRe: Storing file in XL format Pin
FilipKrnjic5-Jul-09 23:23
FilipKrnjic5-Jul-09 23:23 
GeneralVery Large Results killing AppPool. Pin
StephenHill1126-Jun-08 11:05
StephenHill1126-Jun-08 11:05 
Hi,
Great Article, but I have some very large ResultSets that my users are trying to create and download, and if more than one is launched at the same time, it fills up the App Pool and brings IIS down ...

Is there a way to launch this process in it's own thread, so it doesn't fill up the App Pool and will download successfully. Or at least, if it fails, will not fill up the App Pool ???

Thanks,

Steve Hill
QuestionGreat Article! Pin
count_rugen18-Jun-08 11:13
count_rugen18-Jun-08 11:13 
GeneralUse httpRunTime Element in Web.config File instead Pin
Bhaskar Shetty12-Aug-07 23:50
Bhaskar Shetty12-Aug-07 23:50 
GeneralError Pin
shekhar kumar das11-Jul-07 18:40
shekhar kumar das11-Jul-07 18:40 
GeneralRe: Error Pin
bonhomie30-Aug-07 13:46
bonhomie30-Aug-07 13:46 
GeneralThanks Pin
2 Rocks5-Jul-07 4:32
2 Rocks5-Jul-07 4:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.