Click here to Skip to main content
16,016,306 members
Search within:


Why is exporting GridView data to Excel, exports the entire page instead?
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
ASP.NET
Gridview
I have a GridView in my Content page along with some DropDownList which I use for filtering:

ASP.NET
<asp:Panel ID="pnlDropDown" runat="server" ClientIDMode="Static" CssClass="pnlDropDown">
    	<!-- TASK NAME -->
    	<asp:DropDownList ID="ddlTaskName" CssClass="chosen-select" DataSourceID="dsPopulateTaskName" AutoPostBack="true" DataValueField="Task Name" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlTaskName_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateTaskName" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- SERVICE -->
    	<asp:DropDownList ID="ddlService" CssClass="chosen-select" DataSourceID="dsPopulateService" AutoPostBack="true" DataValueField="Service" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlService_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateService" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- STATUS -->
    	<asp:DropDownList ID="ddlStatus" CssClass="chosen-select" DataSourceID="dsPopulateStatus" AutoPostBack="true" DataValueField="Status" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlStatus_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateStatus" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- DUE DATE -->
    	<asp:DropDownList ID="ddlDueDate" CssClass="chosen-select" DataSourceID="dsPopulateDueDate" AutoPostBack="true" DataValueField="Due Date" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlDueDate_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateDueDate" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- OWNER -->
    	<asp:DropDownList ID="ddlOwner" CssClass="chosen-select" DataSourceID="dsPopulateOwner" AutoPostBack="true" DataValueField="Owner" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlOwner_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateOwner" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- CLIENT -->
    	<asp:DropDownList ID="ddlClient" CssClass="chosen-select" DataSourceID="dsPopulateClient" AutoPostBack="true" DataValueField="Client" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlClient_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateClient" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- SITE -->
    	<asp:DropDownList ID="ddlSite" CssClass="chosen-select" DataSourceID="dsPopulateSite" AutoPostBack="true" DataValueField="Site" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlSite_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateSite" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- PRACTICE -->
    	<asp:DropDownList ID="ddlPractice" CssClass="chosen-select" DataSourceID="dsPopulatePractice" AutoPostBack="true" DataValueField="Practice" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlPractice_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulatePractice" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    
    	<!-- PROVIDER -->
    	<asp:DropDownList ID="ddlProvider" CssClass="chosen-select" DataSourceID="dsPopulateProvider" AutoPostBack="true" DataValueField="Provider" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlProvider_onSelectIndexChanged">
    		<asp:ListItem Text="All" Value="%"></asp:ListItem>
    	</asp:DropDownList>
    	<asp:SqlDataSource ID="dsPopulateProvider" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand=""></asp:SqlDataSource>
    </asp:Panel>
    <asp:GridView ShowHeaderWhenEmpty="false" AlternatingRowStyle-BackColor="#EBE9E9" AutoGenerateColumns="false" OnSorting="yourTasksGV_Sorting" AllowSorting="true" ID="yourTasksGV" runat="server" ClientIDMode="Static" EmptyDataText="You currently have no tasks assigned to you" OnRowDataBound="yourTasksGV_RowDataBound" OnRowCreated="yourTasksGV_RowCreated">
    	<Columns>
    		<asp:HyperLinkField Target="_self" DataNavigateUrlFields="Task Detail" DataTextField="Task Name" DataNavigateUrlFormatString="" HeaderText="Task Detail" SortExpression="Task Name" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Service" HeaderText="Service" SortExpression="Service" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Due Date" HeaderText="Due" SortExpression="Due Date" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Owner" HeaderText="Owner" SortExpression="Owner" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Client" HeaderText="Client" SortExpression="Client" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Site" HeaderText="Site" SortExpression="Site" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Practice" HeaderText="Practice" SortExpression="Practice" ItemStyle-CssClass="taskTableColumn" />
    		<asp:BoundField DataField="Provider" HeaderText="Provider" SortExpression="Provider" ItemStyle-CssClass="taskTableColumn" />
    	</Columns>
    </asp:GridView>


I populate the GridView from code-behind and also attach all the DropDownList to the GridView as the first header row:

C#
protected void yourTasksGV_RowCreated(object sender, GridViewRowEventArgs e)
    {
    	if (e.Row.RowType == DataControlRowType.Header)
    	{
    		GridView hGrid = (GridView)sender;
    		GridViewRow gvrRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
    
    
    		TableHeaderCell tcCellTask = new TableHeaderCell();
    		tcCellTask.Controls.Add(ddlTaskName);
    		gvrRow.Cells.Add(tcCellTask);
    
    		TableHeaderCell tcCellSvc = new TableHeaderCell();
    		tcCellSvc.Controls.Add(ddlService);
    		gvrRow.Cells.Add(tcCellSvc);
    
    		TableHeaderCell tcCellStats = new TableHeaderCell();
    		tcCellStats.Controls.Add(ddlStatus);
    		gvrRow.Cells.Add(tcCellStats);
    
    		TableHeaderCell tcCellDD = new TableHeaderCell();
    		tcCellDD.Controls.Add(ddlDueDate);
    		gvrRow.Cells.Add(tcCellDD);
    
    		TableHeaderCell tcCellOwner = new TableHeaderCell();
    		tcCellOwner.Controls.Add(ddlOwner);
    		gvrRow.Cells.Add(tcCellOwner);
    
    		TableHeaderCell tcCellCli = new TableHeaderCell();
    		tcCellCli.Controls.Add(ddlClient);
    		gvrRow.Cells.Add(tcCellCli);
    
    		TableHeaderCell tcCellSit = new TableHeaderCell();
    		tcCellSit.Controls.Add(ddlSite);
    		gvrRow.Cells.Add(tcCellSit);
    
    		TableHeaderCell tcCellPra = new TableHeaderCell();
    		tcCellPra.Controls.Add(ddlPractice);
    		gvrRow.Cells.Add(tcCellPra);
    
    		TableHeaderCell tcCellPro = new TableHeaderCell();
    		tcCellPro.Controls.Add(ddlProvider);
    		gvrRow.Cells.Add(tcCellPro);
    
    		yourTasksGV.Controls[0].Controls.AddAt(0, gvrRow);
    
    		LinkButton btnSort;
    		System.Web.UI.WebControls.Image image;
    		//iterate through all the header cells
    		foreach (TableCell cell in e.Row.Cells)
    		{
    			//check if the header cell has any child controls
    			if (cell.HasControls())
    			{
    				//get reference to the button column
    				btnSort = (LinkButton)cell.Controls[0];
    				image = new System.Web.UI.WebControls.Image();
    				if (ViewState["sortExp"] != null)
    				{
    					//see if the button user clicked on and the sortexpression in the viewstate are same
    					//this check is needed to figure out whether to add the image to this heade column or not
    					if (btnSort.CommandArgument == ViewState["sortExp"].ToString())
    					{
    						//check what type of sort order it is
    						if (ViewState["sortOrder"].ToString() == "Asc")
    						{
    							image.ImageUrl = "../theImages/up.png";
    							image.CssClass = "hdrImage";
    						}
    						else
    						{
    							image.ImageUrl = "../theImages/down.png";
    							image.CssClass = "hdrImage";
    						}
    						cell.Controls.Add(image);
    					}
    				}
    			}
    		}
    	}
    }


I have a button in the MasterPage which, when the user clicks, takes that GridView in the Content page and exports is:

ASP.NET
<asp:LinkButton ID="btnExport" runat="server" Text="Excel" ClientIDMode="Static" OnClick="btnExport_Click" CssClass="linkOff" />


C#
protected void btnExport_Click(object sender, EventArgs e)
    { 
    	Response.ClearContent();
    	Response.Buffer = true;
    
    	Response.AddHeader("content-disposition", "attachment;filename=yourTaskList.xls");
    	Response.Charset = "";
    	Response.ContentType = "application/excel";
    	StringWriter sw = new StringWriter();
    	HtmlTextWriter hw = new HtmlTextWriter(sw);
    
    	GridView gvTasks = (GridView)ContentMain.FindControl("yourTasksGV"); //accessing the control from the content page
    	if (gvTasks != null && gvTasks.Rows.Count > 0)
    	{
    		gvTasks.AllowPaging = false;
    		//gvTasks.DataBind();
    
    		gvTasks.HeaderRow.Style.Add("background", "#CCCCCC");
    
    		//Apply style to Individual Cells
    		gvTasks.HeaderRow.Cells[0].Style.Add("background-color", "#E2E2E2");
    		gvTasks.HeaderRow.Cells[1].Style.Add("background-color", "#E2E2E2");
    		gvTasks.HeaderRow.Cells[2].Style.Add("background-color", "#E2E2E2");
    		gvTasks.HeaderRow.Cells[3].Style.Add("background-color", "#E2E2E2");
    		gvTasks.HeaderRow.Cells[4].Style.Add("background-color", "#E2E2E2");
    		gvTasks.HeaderRow.Cells[5].Style.Add("background-color", "#E2E2E2");
    
    		for (int i = 0; i <= 4; i++)
    		{
    			gvTasks.HeaderRow.Cells[i].Style.Add("height", "30px");
    		}
    
    		for (int i = 0; i < gvTasks.Rows.Count; i++)
    		{
    			GridViewRow row = gvTasks.Rows[i];
    
    			//Change Color back to white
    			row.BackColor = System.Drawing.Color.White;
    
    			//Apply text style to each Row
    			row.Attributes.Add("class", "textmode");
    
    			//Apply style to Individual Cells of Alternating Row
    			if (i % 2 != 0)
    			{
    				row.Cells[0].Style.Add("background-color", "#C2D69B");
    				row.Cells[1].Style.Add("background-color", "#C2D69B");
    				row.Cells[2].Style.Add("background-color", "#C2D69B");
    				row.Cells[3].Style.Add("background-color", "#C2D69B");
    				row.Cells[4].Style.Add("background-color", "#C2D69B");
    			}
    		}
    		gvTasks.RenderControl(hw);
    
    		//style to format numbers to string
    		string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    		Response.Write(style);
    		Response.Output.Write(sw.ToString());
    		Response.Flush();
    		Response.End();
    	}
    }


When I click the button it fails to export, when I retry it, it opens and instead of just saving the GridView, it opens the webpage instead inside Excel.

How do I modify/add to my code to make it work correctly?

The page itself gets exported not the GridView!

UPDATE: The error happens when I want to OPEN it without saving. When I save and then open, it works fine.
Posted
Updated 2-Sep-14 9:02am
v2
Add a Solution

1 solution

public static void Export(GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=DataExport.xls");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();

// include the gridline settings
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
//string lastHeaderText = gv.HeaderRow.Cells[gv.Columns.Count - 1].Text;
//if (lastHeaderText == "Select")
//{


//{
if (gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdInvestment" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdFinanceEquity" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_GridViewMRDInvestmentData" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdMRDEquity")
{
gv.HeaderRow.Cells[gv.Columns.Count - 1].Text = "";
gv.HeaderRow.Cells[gv.Columns.Count - 2].Text = "";
gv.HeaderRow.Cells[gv.Columns.Count - 3].Text = "";
}

//if (gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_GridViewMRDInvestmentData")
//{
// gv.HeaderRow.Cells[gv.Columns.Count - 8].Text = "";
// gv.HeaderRow.Cells[gv.Columns.Count - 9].Text = "";
//}
GridviewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
if (gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdInvestment" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdFinanceEquity" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_GridViewMRDInvestmentData" || gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_grdMRDEquity")
{
row.Cells[gv.Columns.Count - 1].Text = "";
row.Cells[gv.Columns.Count - 2].Text = "";
row.Cells[gv.Columns.Count - 3].Text = "";
}
//if (gv.ClientID.ToString() == "ctl00_ContentPlaceHolder1_GridViewMRDInvestmentData")
//{
// row.Cells[gv.Columns.Count - 8].Text = "";
// row.Cells[gv.Columns.Count - 9].Text = "";
//}
GridviewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridviewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);

// render the htmlwriter into the response

HttpContext.Current.Response.Write(sw.ToString());
// HttpContext.Current.Response.Write(gv.ClientID.ToString());
HttpContext.Current.Response.End();
}
}
}
///
/// Replace any of the contained controls with literals
///
/// <param name="control" />
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
//control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is HiddenField)
{
control.Controls.Remove(current);
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
// control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridviewExportUtil.PrepareControlForExport(current);
}
}
}
C#
 
Share this answer
 
Posted
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Excel export data in MVC
Gridview to Excel export
Export data from gridview to excel problem
Export Gridview to Excel but the whole aspx page exported instead
Export Data from gridview to Excel
How to export ASP.NET grid to excel (.XLSX) format, and also at the same time I want to export link buttons, hyperlinks into excel
gridview data is not exporting to excel.
Export to Excel Gridview in master pages
problem with Gridview export to excel along with image
export gridview data to excel

Advertise
Privacy
Cookies
Terms of Use
Last Updated 3 Sep 2014
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900