Exporting data to Excel Part 3

This is the final part of my series on exporting data to Excel. This approach only works with asp.net while the previous two approaches works in ASP classic.

This approach is render the web control directly to excel:

First thing to do is to override the following verification as follows:

' required to prevent error of control not running within "form runat=server" tags
 Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)
     '###this removes the no forms error by overriding the error
 End Sub

Next is to render the control as follows:

        'Export the datagrid to excel
  Private Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
      Response.Clear()
      Response.ContentType = "application/vnd.ms-excel"
      VerifyRenderingInServerForm(DG_Cycle)
      Response.AddHeader("content-disposition", "attachment;filename=Data.xls")
      Response.Charset = ""
      Me.EnableViewState = False
      Dim tw As New System.IO.StringWriter
      Dim hw As New System.Web.UI.HtmlTextWriter(tw)
      DG_Cycle.RenderControl(hw)
      Response.Write(tw.ToString)
      Response.End()
  End Sub

I have left out the aspx codes and the rest of the codes and assume that those portion are already taken care of.

Notice the following:

1. response.clear is done to clear off anything left over in the response buffer.
2. We pass in the data grid to VerifyRenderingInServerForm
3. A string writer is used to render the control.

The advantage of this approach is:

1. We don't have to bother about hiding any other controls. Only the datagrid will be rendered to Excel.
2. Since the datagrid is already bound and contains data, the program need not go back to the database to process the data again.

The disadvantage:

1. This approach only works with asp.net.
2. You will not see other information other than the datagrid.
   

Note that this approach can be used to specify other data types (such as pdf, ppt etc) but I have not had a need to do that.

Links to earlier articles found below:
Part 1
Part 2