Export to Excel – From SharePoint user control based web part
My Requirement is to design a web part while pulls the data from SQL Server and display over the grid based on logged-in user.
Yes, It’s pretty straight forward. So I designed the below web part. Just a grid with few labels and drop-downs.
Now, the end users require Export to excel functionality. So, I placed a button at the bottom of the web part and wrote the code for exporting from Data grid to excel.
public void ExportToExcel()
{
try{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename=Charge Report.xls"));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the gridTable table = new Table();
table.GridLines = GridView.GridLines;
// add the header row to the tableif (GridView.HeaderRow != null)
{
table.Rows.Add(GridView.HeaderRow);
}
// add each of the data rows to the tableforeach (GridViewRow row in GridView.Rows)
{
table.Rows.Add(row);
}
// add the footer row to the tableif (GridView.FooterRow != null)
{
table.Rows.Add(GridView.FooterRow);
}
// render the table into the htmlwritertable.RenderControl(htw);
// render the htmlwriter into the responseHttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
catch (Exception ex1)
{
System.Diagnostics.EventLog.WriteEntry("TeamApps Charge Report", ex1.Message);
}
}
Yeah, it worked well, But the problem is: the export to excel works ONLY for the first time. When I tried clicking on the Export to Excel button again, Nothing happens. Hmm..
After spending long time, I was able to figure out the issue and solution! All I’ve done is: added the OnClientClick to the button’s event, and called the _spFormOnSubmitCalled = false;_spSuppressFormOnSubmitWrapper=true;.
<asp:Button ID="btnExportToExcel" runat="server" Text="Export to Excel" onclick="btnExportToExcel_Click" OnClientClick="_spFormOnSubmitCalled = false;_spSuppressFormOnSubmitWrapper=true;" />
This is because of the security settings of SharePoint. After placing this code every thing worked well.
New to asp.net..
Will it work for Grid view with paging??
GridView Subtotals not showing in Excel.
In excel showing Blank Row. How to set it.
it doesn’t work in sandbox webpart, throws an object ref not set to an instance of an object on HttpContext.Current.Response.End(), any idea why?
This is due to the sandbox solution limitation! I believe only Farm solutions can do this due to Namespace restriction in sandboxed solution.
I want this exact functionality for sharePoint chart web part.
i want export chart to excel. how can i modify this user control code to achieve that?
Thank You! It is working … great.
I am getting this error Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.
Details: Error parsing near table border=”0″> at the HttpContext.Current.Response.End() line
Thank You so much man
Hi..i tried the above code in sandbox solutions.
HttpContext.Current.Response.End(); is throwing an exception and download doesn happen..any ideas??
very urgent
Can you provide me the Visual studio Error message?
Can you tell me where you put the code for this button? I am adding the ASP.net button in SharePoint Designer.
The above code is for Visual Studio based user control and won’t work on SharePoint designer.
Thank You! Fix works great!
-Carlos