Lanka Srikanth

Lanka Srikanth

  • NA
  • 3
  • 2.4k

DataGrid Export to Excel in ASP.NET

Jan 26 2012 7:25 AM
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.

Answers (2)