Saturday, February 12, 2011

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, Its pretty straight forward. so I designed the below web part. Just a grid with with few lables 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.Hmmm..

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.



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

12 comments :

  1. Thank You! Fix works great!

    -Carlos

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

    ReplyDelete
    Replies
    1. The above code is for Visual Studio based user control and won't work on SharePoint designer.

      Delete
  3. 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

    ReplyDelete
    Replies
    1. Can you provide me the Visual studio Error message?

      Delete
  4. 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

    ReplyDelete
  5. Thank You! It is working ... great.

    ReplyDelete
  6. 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?

    ReplyDelete
  7. 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?

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

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

    ReplyDelete

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...