Please help with Data Grid export problem

[Hammad Mansoor]

I am using the following code to export Data Grid to excel. It works fine, except that it misses the leading '0's in the first column. For example, I have data in first column like '0034566'. When it exports to excel, the data is shown as '34566'. I have tried many things but it's still not working. Please help. Imports System.Data Imports System.IO Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Response.ContentType = "application/vnd.ms-excel" Response.AddHeader("content-disposition", "attachment;filename=SearchDownload.xls") Response.Charset = "" Me.EnableViewState = False GridView1.AllowPaging = False Dim dtSupplier As DataTable = DirectCast(ViewState("dtSupplier"), DataTable) GridView1.DataSource = dtSupplier GridView1.DataBind() Dim stringWrite As New System.IO.StringWriter() Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite) Dim StyleAsText As String = "<style> .StyleAsText{ mso-number-format:\@;} </style>" GridView1.RenderControl(htmlWrite) 'Response.Write(strStyle) 'Response.Write(stringWrite.ToString()) Response.Write(StyleAsText + stringWrite.ToString()) Response.End() End Sub Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control) End Sub End Class

[Abhijit Jana]

I think, it because of you are exporting that field as number format.

cheers, Abhijit CodeProject MVP Web Site:abhijitjana.net View My Recent Article

[Hammad Mansoor]

Ok I have changed my code. I think it should work, But it isn't. Any suggestions? Please check my code below. Imports System.Data Imports System.IO Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Dim style As String = "<style> .text { mso-number-format:\@; } </style> " Response.ClearContent() Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls") Response.ContentType = "application/excel" GridView1.AllowPaging = False Dim sw As New StringWriter() Dim htw As New HtmlTextWriter(sw) GridView1.DataBind() GridView1.RenderControl(htw) 'Style is added dynamically Response.Write(style) Response.Write(sw.ToString()) Response.End() End Sub Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control) End Sub Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then e.Row.Cells(0).Attributes.Add("class", "text") End If End Sub End Class

[kumar_k508]

Hi, CHeck the below code... Response.Clear() Response.Charset = "" 'set the response mime type for excel Response.ContentType = "application/vnd.ms-excel" Response.Charset = "iso-8859-1" 'create a string writer Dim stringWrite As New System.IO.StringWriter 'create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite) dg.DataSource = ds 'bind the datagrid dg.DataBind() 'tell the datagrid to render itself to our htmltextwriter dg.RenderControl(htmlWrite) Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>" **Response.Write(strStyle + stringWrite.ToString) Response.AddHeader("Content-Disposition", "attachment")** Response.End() Protected Sub dg_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles dg.RowDataBound If e.Row.RowType = DataControlRowType.DataRow Then e.Row.Cells(1).Attributes.Add("class", "text") End If End Sub and just change the code in response.write line according to above... Cheers:rose:

S Kumar

modified on Thursday, July 9, 2009 4:35 AM

[Hammad Mansoor]

Thanks a lot Kumar. It worked like a magic :) Thanks once again. Hammad