Click here to Skip to main content
Click here to Skip to main content

Export large data from a GridView and DataReader to an Excel file using C#

, 20 Jun 2007
Rate this:
Please Sign up or sign in to vote.
An article on exporting large data to excel file

Introduction

To show data in a GridView is a good way of displaying data, but it becomes difficult to manipulate and filter large amounts of data in a GridView. Exporting data to an Excel file is a great solution to handle large amounts of data because Excel gives many features like sorting, searching, filtering, etc., without writing a single line of code.

In this example, I will show:

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

Using the Code

This sample is uses ASP.NET 2.0, C#, and SQL Server 2005.

I am using a simple form of database table to avoid unnecessary overheads. Let us assume that we have a database named "UniversityManager" and it has a table named "Student". The structure of the table is as follows:

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

I am using an ASP.NET SqlDataSource control to pull the data from the database. SqlDataSource can be used as a data source which can fetch data from the database, and it can be bound to an ASP.NET control. For showing this data in 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.

<asp:GridView ID="grdStudentMarks" 
      Font-Names="verdana" runat="server" 
      DataSourceID="dsStudentMarks">
<EmptyDataTemplate>
No Data Found
</EmptyDataTemplate>
        
<RowStyle BackColor="white" />
<AlternatingRowStyle BackColor="beige" />
<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>
<headerstyle cssclass="ClsHeaderRow">
<asp:Button ID="btnExportFromDatagrid" runat="server" 
   Text="Export From Grid" OnClick="btnExportFromDatagrid_Click" />
<asp:Button ID="btnExportFromDataset"  runat="server" 
  Text="Export From Data Reader" OnClick="btnExportFromDataset_Click" />
</headerstyle>
<asp:sqldatasource selectcommand="  (
                SELECT *FROM STUDENT                                     
                )                 
                " connectionstring="Data Source=.;
                Initial Catalog=UniversityManager;Integrated Security=True;" 
         runat="server" id="dsStudentMarks">
</asp:sqldatasource>    
<asp:button text="Export From Data set" runat="server" id="btnExportFromDataset">
</asp:button>

When the ASP.NET page is rendered, "grdStudentMarks" would be populated with data from the "student" table of the "UniversityManager" database. Hence Windows authentication is used. Besides this, I have 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 DataGrid to Excel. We have written this code in the Click event of "btnExportFromDataGrid".

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 a function ExportGridToExcel with Gridview as a parameter 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 (in IE). Then, with Response.ContentType, we set the HTTP MIME Type of the output stream to "application/vnd.xls". Then, the RenderControl method of the GridView class is used, which outputs the server control content to the provided HtmlTextWriter, which is htmlWrite. Finally, this is written to the response stream.

The job is supposed to be done at this stage! But when you 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:

public override void VerifyRenderingInServerForm(Control control)
{
}

That's it! The data in the GridView would be exported to the Excel file which would be saved at the desktop.

But there are some problems with this solution:

  1. If you use paging in your GridView, then only the data of individual pages would be exported instead of the data of the whole grid. That means only those data would be exported which are rendered within the page.
  2. If the data in the GridView is huge, you would probably get the following error:
  3. "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 the completion of the operation.

To solve this type of problem, we would use the following solution:

Solution

I am going to use the SQLCommand class because by using it, 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 (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:

 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();
}

It would solve the problem of having to download large data. The problem of the SQLClient timeout exception is solved, but there is still a possibility to get an HtttpRequest timeout exception. To solve this, put the following lines inside the <system.web> tag of the web.config file.

<httpRuntime maxRequestLength="2097151" executionTimeout="3600"/></httpruntime>

That's it!

Points of Interest

So, we have learned how to pull data from a database and show it in a GridView control, export data from a GridView to an Excel file, and a way of exporting huge data from a datareader and exporting that data to an Excel file.

Hope this example helps you. Feel free to give me any suggestions regarding this article.

Happy coding!

History

  • This demo was uploaded in June, 2007.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fuad Bin Omar
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

 
Follow on   Twitter

Comments and Discussions

 
QuestionHow about a version that exports to an Excel template? Pinmemberrandydouglas200424-May-12 5:43 
GeneralA suggestion Pinmemberettore.cefala22-Jun-10 1:52 
GeneralExcel Automation PinmemberFilipKrnjic9-Jul-09 3:37 
Hi,
 
using Excel Automation has many problems. I think it is better to use some third party component (at least in commercial applications) to work with Excel. I recommend you to try GemBox.Spreadsheet - component for importing/exporting data to XLS, CSV, HTML and XLSX files. Here you can find some reasons why is it better to use GemBox.Spreadsheet than Excel Automation.
 
We have articles section where we explain how to use GemBox.Spreadsheet for some common operations. Here is the example that explains how to use GemBox.Spreadsheet to export or import DataTable to XLS, CSV, HTML or XLSX files.
 
If you are looking for a free component, note that we have GemBox.Spreadsheet Free version that you can use even in commercial applications. Free version has 150 rows limit.
 
Filip
www.GemBoxSoftware.com
GeneralRegisterForEventValidation can only be called during Render(); PinmemberCalinga10-Sep-08 10:42 
Generaldata bind not found Pinmemberkanza azhar9-Apr-08 9:20 
GeneralToString is eeevil Pinmembertaoklerks20-Feb-08 13:37 
GeneralHI! save on hard drive PinmemberTwoEyez6-Aug-07 1:22 
GeneralTimeout error solution PinmemberPhilProgrammer17-Jul-07 11:44 
GeneralRe: Timeout error solution Pinmemberrezkeya21-Jul-07 16:40 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140823.1 | Last Updated 20 Jun 2007
Article Copyright 2007 by Fuad Bin Omar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid