formatting numbers as text in excel

问题:

Building an excel spreadsheet is a classic asp application. The problem now is that one of the columns is being automatically formatted by excel as a number when it should really be text and so it is removing leading zeros which the boss wants to keep. I have done some web search and you should be able to pre-append an apostrophe to the string forcing excel to display it as text:

If isNumeric(val) Then
response.write "'" & val
Else
response.write val
End If

Great, but excel is now displaying those columns with a leading apostrophe! From the reading that I have done the leading apostrophe should be hidden and only used by excel as formatting, what am I doing wrong?

Thanks.

回答1:

After a little poking around I found this to work:

Response.Write "=""" & Trim(val) & """"

Displayed correctly and the formatting characters were not displayed, unless of course you click on the cell then the contents is displayed in the header.

回答2:

If you are creating the Excel by using Microsoft Libraries, instead of creating csv file that Excel reads. Then you can basically specify the column as a Text. By doing so you don't have to add string characters into the cell.

回答3:

Add a white space character in HTML   before the value you want to insert in the Excel cell:

If isNumeric(val) Then
    response.write " " & val
Else
    response.write val
End If