Leading Zeros truncated when exporting from asp page to excel

[leninv]

Hi,

I have the following code where 'recs' is a record set.

For i=0 to recs.Fields.Count - 1
if i = 0 then
pindnt = string(itmlvl*2," ")
response.write pindnt & recs(i)
else
Response.write recs(i)
end if

Response.write CHR(09)
Next

Using this part of code in my application I am exporting data from oracle database to excel, but leading zeros for one column "CURRENT_REVISION" are truncated in the excel sheet.

I am able to see the leading zeros only if I add a string(like ' - single quotes). Please see the below code where I am adding a string.But I don't want any string to the CURRENT_REVISION value in the excel sheet.

For i=0 to recs.Fields.Count - 1
if i = 0 then
pindnt = string(itmlvl*2," ")
response.write pindnt & recs(i)
else
if recs.Fields(i).Name = "CURRENT_REVISION" then
Response.Write "'" & recs(i) ' this is where I concatinated a string
else
Response.write recs(i)
end if
end if
Response.write CHR(09)
Next


My code does not contain any HTML or Javascript code. Using the below code I am exporting to Excel

<%Response.buffer = True
Server.ScriptTimeout = 1800
Response.ContentType ="application/x-msexcel"%>

This seems to be very small issue but eating a lot of my time.
Can anyone help me to resolve this issue.

[improvcornartist]

Excel is probably considering the fields to be numbers, so it removes the leading zero. When you add an extra mark, it then makes the field text, so the zero stays. You could possibly use a decimal at the end of the number (if the field has two decimals, it will be read as text). You may also be able to create your data as a CSV instead of MSExcel, then format the spreadsheet when it is opened in Excel so it will display the version data as text. Otherwise, I'm not really sure how to get around the issue.

[leninv]

Excel is probably considering the fields to be numbers, so it removes the leading zero. When you add an extra mark, it then makes the field text, so the zero stays. You could possibly use a decimal at the end of the number (if the field has two decimals, it will be read as text). You may also be able to create your data as a CSV instead of MSExcel, then format the spreadsheet when it is opened in Excel so it will display the version data as text. Otherwise, I'm not really sure how to get around the issue.

Thank you for the reply. I tried using decimal at the end of the number, but that doesn't work. If I am the only user then I can format the spreadsheet by using CSV, but this will be opened by many users, so I cannot tell every one to format the spreadsheet, so I cannot use CSV instead of MSExcel. I am trying for some alternative using reverse engineering approach. Any how I would like to thank you once again. Any suggestion is always welcome.

[leninv]

Thank you for the reply. I tried using decimal at the end of the number, but that doesn't work. If I am the only user then I can format the spreadsheet by using CSV, but this will be opened by many users, so I cannot tell every one to format the spreadsheet, so I cannot use CSV instead of MSExcel. I am trying for some alternative using reverse engineering approach. Any how I would like to thank you once again. Any suggestion is always welcome.

Hi

I am able to export the content to Excel with Leading zeros now using reverse engineering approach, but I am facing another probelm. The result is displayed on a blank page, I am not getting any cells displayed. I mean no spreadsheet is seen. I used the following statements for Excel

<%Response.buffer = True
Server.ScriptTimeout = 1800
Response.ContentType ="application/vnd.ms-excel"%>

Can you help me to sort out why this problem is coming.

[jhardman]

I don't know how you got it to work, but the correct way to format numbers as text in excel is to prepend an apostrophe (single quote mark ') to the beginning of the number. Otherwise Excel will always truncate leading zeroes.

Jared

[leninv]

I don't know how you got it to work, but the correct way to format numbers as text in excel is to prepend an apostrophe (single quote mark ') to the beginning of the number. Otherwise Excel will always truncate leading zeroes.

Jared

I tried this but the problem with this is, If I prepend a apostrophe to a value 003 it is showing the result '003. So apostrophe is seen in my result, which I should not get.

[sorpigal]

This one bit me recently and, finding no solution via Google, I developed my own. I thought I'd share my answer in case it's useful to others.

On columns which are known to be strings that will (or can) contain leading zeroes, do this:

string celltext = "08010203";
celltext = "=concatenate(\"" + celltext + "\")";

Since excel treats the result of a concatenation as a string, even if there was only one element in the concat list, this works. The result appears in excel with the leading zero and with no quotation marks. The obvious disadvantage is the increased number of bytes necessary to represent the data.

[shankrayya]

sorpigal : this works awesome .... i did it like this : I am working in ASP 1.1

drAddItem(2) = "=concatenate(""" + dtOriginal.Rows(i)(1).ToString() + """)"
or string celltext = "08010203";
celltext = "=concatenate(\"" + celltext + "\")";