Export large data from Gridview to Excel file using C#

Prabhakar
Posted by in ASP.NET category on for Intermediate level | Points: 250 | Views : 42332 red flag
Rating: 4 out of 5  
 2 vote(s)

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.

Following step do this



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 to fast data retrieving. Firstly pull data from a database and display it in the grid then we are export data from the grid to the Excel file.

That’s the process to handle large amounts of data and combat different types of errors.

Create a <DIV id=”divprint”>

On aspx page create a div tag for id div print then copy & Paste ur gridview source code then close div tag with </div>

 

On .aspx page


Firstly add  controls Lables, Text Box. Gridview also Button .  In Go button write the code to fill girdview with calling Fillgrid() Function .. in a Textbox pass a query on Go button Click event . Then Gridview Fill .



Using these Namespaces

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 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 you do this Ur gridview data exports in Excel File Export.xls

Conculsion

On this article  we learned how to pull data from a database and show it in the grid view 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!

Regard's

Prabhakar

Page copy protected against web site content infringement by Copyscape

About the Author

Prabhakar
Full Name: prabhakar parihar
Member Level:
Member Status: Member,MVP
Member Since: 1/12/2011 5:05:40 AM
Country: India
Best Regard's Prabhakar
http://www.dotnetfunda.com
Currently working as a Software Engineer (Developer) [ ASP.NET , C# ]

Login to vote for this post.

Comments or Responses

Posted by: Bhanubysani on: 9/12/2011 | Points: 25
hi

this code is working fine in some pages..but in some pages its not working.but i wrote in aspx page page directive as enableeventvalidation=false;

then its working fine..why the reason for that..plz explain me
Posted by: Nikunj.mochi on: 10/17/2011 | Points: 25
Hi really helpful,

Would you please tell me while importing more than 50,000 rows in excel, i am only able to see 22579 rows only,

did you have faced such issue?

Thanks
Posted by: Vinay13mar on: 11/4/2012 | Points: 25
Hi this is really nice article . Please check this link and let me know your view

http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=22&title=Gridview Export To Excel In Asp.Net C#
Posted by: Nachi on: 12/29/2012 | Points: 25
Hi,
Please explain how this program works.
I am new to C#

Login to post response

Comment using Facebook(Author doesn't get notification)