Datagrid to Excel: Retain leading zeros?

[Guest]

I need to export a datagrid to Excel.
I could did this. But, also need to keep the leading
zeros in the data.
How can I acheive this?

Any help would be appreciated.

 

[Peter van der Goes]

I need to export a datagrid to Excel.
I could did this. But, also need to keep the leading
zeros in the data.
How can I acheive this?

Any help would be appreciated.

Perhaps the following article and its references will help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317719

It sounds like you are trying to export a field containing numbers with
leading zeros and Excel is seeing it as a numeric field. To keep the leading
zeros, you'll need to add string punctuation.

 

[Guest]

Hi Peter,

Thanks.
Yes, there are integer data.
I am creating the datagrid at runtime and render its data in html.
Please find the code snnipet as below:

response.Charset = ""
response.ContentType = "application/vnd.ms-excel"
Dim stringWrite As New System.IO.StringWriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
Dim dg As New DataGrid
dg.DataSource = <datasource>
dg.DataBind()
dg.RenderControl(htmlWrite)
response.Write(stringWrite.ToString)

Please suggest how to honour the leading zeros.

Thanking you in anticipation,
Regards,
Samik.

 

[Peter van der Goes]

Hi Peter,

Thanks.
Yes, there are integer data.
I am creating the datagrid at runtime and render its data in html.
Please find the code snnipet as below:

response.Charset = ""
response.ContentType = "application/vnd.ms-excel"
Dim stringWrite As New System.IO.StringWriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
Dim dg As New DataGrid
dg.DataSource = <datasource>
dg.DataBind()
dg.RenderControl(htmlWrite)
response.Write(stringWrite.ToString)

Please suggest how to honour the leading zeros.

Thanking you in anticipation,
Regards,
Samik.

As I mentioned, you are going to have to convert the integers to strings if
you expect to retain the leading zeros, probably when the fields are
extracted from the datasource. What is the datasource, and are these numbers
stored as integers in it? I'm having a bit of trouble envisioning an integer
field displayed in a DataGrid with leading zeros.

 

[Guest]

The datasource is either a dataset or a datareader which is populated by a
sqlcommand, calling a stored proc.
Few integer data is shown and I want to add a single quote before each
column value so that they are treated as string by excel.
Can it be done using Regex or any; please suggest.

 

[Jan]

Hi Samik,

What you really need it to apply a number format to all the cells you want
to have leading zeros. If you convert all numbers to strings, you will get
Excel "Number stored as text" warnings and that numbers will be treated as
text (and ignored in formulas).

You have few third-party components that can generate native XLS (together
with number format). If amount of data you are exporting is not large (less
than 5 sheets and 150 rows per sheet), you have our ExcelLite Free component
you can freely use in commercial apps. If your worksheet grows in size, you
can easily update to ExcelLite Professional. Check
http://www.gemboxsoftware.com for more information.

Jan