Gridview to Excel export

Question

5.00/5 (1 vote)

See more:

HI,
I need to export Gridview contents to an excel sheet. I tried searching and got many solutions. Among them i used the most simple one. But the problem is that it is exporting the entire HTML page instead of selected GridView. Here's my code:

protected void exportToExcel_Click(object sender, EventArgs e)
    {
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        try
        {
            
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
            Response.Charset = "";
            GridView1.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
            
        }
        catch (Exception except1)
        {
            Response.Write(except1);
        }
    }

Please suggest me how to do the exporting stuff. I'm new to C# environment.

Posted 6-Jan-13 22:06pm

Sriram Mani

Updated 6-Jan-13 22:24pm

v2

Add a Solution

Comments

Christian Graus 7-Jan-13 4:12am

This is way too complex for people new to C#. Why are you doing it ?

Sriram Mani 7-Jan-13 4:34am

Graus,
Just to learn.

Christian Graus 7-Jan-13 4:36am

Well, if you're new to C#, it's a dumb learning project. Learn to program, not to interact with Excel. Even ASP.NET is a bad choice to learn C#, winforms is far better.

Sriram Mani 7-Jan-13 4:36am

Graus,
I'm developing an ASP.NET website. I just wanted to have the functionality of exporting data from a gridview to excel.

Christian Graus 7-Jan-13 4:37am

So you're not 'just learning' at all. Didn't think so.

Sriram Mani 7-Jan-13 4:43am

Graus,
I'm learning to develop an ASP.NET site.. and completed some level in it. Trying to move to the new level(of learning ofcourse).

Christian Graus 7-Jan-13 4:51am

I recommend learning jquery and AJAX through jquery, that's generally useful stuff, what you're doing now, you'll hardly ever use.

Sriram Mani 7-Jan-13 4:54am

Thanks Graus,
I'll start learning them. If you have had any ideas about this exporting stuff, please guide me.

Thanks in advance.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ranjeet.waje · Answer 1 · 2013-01-06T22:24:00

Hi,

If your filling your grid view by fetching data from database then following will be useful to you...

protected void btnExport_Click(object sender, EventArgs e)
{

try
{
string strconstring = ConfigurationManager.AppSettings["con"]; //database connection string from web.config file
SqlConnection cn = new SqlConnection(strconstring); // connection to DB

SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
string strSQL = "";
cn.Open();

strSQL = "select * from table1 "; //your select query which display data in gridview

C#

<pre lang="text">

}
da.SelectCommand = new SqlCommand(strSQL, cn);
da.Fill(dt);

//DataTable dt = new DataTable();

//dt = (DataTable)Session["MonthlyCostingData"];

Response.Clear();

string attachment = "attachment; filename=AfterSalesPerformance_" + DateTime.Now.Date.ToString() + ".xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/vnd.ms-excel";

string tab = "";

foreach (DataColumn dc in dt.Columns)
{

Response.Write(tab + dc.ColumnName);

tab = "\t";

}

Response.Write("\n");

int i;

foreach (DataRow dr in dt.Rows)
{

tab = "";

for (i = 0; i < dt.Columns.Count; i++)
{
//dr[i].Style.Add("background-color", "#FFFFC0");

Response.Write(tab + dr[i].ToString());

tab = "\t";

}

Response.Write("\n");

}

Response.End();
}
#endregion

catch (System.Threading.ThreadAbortException lException)
{
// do nothing
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}

Syed Ali Raza · Answer 2 · 2013-01-06T22:57:00

If you want to export data from Directly gridview then you can try this code.
It will export the GridView Data in Excel regardless of source of database.

string filename = "EmployeeRecord.xls";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

//Get the HTML for the control.
dgGrid.RenderControl(hw);
//Write the HTML back to the browser.
//Response.ContentType = application/vnd.ms-excel;
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
this.EnableViewState = false;
Response.Write(tw.ToString());

Response.End();

Hopefully It will help you.

ranjeet.waje · Answer 3 · 2013-01-06T22:47:00

Solution 2

Sorry I don't have any idea about any kind of such possibilities..

Posted 6-Jan-13 22:47pm

ranjeet.waje

Comments

Sriram Mani 7-Jan-13 4:49am

Thanks ranjitwaje. I'll post the solution if i find one.

joe_j · Answer 4 · 2013-01-07T02:17:00

try this.
recently encountered a similar problem as yours

VB

Response.Clear()
          Response.ClearHeaders()
          Response.AddHeader("content-disposition", "attachment; filename=" + newTempFile)
          Response.AddHeader("content-length", New FileInfo(filePath + newTempFile).Length.ToString())
          Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

          Response.WriteFile(Server.MapPath("~/temp/") + newTempFile)
          Response.Flush()
          File.Delete(Server.MapPath("~/temp/") + newTempFile)
          Response.End()

please change the variable like filePath, newTempFile etc to suite your needs

and make sure you have given proper permissions on your webserver.

Gridview to Excel export

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0