I have 3 gridviews on my page, all with the same columns. I want the user to be able to click on a button and it export all three gridviews to an Excel file, whether on 3 different worksheets or the same worksheet (preferrably the same, but either will do).
And in code behind, I insert footer rows to total by department, so if I use this
Export to Excel control it will export only 1 gridview at a time and won't export the inserted footer rows separating the departments.
Look at this article
which has a class to export the gridview. Create an overloaded method that takes an array of gridviews instead of a single gridview. Write the gridviews one by one and when all the gridviews are written send it to the browser via response.write. Following
is an example - I have just modified the original code to suite your requirement, but this qas done in the editor itself and hasn't been tested - but you get the idea.
Public Shared Sub Export(ByVal fileName As String, ByVal gvs As List(of GridView))
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
for each gv as GridView in gvs
' Create a form to contain the grid
Dim table As Table = New Table
table.GridLines = gv.GridLines
' add the header row to the table
If (Not (gv.HeaderRow) Is Nothing) Then
PrepareControlForExport(gv.HeaderRow)
table.Rows.Add(gv.HeaderRow)
End If
' add each of the data rows to the table
For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row)
table.Rows.Add(row)
Next
' add the footer row to the table
If (Not (gv.FooterRow) Is Nothing) Then
PrepareControlForExport(gv.FooterRow)
table.Rows.Add(gv.FooterRow)
End If
' render the table into the htmlwriter
table.RenderControl(htw)
next gv
' render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.End()
End Sub
I actually got it working (the mulitple GridViews export). Unfortunately, this doesn't export all the (footer/total) rows I explicitly insert after the GridView is rendered. Here is my solution in C# with the help of codeasp
//creating the array of GridViews and calling the Export function
GridView[] gvList = new GridView[] {GridView2, GridView3, GridView4};
Export("member.xls", gvList);
public static void Export(string fileName, GridView[] gvs)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
foreach (GridView gv in gvs) {
// Create a form to contain the grid
Table table = new Table();
table.GridLines = gv.GridLines;
// add the header row to the table
if (!(gv.HeaderRow == null)) {
PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows) {
PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (!(gv.FooterRow == null)) {
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.End();
}
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 CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
PrepareControlForExport(current);
}
}
}
Now I need to figure out how to export those footer/total rows I explicitly insert after the GridView has been rendered.
1. Create a method that takes gridview as a parameter and add the above method body to it (modify to suit the method - take out calls to Render etc)
2. After you have databound the gridviews, call the above method, so that each gridview has a footer information as needed.
3. Call the Export method you posted in your previous post. Because you have already done the customization (footer info) to the grid, you do not have to alter this method.
I haven't tested this, but in theory it should work. Reply if you have any problem.
jrossh21
Member
77 Points
142 Posts
Export multiple, edited gridviews to Excel
Feb 18, 2010 04:22 PM|LINK
I have 3 gridviews on my page, all with the same columns. I want the user to be able to click on a button and it export all three gridviews to an Excel file, whether on 3 different worksheets or the same worksheet (preferrably the same, but either will do).
And in code behind, I insert footer rows to total by department, so if I use this Export to Excel control it will export only 1 gridview at a time and won't export the inserted footer rows separating the departments.
Anybody got any other solutions?
codeasp
Star
14569 Points
2517 Posts
Re: Export multiple, edited gridviews to Excel
Feb 18, 2010 06:27 PM|LINK
Look at this article which has a class to export the gridview. Create an overloaded method that takes an array of gridviews instead of a single gridview. Write the gridviews one by one and when all the gridviews are written send it to the browser via response.write. Following is an example - I have just modified the original code to suite your requirement, but this qas done in the editor itself and hasn't been tested - but you get the idea.
Public Shared Sub Export(ByVal fileName As String, ByVal gvs As List(of GridView)) HttpContext.Current.Response.Clear() HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName)) HttpContext.Current.Response.ContentType = "application/ms-excel" Dim sw As StringWriter = New StringWriter Dim htw As HtmlTextWriter = New HtmlTextWriter(sw) for each gv as GridView in gvs ' Create a form to contain the grid Dim table As Table = New Table table.GridLines = gv.GridLines ' add the header row to the table If (Not (gv.HeaderRow) Is Nothing) Then PrepareControlForExport(gv.HeaderRow) table.Rows.Add(gv.HeaderRow) End If ' add each of the data rows to the table For Each row As GridViewRow In gv.Rows PrepareControlForExport(row) table.Rows.Add(row) Next ' add the footer row to the table If (Not (gv.FooterRow) Is Nothing) Then PrepareControlForExport(gv.FooterRow) table.Rows.Add(gv.FooterRow) End If ' render the table into the htmlwriter table.RenderControl(htw) next gv ' render the htmlwriter into the response HttpContext.Current.Response.Write(sw.ToString) HttpContext.Current.Response.End() End Subjrossh21
Member
77 Points
142 Posts
Re: Export multiple, edited gridviews to Excel
Feb 18, 2010 07:07 PM|LINK
How would I that first line in c#?
public static void Export(string fileName, List[] gvs) ?
Also, how would I get the gridviews into that List array in c#?
Thanks for the reply btw :)
jrossh21
Member
77 Points
142 Posts
Re: Export multiple, edited gridviews to Excel
Feb 18, 2010 07:24 PM|LINK
I actually got it working (the mulitple GridViews export). Unfortunately, this doesn't export all the (footer/total) rows I explicitly insert after the GridView is rendered. Here is my solution in C# with the help of codeasp
//creating the array of GridViews and calling the Export function GridView[] gvList = new GridView[] {GridView2, GridView3, GridView4}; Export("member.xls", gvList); public static void Export(string fileName, GridView[] gvs) { HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; System.IO.StringWriter sw = new System.IO.StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); foreach (GridView gv in gvs) { // Create a form to contain the grid Table table = new Table(); table.GridLines = gv.GridLines; // add the header row to the table if (!(gv.HeaderRow == null)) { PrepareControlForExport(gv.HeaderRow); table.Rows.Add(gv.HeaderRow); } // add each of the data rows to the table foreach (GridViewRow row in gv.Rows) { PrepareControlForExport(row); table.Rows.Add(row); } // add the footer row to the table if (!(gv.FooterRow == null)) { 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.End(); } 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 CheckBox) { control.Controls.Remove(current); control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False")); } if (current.HasControls()) { PrepareControlForExport(current); } } }Now I need to figure out how to export those footer/total rows I explicitly insert after the GridView has been rendered.
codeasp
Star
14569 Points
2517 Posts
Re: Export multiple, edited gridviews to Excel
Feb 21, 2010 06:20 PM|LINK
Post the code that does the footer row - both markup and code.
jrossh21
Member
77 Points
142 Posts
Re: Export multiple, edited gridviews to Excel
Feb 22, 2010 03:02 PM|LINK
I don't do anything in the .aspx page (except for the style of the footer row), so here is my over ridden render function in my code behind:
protected override void Render(HtmlTextWriter writer) { Table gridTable = (Table)(GridView2.Controls[0]); string lastValue = GridView2.Rows[0].Cells[0].Text.Substring(0, 2); int tot1Pot = 0; int tot2Pot = 0; foreach (GridViewRow gvr in GridView2.Rows) { //This is separating the departments with a "footer" row that includes totals for that department string currentValue = gvr.Cells[0].Text.Substring(0, 2); if (lastValue.CompareTo(currentValue) != 0) { int rowIndex = gridTable.Rows.GetRowIndex(gvr); GridViewRow totalRow = new GridViewRow(rowIndex, rowIndex, DataControlRowType.DataRow, DataControlRowState.Normal); for (int i = 0; i < 3; i++) { TableCell totalCell = new TableCell(); totalCell.CssClass = "SortHeaderRowStyle"; totalRow.Cells.Add(totalCell); } totalRow.Cells[0].Text = lastValue + " Total"; totalRow.Cells[1].Text = tot1Pot.ToString(); totalRow.Cells[2].Text = tot2Pot.ToString(); gridTable.Controls.AddAt(rowIndex, totalRow); lastValue = currentValue; } tot1Pot = tot1Pot + Convert.ToInt32(gvr.Cells[1].Text); tot2Pot = tot2Pot + Convert.ToInt32(gvr.Cells[2].Text); } base.Render(writer); }codeasp
Star
14569 Points
2517 Posts
Re: Export multiple, edited gridviews to Excel
Feb 22, 2010 06:24 PM|LINK
1. Create a method that takes gridview as a parameter and add the above method body to it (modify to suit the method - take out calls to Render etc)
2. After you have databound the gridviews, call the above method, so that each gridview has a footer information as needed.
3. Call the Export method you posted in your previous post. Because you have already done the customization (footer info) to the grid, you do not have to alter this method.
I haven't tested this, but in theory it should work. Reply if you have any problem.