gridview to excel


J

JohnE

I have a gridview on a page that is to export to excel. Below is the code
that is used. Also placed the EnableEventValidation="false" at the top of
the aspx as well as the verifyingrenderinginserviceform. I have googled and
found info on exporting and most all show what I have below. I added the
paging and sorting lines as the export still looked like the gridview. There
should be 184 records in the spreadsheet for the user to manipulate, but
rather the gridview look is there. The user should be able to see a
spreadsheet to do what they further need to do with the information.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
gvwChangeRequestList.AllowPaging = false;
gvwChangeRequestList.AllowSorting = false;
ExportToExcel();
}

//Export to Excel from a GridView
protected void ExportToExcel()
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition",
"attachment;filename=ChangeRequest.xls");
Response.Charset = "";
this.EnableViewState = false;

System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new
System.Web.UI.HtmlTextWriter(sw);

gvwChangeRequestList.RenderControl(htw);

Response.Write(sw.ToString());
Response.End();
}

The gridview uses a sqldatasource. Hoping someone can shed some light on
this as to what is wrong or missing (reference, line, or something).

Thanks...John
 
Ad

Advertisements

A

Alvin Bruney - ASP.NET MVP

The GridView needs to be on a page by itself, no other server controls
should be present. Then you can change the content type and it will work.
 
J

JohnE

Thanks for the reply. I found a way to get the full grid. In my sample code
just after the paging and sorting lines I rebound the sqldatasource. I also
added a line turning off the Edit command in the gridview so it doesn't show
in the spreadsheet (gvwChangeRequestList.AutoGenerateEditButton = false;).
But, there is a a Detail command link as well. Line below.

<asp:CommandField SelectText="Detail" ShowSelectButton="true"
ButtonType="Link"/>

I would like to turn this off as well so it doesn't show on the spreadsheet.
Do you happen to know how that would be done?

Thanks.
John
 
S

sloan

How about posting your (own) solution......so that when someone finds this
thread a year from now via googling...........it isn't a dead end?

...........
 
J

JohnE

Incase if anyone is interested, here is the whole export to excel. It could
use some polish, which will occur over time, but here is what is working.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
gvwChangeRequestList.AllowPaging = false;
gvwChangeRequestList.AllowSorting = false;
gvwChangeRequestList.AutoGenerateEditButton = false;
gvwChangeRequestList.Columns[0].Visible = false;
gvwChangeRequestList.DataSourceID = "ChangeRequestListSqlDataSource";
gvwChangeRequestList.DataBind();
PrepareGridViewForExport(gvwChangeRequestList);
ExportToExcel();
}

//Export to Excel from a GridView
protected void ExportToExcel()
{
Response.Clear();
Response.Buffer = true;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition",
"attachment;filename=ChangeRequest.xls");
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new
System.Web.UI.HtmlTextWriter(sw);
gvwChangeRequestList.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal l = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, l);
}

else if (gv.Controls.GetType() == typeof(DropDownList))
{
l.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, l);
}

else if (gv.Controls.GetType() == typeof(CheckBox))
{
l.Text = (gv.Controls as CheckBox).Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, l);
}

if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}
 
Ad

Advertisements

G

Guest

Incase if anyone is interested, here is the whole export to excel.  It could
use some polish, which will occur over time, but here is what is working.

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        gvwChangeRequestList.AllowPaging = false;
        gvwChangeRequestList.AllowSorting = false;
        gvwChangeRequestList.AutoGenerateEditButton = false;
        gvwChangeRequestList.Columns[0].Visible = false;
        gvwChangeRequestList.DataSourceID = "ChangeRequestListSqlDataSource";
        gvwChangeRequestList.DataBind();
        PrepareGridViewForExport(gvwChangeRequestList);
        ExportToExcel();
    }

    //Export to Excel from a GridView
    protected void ExportToExcel()
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("content-disposition",
"attachment;filename=ChangeRequest.xls");
        Response.Charset = "";
        this.EnableViewState = false;
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new
System.Web.UI.HtmlTextWriter(sw);
        gvwChangeRequestList.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

    private void PrepareGridViewForExport(Control gv)
    {
        LinkButton lb = new LinkButton();
        Literal l = new Literal();
        string name = String.Empty;

        for (int i = 0; i < gv.Controls.Count; i++)
        {
            if (gv.Controls.GetType() == typeof(LinkButton))
            {
                l.Text = (gv.Controls as LinkButton)..Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, l);
            }

            else if (gv.Controls.GetType() == typeof(DropDownList))
            {
                l.Text = (gv.Controls as DropDownList).SelectedItem.Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, l);
            }

            else if (gv.Controls.GetType() == typeof(CheckBox))
            {
                l.Text = (gv.Controls as CheckBox).Checked ? "True" :
"False";
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, l);
            }

            if (gv.Controls.HasControls())
            {
                PrepareGridViewForExport(gv.Controls);
            }
        }
    }



sloan said:
How about posting your (own) solution......so that when someone finds this
thread a year from now via googling...........it isn't a dead end?
.- Hide quoted text -

- Show quoted text -


I think the problem with this approach is that it will open an Excel,
but you can't save it in xls format without using "Save As".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top