[thelist] TIP - Getting leading zeroes into XL output from ASP

[Luther, Ron]

Hi Gang,


Just had to solve this - so I thought I'd share.  [More elegant solutions
are certainly welcome!]

Da Problem:
A client has a static HTML page with a link to a file that should come up in
Excel.  Our IM folks built an ASP program that generates said file in a CSV
format.

Unfortunately, we have a alpha field that looks like a numeric with leading
zeroes that are getting cropped when Excel opens the file.  [Lord - save me
from "smart" applications!]


Da Easy Way Out:
We could write the file out as a ".dat" to trigger the 'import wizard' in
Excel.  Unfortunatly this also triggers an ugly warning message and requires
some user training and patience ... "just make it work in Excel".    ;-)


An Answer:
I searched MSDN, Google, etc. and didn't find exactly what I was looking for
- but did find a good starting point:
http://www.15seconds.com/Issue/970515.htm


<tip type="Leading Zeroes in Excel from ASP" author="RonL">
Fortunately there IS an Excel function for formatting numbers as text, so
this is what I came up with:

(please excuse the ugly 'back of a napkin' feel ... this was a "quick and
dirty")

<%
Response.ContentType = "application/vnd.ms-excel"
%>
<TABLE>
<TR><TD><!-- Cell : A1 -->Plant Name</TD>
<TD>Plant Code</TD>
<TD>Data</TD>
</TR>
<TR>
<TD><!-- Cell : A2 -->Lemuria</TD><TD>=text(0,"0040")</TD><td>$500.12</td>
</TR>
<tr>
<td>Atlantis</td><td>=text(0,"0078")</td><td>$200.78</td></tr>
<tr><td>TOTAL</td><td>&nbsp;</td><td>=sum(C2:C3)</td>
</TABLE>


When named "whatever.asp" it brings up a popup (open here or save to disk)
... but I'm just providing this to IM as a "proof of concept" so they can
swizzle it into the program that generates the 'real file'.

[At least that's what it does using IE5.5 on a win2k box, goodness knows
what happens on 'nix or mac ... probably saves to file to be opened with
StarOffice?]

Gee, if I had time to track down more formatting issues, bold, background
cell colors, fonts, etc. .... this might be worth an article!

</tip>


Hope That Helps Somebody Else,


RonL.

(Just three more impossible things to do ... and then lunch at Milliways!)