Export Large Data from GridView to Excel File using C#

A good way to display data is to show it in a GridView. 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.

The following step does this.

Using the code

This sample uses ASP.NET 2.0, C#, and SQL Server 2000. I am using a simple form & database for fast data retrieval. First, we pull data from a database and display it in the grid, and then we export the data from the grid to the Excel file.

That's the process to handle large amounts of data and combat various errors.

<div id="divprint">

On an aspx page, create a div tag with the id divPrint, then copy & paste your GridView source code, then close the div tag with </div>.

On .aspx page

First, add control Labels, Text Box, GridView, and Button. In the Go button, write the code to fill the GridView by calling the Fillgrid() function. In a TextBox, pass a query to the Go button. Click event, then Gridview Fill.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;

Fillgrid function code

public void fillGrid()
{
    string constr1;
    IFormatProvider culture = new CultureInfo("fr-FR", true);

    constr1 = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    con = new SqlConnection(constr1);
    con.Open();

    cmd = new SqlCommand(TextBox1.Text, con);
    adp = new SqlDataAdapter(cmd);
    ds = new DataSet();
    adp.Fill(ds);

    gv.DataSource = ds;
    gv.DataBind();

    con.Close();
}

On the .aspx page in Page_Load Event.

On the page load event, write this code and set the Button.Attributes["onclick"].

Button2.Attributes["onclick"] = "javascript:CallPrint('divPrint');";

On Export Button

On the export button, write this code & generate a Save As popup box.

string attachment = "attachment; filename=Export.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

// Create a form to contain the grid
HtmlForm frm = new HtmlForm();
gv.Parent.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(gv);
frm.RenderControl(htw);

// GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

After doing this, your GridView data can be exported to the Excel file Export.xls.

Conclusion

This article showed how to pull data from a database and show it in the GridView control. We have learned how to export data from GridView to an Excel file. I hope that this code helps you. Feel free to give me any suggestions regarding this article.

Happy coding!