gridview to excel

  • Thread starter Thread starter JohnE
  • Start date Start date
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
 
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.
 
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
 
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?

...........
 
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);
}
}
}
 
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?
Click to expand...
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...


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".
 
Back
Top