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.

Following step do 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 pull data from a database and display it in the grid 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.

Create a <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 controls Lables, Text Box, GridView also 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.Xml.Linq;
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 .aspx page in Page_Load Event

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

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

On Export Button

On export button write this code & generate 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 export 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!