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.

sharepoint user control export to excel

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.

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

13 thoughts on “Export to Excel – From SharePoint user control based web part

  • October 30, 2017 at 9:53 AM

    New to asp.net..
    Will it work for Grid view with paging??

    Reply
  • April 3, 2015 at 11:09 AM

    GridView Subtotals not showing in Excel.
    In excel showing Blank Row. How to set it.

    Reply
  • April 8, 2013 at 4:42 PM

    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?

    Reply
    • April 9, 2013 at 8:38 AM

      This is due to the sandbox solution limitation! I believe only Farm solutions can do this due to Namespace restriction in sandboxed solution.

      Reply
  • February 6, 2013 at 12:44 PM

    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?

    Reply
  • August 7, 2012 at 1:49 PM

    Thank You! It is working … great.

    Reply
  • June 18, 2012 at 10:27 PM

    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

    Reply
  • May 15, 2012 at 8:54 PM

    Thank You so much man

    Reply
  • March 20, 2012 at 1:18 PM

    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

    Reply
    • March 20, 2012 at 2:31 PM

      Can you provide me the Visual studio Error message?

      Reply
  • February 17, 2012 at 10:08 PM

    Can you tell me where you put the code for this button? I am adding the ASP.net button in SharePoint Designer.

    Reply
    • February 18, 2012 at 7:35 AM

      The above code is for Visual Studio based user control and won’t work on SharePoint designer.

      Reply
  • February 13, 2012 at 4:07 PM

    Thank You! Fix works great!

    -Carlos

    Reply

Leave a Reply