Export Gridview to Excel
Posted by Ramani Sandeep on April 7, 2009
The focus of the article is the Export to Excel functionality – the Gridview and it’s data binding are only for demonstrating the Export functionality.The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.
Default.aspx :
<asp:SqlDataSource ID="sqldsCustomers" runat="server" SelectCommand="select * from UserDetails"
SelectCommandType="Text" ConnectionString="server=server;database=LegalWindow;Trusted_Connection=yes;" />
<asp:GridView ID="gvCustomers" runat="server" AllowPaging="true" AllowSorting="true"
PageSize="10″" DataSourceID="sqldsCustomers" GridLines="Both" />
<asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />
Default.aspx.cs :
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
// pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}
GridViewExportUtil.cs
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
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.End();
}
}
}
//Export Gridview Data to Excel File and Save Excel file to Server Folder Rather than
//allowing user to Open or Save it.
public static void ExportToFolder(string fileName, GridView gv)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using (StringWriter sw = new StringWriter(sb))
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
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);
//Create file
System.IO.TextWriter w = new System.IO.StreamWriter(HttpContext.Current.Server.MapPath("~") + "\\" + fileName);
w.Write(sb.ToString());
w.Flush();
w.Close();
}
}
}
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())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}
For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility. For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound. You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting. Here is the code for the export button click handler
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
if (this.rdoBtnListExportOptions.SelectedIndex == 1)
{
// the user wants all rows exported, turn off paging
// and rebing the grid before sending it to the export
// utility
this.gvCustomers.AllowPaging = false;
this.gvCustomers.DataBind();
}
else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
{
// the user wants just the first 100,
// adjust the PageSize and rebind
this.gvCustomers.PageSize = 100;
this.gvCustomers.DataBind();
}
// pass the grid that for exporting …
GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
}
For More Extensive Study of GridView Export to Excel………………read more
Advertisement
dave said
I am currently useing this utility however I want to pass the criteria the user selected sent to the excel as well. For Example.
Job Cost Report
Dates 9/21/09 – 9/27/2009
Branches 2,3,4
System Types A, B, C, D
ID Name Customer Address City State
1 Dave Big Bus. 123 Street PGH PA
ramanisandeep said
I did not understand properly what u want to do exactly !!
Michael said
This works well. The only problem is that once the button is pushed, the page no longer posts back on any of the buttons. Links work fine, but none of the post backs work anymore.
Any ideas?
ramanisandeep said
i have tried it in my project, i m not getting any such problem yet. might something wrong..u need to paste some code for that..
pojek kat said
i’ll tried the coding above to my system but not working..this is the error with red underline at code GridViewExportUtil.Export(“Customers.xls”, this.GridView1);”the name GridViewExportUtil does not exist in the current context”.how do i solve this prob?:-(
Ramani Sandeep said
have you created GridViewExportUtil class in app_code folder or not?
this error indicating that class is not available..
please check it & create the class first.
Hope this will help
Ramani Sandeep
joel said
Good stuff – works well. One question though. I have a footer row added at data bind time containing grand totals and these do not get exported. I’m sure it’s because the object type of control is not in the if statement. I believe it to be a TableCell type and this cannot be cast as a LiteralControl so in the PrepareControlForExport method is gets passed over… Any workaround you can come up with?
Ramani Sandeep said
How you are adding Footer row in databind event?
Because i have already covered the code to handle the footer row to be printed..
Joel said
Sorry for the long delay. In the RowDataBound event I call a method CreateGridViewRow which does nothing more than create a gridviewrow and then a series of Cells.Add each containing a value and some formatting…
Sathish said
This is the one which I am looking for. Thanks a lot. BTW, As Joel said I can not even get the Footer in the Export. Showfooter=true and I add the summary info at the RowBound event. Any row which I added at run time is not getting into export.
Sathish said
Any help on this?
Christie said
I’m having same issue (extra header and footer row added but not showing up) … any solutions??
Thanks
Ramani Sandeep said
Hi Christie,
can you put some code so that i can help you more..
Cheers !!!
Christie said
Thanks so much! I am hoping this will be easy
So, what I have is a gridview that I’ve set up using markup. However, I am adding two new rows at run-time – An extra header row that spans on top of sum summarized data (so that I can show MTD, Prev Year MTD, etc. on top of the repeated columns for Sales, GM$) I’ve also added summarized totals to an already existing footer row. The problem is that when I call the GridViewExportUtil method to export, it does not recognize the header row I’ve added nor does it recognize the already existing footer?
Where the gridview is named ResultsGrid, here is the code I have added to the DataBound method to add the new header row to the grid and then call the second method to add the totals to the existing footer. Again, it seems that the method to export just does not see the new header row OR the existing footer row??
Any help is GREATLY appreciated!! Thanks
Christie
protected void ResultsGrid_DataBound(object sender, EventArgs
e)
{
GridView grid = sender as GridView;
bool showCustomer = false;
if (grid != null && grid.Rows.Count > 0)
{
GridViewRow row = new GridViewRow(0, -1,
DataControlRowType.Header, DataControlRowState.Normal);
switch (GroupingDropDown.SelectedIndex)
{
case 2:
showCustomer = true;
break;
default:
break;
}
TableHeaderCell product = new TableHeaderCell();
if (showCustomer)
{
product.ColumnSpan = 2;
}
else
{
product.ColumnSpan = 1;
}
row.Cells.Add(product);
TableHeaderCell currentMTD = new TableHeaderCell();
currentMTD.ColumnSpan = 3;
currentMTD.Text = “MTD”;
row.Cells.Add(currentMTD);
TableHeaderCell prevYearMTD = new TableHeaderCell();
prevYearMTD.ColumnSpan = 3;
prevYearMTD.Text = “Prev Year MTD”;
row.Cells.Add(prevYearMTD);
TableHeaderCell MTDDiff = new TableHeaderCell();
MTDDiff.ColumnSpan = 3;
MTDDiff.Font.Italic = true;
MTDDiff.Text = “MTD Diff”;
row.Cells.Add(MTDDiff);
TableHeaderCell currentYTD = new TableHeaderCell();
currentYTD.ColumnSpan = 3;
currentYTD.Text = “YTD”;
row.Cells.Add(currentYTD);
TableHeaderCell prevYTD = new TableHeaderCell();
prevYTD.ColumnSpan = 3;
prevYTD.Text = “Prev YTD”;
row.Cells.Add(prevYTD);
TableHeaderCell YTDDiff = new TableHeaderCell();
YTDDiff.ColumnSpan = 3;
YTDDiff.Font.Italic = true;
YTDDiff.Text = “YTD Diff”;
row.Cells.Add(YTDDiff);
Table t = grid.Controls[0] as Table;
if (t != null)
{
t.Rows.AddAt(0, row);
}
grid = AddTotalRowToGridView(grid, true);
}
}
protected GridView AddTotalRowToGridView(GridView Grid, bool
ShowTotalTextInFirstColumn)
{
DataTable dtTotals = new DataTable();
DataRow dr = dtTotals.NewRow();
int intTemp = 0;
int rowVal = 0;
for (int iColumn = 0; iColumn < Grid.Rows[0].Cells.Count;
iColumn++)
{
dtTotals.Columns.Add("Column" + iColumn);
}
for (int iRow = 0; iRow < Grid.Rows.Count; iRow++)
{
if (iRow == 0 && ShowTotalTextInFirstColumn)
{
dr["Column0"] = "Totals:";
}
if(Grid.Rows[iRow].RowType.Equals(DataControlRowType.DataRow))
{
for(int iCurrentColumn = 0; iCurrentColumn < Grid.Rows[iCurrentColumn].Cells.Count; iCurrentColumn++)
{
string rowValText = Grid.Rows[iRow].Cells[iCurrentColumn].Text;
rowValText = rowValText.Replace("$", "");
rowValText = rowValText.Replace("%", "");
rowValText = rowValText.Replace("(", "-");
rowValText = rowValText.Replace(")", "");
rowValText = rowValText.Replace(",", "");
bool success = int.TryParse(rowValText, out rowVal);
if (!rowVal.Equals(0) && !
iCurrentColumn.Equals(0) && ShowTotalTextInFirstColumn.Equals(true))
{
if(dr["Column" +
iCurrentColumn].ToString().Equals(""))
{
dr["Column" + iCurrentColumn] = rowVal;
}
else
{
intTemp = Convert.ToInt32(dr["Column"
+ iCurrentColumn].ToString());
intTemp += rowVal;
dr["Column" + iCurrentColumn] = intTemp;
intTemp = 0;
}
}
}
}
}
dtTotals.Rows.Add(dr);
for(int iFooterColumn = 0; iFooterColumn < Grid.FooterRow.Cells.Count – 1; iFooterColumn++)
{
if (!
dtTotals.Rows[0].ItemArray[iFooterColumn].ToString().Equals("") && !
dtTotals.Rows[0].ItemArray[iFooterColumn].ToString().Equals("Totals:"))
{
Int32 total =
Convert.ToInt32(dtTotals.Rows[0].ItemArray[iFooterColumn].ToString());
Grid.FooterRow.Cells[iFooterColumn].Text = string.Format("{0:c0}", total);
}
}
return Grid;
}
Elaell said
Hi Christie
i have same issue . Can you solved this ?
anyone can help me .plz
Praveen said
Here is complete details about all problems we face in this process:
http://praveenbattula.blogspot.com/2010/09/gridview-and-export-to-excel.html
Divyang Sojitra said
Thanx
This code is really helpful to me. but I have question about storing images using this file upload in folder.
B’coz I dont know how many images uploaded by User so I have to dynamically store images in folder. But I tried so much but I couldnt do this. I hope You will help me.
Thanx
Takecare
JSK
Me said
How is the PrepareControlForExport method returning its value? The parameter is not a ref.
Ramani Sandeep said
Hi, PrepareControlForExport method is not returning anything. it just do some processing so that we can export gridview. so thats it. it does not return anything at all. it sets few property of the class that can be later on used during export call. hope this helps.
let me know if there is still a concern.
Regards,
Sandeep
Divya Gopalpet said
Hello,
Can I hide particular headers in GridViewExportUtil.cs?? I am able to hide the data but not the headers…can u help??
Ramani Sandeep said
Hi divya, To hide the gridview column, i have one post in my blog. you can refer it for hiding column along with data.
Here is the link for it :
http://ramanisandeep.wordpress.com/2009/04/07/how-to-hide-gridview-column-programmatically/
Hope this will help to solve your issue.
Thanks,
Sandeep Ramani
Mike Gledhill said
It’s amazing how many “How do I export from a GridView / DataSet to Excel” articles are out there, and how many of them recommend just sending out a load of values to the “HttpContext.Current.Response”.
Here’s full source code to create *real* Office 2007 files, using the OpenXML libraries.
You just need to include this class in your application, and call one function.
DataSet ds = CreateSampleData();
CreateExcelFile.CreateExcelDocument(myDataSet, “C:\\Sample.xlsx”);
Full source code, and a demo application are provided, free of charge.
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
Good luck !
Medical billing and credentialing said
Medical billing and credentialing…
[...]Export Gridview to Excel « Ramani Sandeep's Blog[...]…
mohamed Elzaki said
It’s very helpful !,
i tried it, but in a grid with a hidden filed it shows an error..
(Control ‘hfXid’ of type ‘HiddenField’ must be placed inside a form tag with runat=server. )
at line( table.RenderControl(htw);)
can you help me please!
Ramani Sandeep said
Can you tell show me the code of gridview and hiddenfield. so that able to find out what might be the issue.
Regards,
Sandeep Ramani
Bhanu Prakash Inturi said
Hi Sandeep,
Code very much helpful to me but I am facing small issue.
I used paging in my grid with another user control. When I applied gridview.allowpaging= false then excelsheet only showing header its eliminating the rows.
When I removed gridview.allowpaging=false then the staring 10 rows are displaying in the ExcelSheet.
Can you please give me any idea regarding this.
Thanks
Bhanu Prakash
Ramani Sandeep said
Please refer the following link. it will help
http://geekswithblogs.net/AzamSharp/archive/2006/01/09/65368.aspx
Thanks,
Sandeep Ramani
Bhanu Prakash Inturi said
Thanks for your early reply my another doubt
Is it possible to export data to Excel from Repeater control
I search in google but I didnt found any such related can you please help on this.
Thanks
Bhanu Prakash
Ramani Sandeep said
Hi , Please refer the following link:
http://stackoverflow.com/questions/438184/export-to-excel-from-a-repeater
This will answer your question.
Hope this will help !!!
Regards,
Sandeep Ramani