Problem exporting datagrid with numeric values to Excel

[Guest]

I am using the following code to write data from a datagrid to an Excel file:
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
dg.RenderControl(hw)
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Response.AppendHeader("content-disposition",
"attachment;filename=test.xls")
Response.Write(txtWriter.ToString)
Response.End()
So far, so good. Creates an Excel spreadsheet very nicely. The problem
occurs when a column contains what appears to be numeric data. Leading
zeroes are being dropped. If the number is too large, the value is being
stored in scientific notation format. Is there any way to get my values to
download to Excel as text? These fields are coming from a SQL database where
they are stored as varchar. Thanks for any help.

 

[Elton Wang]

Hi Nancy,

I think it's an Excel bug. Because all are digits
(including leading 0), it automatically recognizes it as
number. In Excel automation, we can set text format for
specific column first then fill this kind of data. That
works perfectly. But for the datagrid exporting I can't
think any solution.

HTH

Elton Wang
(e-mail address removed)