M
MattC
Hi,
I am trying to export the contents of a DataGrid to Excel. I have already
found the following articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317719
http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp
These methods _only_ work if the following is true:
1.) Paging and sorting is disabled; (produces error stating that controls
must reside within form tag and specify runat=server)
2.) Columns are autogenerated (unless see 3);
3.) If columns are not autogenerated template columns in the form of the
following causes errors; (cannot alter controls that contain <% %>;
<asp:TemplateColumn HeaderText="Day">
<ItemTemplate>
<%# DataBinder.Eval(Container, "DataItem.DayOfWeek") %>
</ItemTemplate>
</asp:TemplateColumn>
The Code I am using is at the bottom of this post.
I was wondering how to go about the following - or if there is a 3rd party
control that does this.
Either:
1.) Pass the original datasource to some kind of custom control. This would
also be given the properties of the datasource (in my case strongly typed
collections) and the control would then loop through using reflection to
Retrieve the values and generate a new HtmlTable that is then rendered to
the Response stream.
2.) Contruct a new grid using the original and only constructing columns
specified (not sure how to manually create a grid yet.)
The first I beleive is far more extensible as it allows my object model and
any available property in it to be exported to excel, but obviously this
will have a longer dev time. Is this even possible.
Any help would be greatly appreciated.
TIA
MattC
protected void ExportToExcel(DataGrid grid)
{
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = String.Empty;
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new
System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(grid);
grid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
protected void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text =
(string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text =
(string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}
I am trying to export the contents of a DataGrid to Excel. I have already
found the following articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317719
http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp
These methods _only_ work if the following is true:
1.) Paging and sorting is disabled; (produces error stating that controls
must reside within form tag and specify runat=server)
2.) Columns are autogenerated (unless see 3);
3.) If columns are not autogenerated template columns in the form of the
following causes errors; (cannot alter controls that contain <% %>;
<asp:TemplateColumn HeaderText="Day">
<ItemTemplate>
<%# DataBinder.Eval(Container, "DataItem.DayOfWeek") %>
</ItemTemplate>
</asp:TemplateColumn>
The Code I am using is at the bottom of this post.
I was wondering how to go about the following - or if there is a 3rd party
control that does this.
Either:
1.) Pass the original datasource to some kind of custom control. This would
also be given the properties of the datasource (in my case strongly typed
collections) and the control would then loop through using reflection to
Retrieve the values and generate a new HtmlTable that is then rendered to
the Response stream.
2.) Contruct a new grid using the original and only constructing columns
specified (not sure how to manually create a grid yet.)
The first I beleive is far more extensible as it allows my object model and
any available property in it to be exported to excel, but obviously this
will have a longer dev time. Is this even possible.
Any help would be greatly appreciated.
TIA
MattC
protected void ExportToExcel(DataGrid grid)
{
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = String.Empty;
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new
System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(grid);
grid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
protected void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls);
}
if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text =
(string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch
{
}
control.Parent.Controls.Remove(control);
}
else
if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text =
(string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}