Click here to Skip to main content
14,699,107 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 26-Jan-12 2:44am
v2
Comments
Dalek Dave 26-Jan-12 7:44am
   
Edited for Code Block.
loctrice 26-Jan-12 14:12pm
   
I had a problem with excel in the past. I built xml headers and an html table from the dt. Sent it back as text/xml with the string as an attachment with xls extension. Seemed to work well enough

1 solution

   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900