Export to excel problem

25 Apr 2005 8:23 AM

Kelvin

Hi All,

I don't know why my program exports to excel which can't convert cell
format ?
for example, I am using the following code:
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;
filename=OutstandingOrderDetailReport.xls");

"OrderNo" field should be 00001234, but it can display 1234 in excel
file.

DataFormatString defined the following:
<asp:boundcolumn headertext="OrderNo" datafield="OrderNo"
DataFormatString="{0:0000000}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundColumn>

Please advised.

25 Apr 2005 8:47 PM

Elton W

Hi Kelvin,

Actually I think it may be an Excel bug. When digital
data, e.g. 00001234, is filled in a excel column, it
automatically recognizes it as number, so trims its
leading zero(s).

If you use Excel automation COM object to deal with this
kind of data, you can format the column as TEXT first,
then fill up data. That will keep leading zero(s).

However, I haven't found any way to figure it out in
exporting datagrid to excel in ASP.NET.

HTH

Elton Wang
elton_w***@hotmail.com

>-----Original Message-----
>Hi All,
>
>I don't know why my program exports to excel which can't
convert cell
>format ?
>for example, I am using the following code:
>Response.ContentType = "application/vnd.ms-excel";
>Response.AddHeader("Content-Disposition", "attachment;
>filename=OutstandingOrderDetailReport.xls");
>
>"OrderNo" field should be 00001234, but it can display

1234 in excel
Show quoteHide quote

>file.
>
>DataFormatString defined the following:
><asp:boundcolumn headertext="OrderNo" datafield="OrderNo"
>DataFormatString="{0:0000000}">
><ItemStyle HorizontalAlign="Right"></ItemStyle>
></asp:BoundColumn>
>
>Please advised.
>.
>

27 Apr 2005 2:21 AM

Alvin Bruney [MVP - ASP.NET]

one workaround is to append a quote before the string like so '00002034234.
The downside is that you would have to write iteration code to do that which
can be a performance drain

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc

Show quoteHide quote

"Kelvin" <kelvin***@gmail.com> wrote in message
news:db7965c.0504250023.31874121@posting.google.com...
> Hi All,
>
> I don't know why my program exports to excel which can't convert cell
> format ?
> for example, I am using the following code:
> Response.ContentType = "application/vnd.ms-excel";
> Response.AddHeader("Content-Disposition", "attachment;
> filename=OutstandingOrderDetailReport.xls");
>
> "OrderNo" field should be 00001234, but it can display 1234 in excel
> file.
>
> DataFormatString defined the following:
> <asp:boundcolumn headertext="OrderNo" datafield="OrderNo"
> DataFormatString="{0:0000000}">
> <ItemStyle HorizontalAlign="Right"></ItemStyle>
> </asp:BoundColumn>
>
> Please advised.