gridview to excel [ASP .NET]

Prev: Confirm dialog and ajax call
Next: How to add namespace to <html> node

From: JohnE on 2 Feb 2010 04:03

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[i].GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls[i] as LinkButton).Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}

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

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

if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
}
}
}

"sloan" wrote:

> 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?
>
> ...........
>
>
>
> "JohnE" <JohnE(a)discussions.microsoft.com> wrote in message
> news:706CF33D-9174-460D-B579-61BC4730921F(a)microsoft.com...
> > Nevermind in responding. I figured it out.
> > John
> >
> >
> > "JohnE" wrote:
> >
> >> 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
> >>
> >>
> >>
> >> "Alvin Bruney - ASP.NET MVP" wrote:
> >>
> >> > 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.
> >> >
> >> > --
> >> > Vapordan
> >> > Shameless Author Plug
> >> > ASP.NET 4 by Example only $20
> >> > OWC Blackbook www.lulu.com/owc
> >> >
> >> > "JohnE" <JohnE(a)discussions.microsoft.com> wrote in message
> >> > news:7811E0AF-EC85-412E-964E-8234A7A9953A(a)microsoft.com...
> >> > > 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
> >> > >
> >> > >
>
>
> .
>

From: Alexey Smirnov on 3 Feb 2010 17:34

On Feb 2, 10:03 am, JohnE <Jo...(a)discussions.microsoft.com> wrote:
> 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[i].GetType() == typeof(LinkButton))
> {
> l.Text = (gv.Controls[i] as LinkButton)..Text;
> gv.Controls.Remove(gv.Controls[i]);
> gv.Controls.AddAt(i, l);
> }
>
> else if (gv.Controls[i].GetType() == typeof(DropDownList))
> {
> l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
> gv.Controls.Remove(gv.Controls[i]);
> gv.Controls.AddAt(i, l);
> }
>
> else if (gv.Controls[i].GetType() == typeof(CheckBox))
> {
> l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" :
> "False";
> gv.Controls.Remove(gv.Controls[i]);
> gv.Controls.AddAt(i, l);
> }
>
> if (gv.Controls[i].HasControls())
> {
> PrepareGridViewForExport(gv.Controls[i]);
> }
> }
> }
>
>
>
> "sloan" wrote:
> > 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?
>
> > ...........
>
> > "JohnE" <Jo...(a)discussions.microsoft.com> wrote in message
> >news:706CF33D-9174-460D-B579-61BC4730921F(a)microsoft.com...
> > > Nevermind in responding. I figured it out.
> > > John
>
> > > "JohnE" wrote:
>
> > >> 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
>
> > >> "Alvin Bruney - ASP.NET MVP" wrote:
>
> > >> > 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.
>
> > >> > --
> > >> > Vapordan
> > >> > Shameless Author Plug
> > >> > ASP.NET 4 by Example only $20
> > >> > OWC Blackbookwww.lulu.com/owc
>
> > >> > "JohnE" <Jo...(a)discussions.microsoft.com> wrote in message
> > >> >news:7811E0AF-EC85-412E-964E-8234A7A9953A(a)microsoft.com...
> > >> > > 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
>
> > .- 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".

First | Prev |
Pages: 1 2
Prev: Confirm dialog and ajax call
Next: How to add namespace to <html> node