Click here to Skip to main content
11,708,312 members (74,502 online)
Click here to Skip to main content

Export Gridview Data to Excel in ASP.NET

, 17 Dec 2012 CPOL 201.4K 11.8K 28
Rate this:
Please Sign up or sign in to vote.
This tip describes how to export data to an Excel file from a GridView.

Introduction

This tip describes how to export data to an Excel file from a GridView. We will create a sample database and see how the data can be shown in the GridView and then export in Excel format.

Background

There are many scenarios where we would want to export data into Excel format. The data could be from a GridView or from any other data bound control. In this article, we will see how we can export data from a GridView to Excel.

Using the Code

In this tip, I'm trying to explain how to create or export GridView to an MS-Excel file in ASP.NET using C#. I have also placed a button on the page for exporting data to a Microsoft Excel file. We have used a GridView which is bound and a button which will be used to create an Excel file.

For this, I have taken a database named demodb in which there is a table with the following columns:

Now when we have the table structure ready, we have a function for binding the Gridview for which I have used a Dataset. So let us look at the code that fetches data from the database and then binds the data to the grid to display it on the webpage.

protected void fillGrid()
{
    string str = "SELECT [UNo], [EmpName], [Age], 
    	convert(char,[dob],103) dob FROM [tbl_EmpDetails]";

    myConnection = new SqlConnection(conn);
    myConnection.Open();
    myCommand = new SqlCommand(str, myConnection);
    SqlDataAdapter mySQLDataAdapter;
    myDataSet = new DataTable();
    mySQLDataAdapter = new SqlDataAdapter(myCommand);
    mySQLDataAdapter.Fill(myDataSet);
    GridView1.DataSource = myDataSet;
    GridView1.DataBind();
    ViewState["dtList"] = myDataSet;
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        fillGrid();
    }
}

Now, after binding a Gridview, data is ready to get exported to an Excel file. We click on a button named Export to Excel. I have used FileInfo to get the information related to the file.

FileInfo FI = new FileInfo(Path);
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
DataGrid DataGrd = new DataGrid();
DataGrd.DataSource = dt1;
DataGrd.DataBind();

DataGrd.RenderControl(htmlWrite);
string directory = Path.Substring(0, Path.LastIndexOf("\\"));// GetDirectory(Path);
if (!Directory.Exists(directory))
{
    Directory.CreateDirectory(directory);
}

System.IO.StreamWriter vw = new System.IO.StreamWriter(Path, true);
stringWriter.ToString().Normalize();
vw.Write(stringWriter.ToString());
vw.Flush();
vw.Close();
WriteAttachment(FI.Name, "application/vnd.ms-excel", stringWriter.ToString());

The above code uses a WriteAttachment function which pushes the attachment to the user in the Response object. The following code shows the implementation of WriteAttachment:

public static void WriteAttachment(string FileName, string FileType, string content)
{
    HttpResponse Response = System.Web.HttpContext.Current.Response;
    Response.ClearHeaders();
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName);
    Response.ContentType = FileType;
    Response.Write(content);
    Response.End();
}

Point of Interest

In this tip, we have seen how to export data to an Excel file in ASP.NET. It is rather easy, but many new developers struggle with it so I wrote this to help them.

History

  • 16th October 2012: First version

License

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

Share

About the Author

AshishChaudha
Software Developer
India India
I am a Software Engineer from Bhopal. I started my Career from Programming in ASP and now working as a Web Developer in ASP.Net (C#). I Love coding and always ready to gain new thing and always been towards Microsoft Technologies. Apart from coding my other hobbies are traveling, Internet Surfing, spending time with family and hang out with friends.

http://www.webtekspace.blogspot.in/

You may also be interested in...

Comments and Discussions

 
QuestionThis is a wonderful component to export datatable or gridview to excel 2007/2010 files. Pin
Jack Smith17-Jun-14 20:09
memberJack Smith17-Jun-14 20:09 
SuggestionMessage Automatically Removed Pin
17-Jun-14 1:24
memberMember 862432517-Jun-14 1:24 
GeneralMy vote of 5 Pin
Deepak Rana1119-Dec-13 0:50
professionalDeepak Rana1119-Dec-13 0:50 
AnswerRe: My vote of 5 Pin
AshishChaudha19-Dec-13 0:55
professionalAshishChaudha19-Dec-13 0:55 
QuestionOK Pin
Deepak Rana1119-Dec-13 0:12
professionalDeepak Rana1119-Dec-13 0:12 
QuestionMessage Automatically Removed Pin
14-Oct-13 23:12
memberMember 1022174114-Oct-13 23:12 
GeneralMy vote of 4 Pin
Rachelle Villar22-Aug-13 20:58
memberRachelle Villar22-Aug-13 20:58 
AnswerRe: My vote of 4 Pin
AshishChaudha22-Aug-13 21:04
professionalAshishChaudha22-Aug-13 21:04 
QuestionPath does not work Pin
Manish Bhardwaj4-Jul-13 17:42
memberManish Bhardwaj4-Jul-13 17:42 
QuestionMessage Removed Pin
Member 87073803-Jun-13 10:17
memberMember 87073803-Jun-13 10:17 
AnswerRe: Hi Code is working fine but not working on Server Pin
jonlink013-Jun-13 11:07
memberjonlink013-Jun-13 11:07 
AnswerRe: Hi Code is working fine but not working on Server Pin
AshishChaudha3-Jun-13 17:49
professionalAshishChaudha3-Jun-13 17:49 
GeneralRe: Hi Code is working fine but not working on Server Pin
jonlink013-Jun-13 21:31
memberjonlink013-Jun-13 21:31 
AnswerRe: Hi Code is working fine but not working on Server Pin
AshishChaudha3-Jun-13 22:51
professionalAshishChaudha3-Jun-13 22:51 
GeneralRe: Hi Code is working fine but not working on Server Pin
jonlink014-Jun-13 21:57
memberjonlink014-Jun-13 21:57 
GeneralRe: Hi Code is working fine but not working on Server Pin
AshishChaudha4-Jun-13 23:58
professionalAshishChaudha4-Jun-13 23:58 
GeneralRe: Hi Code is working fine but not working on Server Pin
jonlink015-Jun-13 5:53
memberjonlink015-Jun-13 5:53 
GeneralMessage Removed Pin
Member 87073805-Jun-13 8:14
memberMember 87073805-Jun-13 8:14 
AnswerRe: Hi Code is working fine but not working on Server Pin
AshishChaudha5-Jun-13 18:05
professionalAshishChaudha5-Jun-13 18:05 
Questionexporting grid view data to excel Pin
Member 1004784720-May-13 23:50
memberMember 1004784720-May-13 23:50 
AnswerRe: exporting grid view data to excel Pin
AshishChaudha21-May-13 21:27
professionalAshishChaudha21-May-13 21:27 
QuestionExport to Excel Pin
alexk1957199420-Feb-13 3:49
memberalexk1957199420-Feb-13 3:49 
QuestionMy vote of 5 Pin
bandi.ramesh15-Feb-13 22:04
memberbandi.ramesh15-Feb-13 22:04 
AnswerRe: My vote of 5 Pin
AshishChaudha7-Apr-13 17:56
professionalAshishChaudha7-Apr-13 17:56 
GeneralMy vote of 5 Pin
bandi.ramesh15-Feb-13 0:48
memberbandi.ramesh15-Feb-13 0:48 
AnswerRe: My vote of 5 Pin
AshishChaudha17-Feb-13 17:55
memberAshishChaudha17-Feb-13 17:55 
GeneralMy vote of 5 Pin
Mitesh Machhi5-Feb-13 20:22
memberMitesh Machhi5-Feb-13 20:22 
AnswerRe: My vote of 5 Pin
AshishChaudha6-Feb-13 7:12
memberAshishChaudha6-Feb-13 7:12 
GeneralRe: My vote of 5 Pin
Jibesh12-Feb-13 10:14
memberJibesh12-Feb-13 10:14 
Questionerror occure Pin
Member 81650405-Feb-13 0:02
memberMember 81650405-Feb-13 0:02 
QuestionThanks Pin
alllaudhin22-Jan-13 0:51
memberalllaudhin22-Jan-13 0:51 
Questionexport gridview data to excel Pin
priti shrivastava26-Dec-12 3:05
memberpriti shrivastava26-Dec-12 3:05 
AnswerRe: export gridview data to excel Pin
AshishChaudha26-Dec-12 17:34
memberAshishChaudha26-Dec-12 17:34 
AnswerRe: export gridview data to excel Pin
karthikreddy08a5030-Jan-13 0:54
memberkarthikreddy08a5030-Jan-13 0:54 
GeneralMy vote of 5 Pin
opv12320-Dec-12 21:05
memberopv12320-Dec-12 21:05 
AnswerRe: My vote of 5 Pin
AshishChaudha20-Dec-12 21:37
memberAshishChaudha20-Dec-12 21:37 
QuestionExporting GridView data to excel & PDF with sugession Pin
varaprasadreddy.bh19-Dec-12 7:04
membervaraprasadreddy.bh19-Dec-12 7:04 
Questionadd some more columns Pin
kamil shaikh17-Dec-12 21:21
memberkamil shaikh17-Dec-12 21:21 
AnswerRe: add some more columns Pin
AshishChaudha17-Dec-12 21:36
memberAshishChaudha17-Dec-12 21:36 
Suggestionanother similar article Pin
Lacy0016-Oct-12 22:18
memberLacy0016-Oct-12 22:18 
AnswerRe: another similar article Pin
AshishChaudha23-Oct-12 0:16
memberAshishChaudha23-Oct-12 0:16 
GeneralRe: another similar article Pin
bandi.ramesh15-Feb-13 22:02
memberbandi.ramesh15-Feb-13 22:02 
GeneralMy vote of 5 Pin
Mits Machhi16-Oct-12 18:07
memberMits Machhi16-Oct-12 18:07 
AnswerRe: My vote of 5 Pin
AshishChaudha16-Oct-12 18:10
memberAshishChaudha16-Oct-12 18:10 
Thanks
AnswerRe: My vote of 5 Pin
AshishChaudha21-May-13 21:28
professionalAshishChaudha21-May-13 21:28 

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 | Terms of Use | Mobile
Web01 | 2.8.150819.1 | Last Updated 17 Dec 2012
Article Copyright 2012 by AshishChaudha
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid