I am using GridViewHelper to generate a group column. It works great. However, when I export the results to excel file format, the last row of the gridview always gets cutoff. I wonder if anyone experiencing the same problem. Is there a resolution for it?
I have exported other gridviews without GridViewHelper intergration and they come out fine.
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.
Thanks for the info. It doesn't cut off the bottom rows anymore. However, it looks redundant without hiding the group column. I suppose there is no other way around.
Ok I just dug this up through searching ... has anybody found a way to export the GridViewHelper generated gridview completely?
Looks like the Export To Excel function recognizes the number of rows of the original gridview display so when GridViewHelper inserts new rows for the grouping, the display on the excel would only be up to the number of rows of the ungrouped gridview.
The problem (as you mention) is that GridViewHelper adds group header and summary rows to the underlying table but not to the gridview Rows collection. This is desirable behavior by GridViewHelper so that we can access just the details rows via the Rows
collection. The other issue is that GridViewHelper optionally hides the group by columns, so we must hide them as well in the exporter. This exporter code seems to solve both these problems:
public class GridViewExportHelper
{
private HtmlTextWriter writer;
public HtmlTextWriter Writer
{
get { return writer = writer ?? new HtmlTextWriter(new StringWriter()); }
set { writer = value; }
}
public string GetExportGridContent(GridView gridView, bool preserveStyles, bool preserveLinks)
{
RenderGrid(gridView, preserveStyles, preserveLinks);
return Writer.InnerWriter.ToString();
}
private void RenderGrid(GridView gridView, bool preserveStyles, bool preserveLinks)
{
Table table = (Table)gridView.Controls[0];
bool hasHeader = gridView.HeaderRow != null;
bool hasFooter = gridView.FooterRow != null;
table.GridLines = gridView.GridLines;
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = gridView.Columns.Count - 1; j >= 0; j--)
{
if (!gridView.Columns[j].Visible && table.Rows[i].Cells.Count > j)
{
table.Rows[i].Cells.RemoveAt(j);
}
}
TableItemStyle style = null;
if (preserveStyles)
{
if (i == 0 && hasHeader)
{
style = gridView.HeaderStyle;
}
else if (i == table.Rows.Count - 1 && hasFooter)
{
style = gridView.FooterStyle;
}
else
{
style = gridView.RowStyle;
}
}
TransformRow(table.Rows[i], style, preserveLinks);
}
table.RenderControl(Writer);
}
private static void TransformRow(Control control, Style style, bool preserveLinks)
{
if (control is WebControl && style != null)
{
((WebControl)control).ApplyStyle(style);
}
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
Control child = control.Controls[i];
if (!preserveLinks && typeof(LinkButton).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((LinkButton)child).Text));
}
else if (typeof(ImageButton).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ImageButton)child).AlternateText));
}
else if (!preserveLinks && typeof(HyperLink).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((HyperLink)child).Text));
}
else if (typeof(ListControl).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ListControl)child).SelectedItem.Text));
}
else if (typeof(ICheckBoxControl).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ICheckBoxControl)child).Checked ? "True" : "False"));
}
if (child.HasControls())
{
TransformRow(child, null, preserveLinks);
}
}
}
}
Note that this code, unlike Matt's exporter simply returns the HTML as a string. I did it that way because my base page has a method that writes the response as an attachment using the HTML generated by this class.
The main difference, and what makes it work, is that it iterates Rows collection of the underlying table, not the gridview.Rows collection. It assumes (safely, I beleive) that if the gridview has a header, then the first row of the table is the header,
and likewise with the footer. I hope this works for you.
P.S. This code has one known limitation. If you specify preserveLinks = false, then LinkButton controls will be rendered as plain text; however, HyperLinkField columns are not detected and thus are always rendered as links. I spent a little time on this
but did not find a solution.
Okay, I re-did this helper class to make it simpler. Per Samu's post it's simpler to render the grid instead of the underlying table. Here's the simplfied class. This version also provides a nice feature to include the styles:
public class GridViewExportHelper
{
public string GetExportGridContent(GridView gridView, bool preserveLinks, string cssPath)
{
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
Table table = (Table)gridView.Controls[0];
foreach (GridViewRow row in table.Rows)
{
TransformRow(row, preserveLinks);
}
if (!string.IsNullOrEmpty(cssPath))
{
RenderCss(cssPath, htw);
}
gridView.RenderControl(htw);
return sw.ToString();
}
private void RenderCss(string cssPath, HtmlTextWriter htw)
{
StreamReader sr = new StreamReader(cssPath);
htw.RenderBeginTag(HtmlTextWriterTag.Head);
htw.RenderBeginTag(HtmlTextWriterTag.Style);
htw.Write(sr.ReadToEnd());
htw.RenderEndTag();
htw.RenderEndTag();
sr.Close();
}
private static void TransformRow(Control control, bool preserveLinks)
{
for (int i = control.Controls.Count - 1; i >= 0; i--)
{
Control child = control.Controls[i];
if (!preserveLinks && typeof(LinkButton).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((LinkButton)child).Text));
}
else if (typeof(ImageButton).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ImageButton)child).AlternateText));
}
else if (!preserveLinks && typeof(HyperLink).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((HyperLink)child).Text));
}
else if (typeof(ListControl).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ListControl)child).SelectedItem.Text));
}
else if (typeof(ICheckBoxControl).IsAssignableFrom(child.GetType()))
{
control.Controls.Remove(child);
control.Controls.AddAt(i, new LiteralControl(((ICheckBoxControl)child).Checked ? "True" : "False"));
}
if (child.HasControls())
{
TransformRow(child, preserveLinks);
}
}
}
}
The only requirement is that each page that uses the helper must override VerifyRenderingInServerForm like so:
public override void VerifyRenderingInServerForm(Control control) { }
I tried to use it like this but the output in excel is still missing the bottom rows ... am I using it incorrectly?
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=test.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
Dim gv As GridViewExportHelper = New GridViewExportHelper
Dim gs As String = gv.GetExportGridContent(GridView1, false, "")
Response.Write(gs)
Response.End()
blumonde
Participant
1866 Points
494 Posts
GridViewHelper and Excel Export
Dec 03, 2007 11:20 PM|LINK
I am using GridViewHelper to generate a group column. It works great. However, when I export the results to excel file format, the last row of the gridview always gets cutoff. I wonder if anyone experiencing the same problem. Is there a resolution for it? I have exported other gridviews without GridViewHelper intergration and they come out fine.
Thanks.
blumonde
no pain no gain
Samu Zhang -...
All-Star
62163 Points
6101 Posts
Re: GridViewHelper and Excel Export
Dec 06, 2007 03:14 AM|LINK
Hi blumonde ,
I have test using the GridViewHelper sample. I clicked radio button which text is Group(don't hide group column) and it works fine.
Then I add a button to export the table to excel and it works fine too. All the rows in gridview were exported into excel.
Here is the code.
protected void Button1_Click(object sender, EventArgs e) { Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls"); Response.ContentType = "application/excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); this.GridView1.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); }Samu Zhang
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.
blumonde
Participant
1866 Points
494 Posts
Re: GridViewHelper and Excel Export
Dec 06, 2007 04:38 AM|LINK
Hi Samu,
Thanks for the info. It doesn't cut off the bottom rows anymore. However, it looks redundant without hiding the group column. I suppose there is no other way around.
blumonde
no pain no gain
Gau
Member
15 Points
12 Posts
Re: GridViewHelper and Excel Export
Jan 10, 2008 08:31 AM|LINK
Ok I just dug this up through searching ... has anybody found a way to export the GridViewHelper generated gridview completely?
Looks like the Export To Excel function recognizes the number of rows of the original gridview display so when GridViewHelper inserts new rows for the grouping, the display on the excel would only be up to the number of rows of the ungrouped gridview.
timtas
Member
24 Points
27 Posts
Re: GridViewHelper and Excel Export
Jan 11, 2008 02:16 PM|LINK
I encountered this issue, and came up with the following solution. It's a modified version of Matt Berseth's exporter, found here :http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
The problem (as you mention) is that GridViewHelper adds group header and summary rows to the underlying table but not to the gridview Rows collection. This is desirable behavior by GridViewHelper so that we can access just the details rows via the Rows collection. The other issue is that GridViewHelper optionally hides the group by columns, so we must hide them as well in the exporter. This exporter code seems to solve both these problems:
public class GridViewExportHelper { private HtmlTextWriter writer; public HtmlTextWriter Writer { get { return writer = writer ?? new HtmlTextWriter(new StringWriter()); } set { writer = value; } } public string GetExportGridContent(GridView gridView, bool preserveStyles, bool preserveLinks) { RenderGrid(gridView, preserveStyles, preserveLinks); return Writer.InnerWriter.ToString(); } private void RenderGrid(GridView gridView, bool preserveStyles, bool preserveLinks) { Table table = (Table)gridView.Controls[0]; bool hasHeader = gridView.HeaderRow != null; bool hasFooter = gridView.FooterRow != null; table.GridLines = gridView.GridLines; for (int i = 0; i < table.Rows.Count; i++) { for (int j = gridView.Columns.Count - 1; j >= 0; j--) { if (!gridView.Columns[j].Visible && table.Rows[i].Cells.Count > j) { table.Rows[i].Cells.RemoveAt(j); } } TableItemStyle style = null; if (preserveStyles) { if (i == 0 && hasHeader) { style = gridView.HeaderStyle; } else if (i == table.Rows.Count - 1 && hasFooter) { style = gridView.FooterStyle; } else { style = gridView.RowStyle; } } TransformRow(table.Rows[i], style, preserveLinks); } table.RenderControl(Writer); } private static void TransformRow(Control control, Style style, bool preserveLinks) { if (control is WebControl && style != null) { ((WebControl)control).ApplyStyle(style); } for (int i = control.Controls.Count - 1; i >= 0; i--) { Control child = control.Controls[i]; if (!preserveLinks && typeof(LinkButton).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((LinkButton)child).Text)); } else if (typeof(ImageButton).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ImageButton)child).AlternateText)); } else if (!preserveLinks && typeof(HyperLink).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((HyperLink)child).Text)); } else if (typeof(ListControl).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ListControl)child).SelectedItem.Text)); } else if (typeof(ICheckBoxControl).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ICheckBoxControl)child).Checked ? "True" : "False")); } if (child.HasControls()) { TransformRow(child, null, preserveLinks); } } } }Note that this code, unlike Matt's exporter simply returns the HTML as a string. I did it that way because my base page has a method that writes the response as an attachment using the HTML generated by this class.
The main difference, and what makes it work, is that it iterates Rows collection of the underlying table, not the gridview.Rows collection. It assumes (safely, I beleive) that if the gridview has a header, then the first row of the table is the header, and likewise with the footer. I hope this works for you.
P.S. This code has one known limitation. If you specify preserveLinks = false, then LinkButton controls will be rendered as plain text; however, HyperLinkField columns are not detected and thus are always rendered as links. I spent a little time on this but did not find a solution.
blumonde
Participant
1866 Points
494 Posts
Re: GridViewHelper and Excel Export
Jan 11, 2008 04:59 PM|LINK
Thanks for the solution, Timtas. I really appreciate it.
blumonde
no pain no gain
timtas
Member
24 Points
27 Posts
Re: GridViewHelper and Excel Export
Jan 11, 2008 06:38 PM|LINK
Okay, I re-did this helper class to make it simpler. Per Samu's post it's simpler to render the grid instead of the underlying table. Here's the simplfied class. This version also provides a nice feature to include the styles:
public class GridViewExportHelper { public string GetExportGridContent(GridView gridView, bool preserveLinks, string cssPath) { StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); Table table = (Table)gridView.Controls[0]; foreach (GridViewRow row in table.Rows) { TransformRow(row, preserveLinks); } if (!string.IsNullOrEmpty(cssPath)) { RenderCss(cssPath, htw); } gridView.RenderControl(htw); return sw.ToString(); } private void RenderCss(string cssPath, HtmlTextWriter htw) { StreamReader sr = new StreamReader(cssPath); htw.RenderBeginTag(HtmlTextWriterTag.Head); htw.RenderBeginTag(HtmlTextWriterTag.Style); htw.Write(sr.ReadToEnd()); htw.RenderEndTag(); htw.RenderEndTag(); sr.Close(); } private static void TransformRow(Control control, bool preserveLinks) { for (int i = control.Controls.Count - 1; i >= 0; i--) { Control child = control.Controls[i]; if (!preserveLinks && typeof(LinkButton).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((LinkButton)child).Text)); } else if (typeof(ImageButton).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ImageButton)child).AlternateText)); } else if (!preserveLinks && typeof(HyperLink).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((HyperLink)child).Text)); } else if (typeof(ListControl).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ListControl)child).SelectedItem.Text)); } else if (typeof(ICheckBoxControl).IsAssignableFrom(child.GetType())) { control.Controls.Remove(child); control.Controls.AddAt(i, new LiteralControl(((ICheckBoxControl)child).Checked ? "True" : "False")); } if (child.HasControls()) { TransformRow(child, preserveLinks); } } } }The only requirement is that each page that uses the helper must override VerifyRenderingInServerForm like so:Gau
Member
15 Points
12 Posts
Re: GridViewHelper and Excel Export
Jan 11, 2008 09:54 PM|LINK
Thanks for this timtas! I'll convert this to VB then try it [Yes]
Gau
Member
15 Points
12 Posts
Re: GridViewHelper and Excel Export
Jan 14, 2008 05:49 AM|LINK
I tried to use it like this but the output in excel is still missing the bottom rows ... am I using it incorrectly?
Response.Clear() Response.AddHeader("content-disposition", "attachment;filename=test.xls") Response.Charset = "" Response.ContentType = "application/vnd.ms-excel" Dim sw As System.IO.StringWriter = New System.IO.StringWriter Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw) Dim gv As GridViewExportHelper = New GridViewExportHelper Dim gs As String = gv.GetExportGridContent(GridView1, false, "") Response.Write(gs) Response.End()timtas
Member
24 Points
27 Posts
Re: GridViewHelper and Excel Export
Jan 14, 2008 06:06 AM|LINK
Hmm, not sure. Here's the method I use to write the content:
protected void WriteResponse(object content, string contentType, bool asAttachment, string filename, params object[] filenameArgs) { if (content != null) { filename = string.Format(filename, filenameArgs); bool isBinary = content.GetType().IsAssignableFrom(typeof(byte[])); Response.ClearHeaders(); Response.ClearContent(); Response.ContentType = contentType; string header = string.Format("{0}Filename={1}", asAttachment ? "attachment; " : string.Empty, filename); Response.AppendHeader("Content-Disposition", header); try { if (isBinary) { Response.AppendHeader("Content-Length", ((byte[])content).Length.ToString()); Response.Buffer = true; Response.BinaryWrite((byte[])content); } else { Response.AppendHeader("Content-Length", Encoding.Default.GetByteCount(content.ToString()).ToString()); Response.Write(content); } } catch (Exception) { Response.ClearContent(); } finally { Response.End(); } } }