DataGrid Export to Excel in ASP.NET

[Srikanth1424]

Hi,

I've a requirement of exporting data to an excel in my application, which is a common feature across all the pages in my ASP.NET application, so I've created a common method in my BLL that takes two parameters dataTable (the one that contains data for my export) and the name of the excel sheet. When I try to export my data to the excel it is not just exporting the data from the DataTable rather it is exporting the whole page with all the images, menus and images. My code in the method is as follows.

Public Sub Export_Excel(ByRef dt As DataTable, ByVal _filename As String)
Try
If Not dt Is Nothing Then
If _filename <> "" Then
Dim tw As StringWriter = New StringWriter()
Dim hw As HtmlTextWriter = New HtmlTextWriter(tw)
Dim dgGrid As DataGrid = New DataGrid()
dgGrid.DataSource = dt
dgGrid.DataBind()
dgGrid.RenderControl(hw)

HttpContext.Current.Response.ClearContents()
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;Filename=" & _filename & "")
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.Write(tw.ToString())
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()
End If
End If
Catch ex As Exception
Throw ex
End Try
End Sub

When I use StreamWriter instead of StringWriter and remove the other below code



HttpContext.Current.Response.ClearContents()
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;Filename=" & _filename & "")
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.Write(tw.ToString())
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()

It is giving the expected result, but it is saving the file on the server somewhere in the root directory. When I try it from the client machine i'm not getting the file in the client machine which is very obvious. I did even try to save the file in the Server as the first step and send it back to the client using the Response.AddHeader(..) and then delete the file from the Server, even then it is giving the same old result with all the images. Please give me some solution it is very urgent.