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.

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.

publicvoidExportToExcel(){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 (StringWritersw = newStringWriter()){using (HtmlTextWriterhtw = newHtmlTextWriter(sw)){// Create a form to contain the gridTabletable = newTable();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 (GridViewRowrowinGridView.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 (Exceptionex1){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;.

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