Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format Excel cells from ASP

Status
Not open for further replies.
grnzbra

grnzbra

Programmer
Mar 12, 2002
1,273
US
I have the following code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment; filename=" & "Address.xls"
%>

The problem is that zipcode columns are automatically formatted as numbers. Is there any way to specify that all the cells are to be formatted as text?
 
Sort by date Sort by votes
What if it looked like an Excel formula that returned a
string?

For example:
[red][tt]= "20002"[/tt][/red]

 
Upvote 0 Downvote
I have no trouble with 20002. The problem is with 02002 which comes out 2002.
 
Upvote 0 Downvote
Right, but what Sheco is pointing out is that you need to format your output to trick Excel into thinking it's a string.
 
Upvote 0 Downvote
I think '02002 is enough to make Excel treat something like text.

So would you write something like:

Code: 
fake_string_variable = "'" & CStr(zipcode)

and then manipulate fake_string_variable?



 
Upvote 0 Downvote
Thanks for the suggestions. The boss decided to reverse engineer it. He created a spreadsheet with two cells, one left alone and one formated as text. Then he entered in both cells a number with a leading zero and saved it as an html file.

We looked at the html with notepad and found the trick.
<html>
<head>
<style>
.txt {mso-number-format:\@;}
<style>
</head>
</html>
<body>
<table>
<tr>
<td>012345</td>
<td class='txt'>012345</td>
</tr>
</table>
</body>
When this is run with the code from my first post, it comes up as a spreadsheet with the leading zero lopped off the first cell but remaining in the second.
 
Upvote 0 Downvote
You *probably* want to look at OWC (Office Web Components).... it lets you do stuff like this:

Code: 
<OBJECT classid="clsid:0002E510-0000-0000-C000-000000000046" height="80%" id="Spreadsheet1" width="100%">
</OBJECT>

<SCRIPT Language=VBScript>


Function Window_OnLoad()

Spreadsheet1.Cells(1,1).Value = "Some string here to put into a cell"

Spreadsheet1.Range("B1:G30").NumberFormat = "00000"



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
278
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
259
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
211
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
507
GriffMG
GriffMG
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
161
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top