Export to excel problem

[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.

 

[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
(e-mail address removed)