Designing web pages for Excel Web Queries

C

Chris Miller

I'm running a website with ColdFusion MX and I've got
account numbers on reports with leading zeros. When I
export spreadsheets from Coldfusion the leading zeros are
stripped out. Is there any html formating that would tell
Excel to keep the leading zeros?
 
J

Jake Marx

Hi Chris,

You can learn a lot from saving an Excel workbook or sheet as an HTML
document. Excel puts all sorts of XML attributes in the HTML tags that tell
Excel what to do when opening them.

This HTML source will produce a numeric value in cell A1 = 1 and a string in
cell A2 = 0001.


<html xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<title>Test</title>
</head>

<body>

<table>
<tr>
<td height=17 width=64 x:num>001</td>
</tr>
<tr>
<td x:str="'00001">00001</td>
</tr>
</table>

</body
</html>


So it looks like you can use x:str to specify that Excel should treat the
cell as a string. If you give that attribute a value, I think Excel will
use that value instead of what's displayed in the HTML output. In this
case, I put a single quote in front of the string so Excel won't reevaluate
the string as a number when you edit it.
 
J

Jake Marx

Chris,

Do the XML schema-related statements appear in the HTML tag? Because the
HTML code below opens perfectly for me in Excel (Win XP, Excel 2002).
 
C

Chris Miller

Jake,
Even with the schema statements it doesn't see the cell as
text. Excel 2000. That extra 2 makes all the difference
apparently. I might just do this as an Excel macro
instead. Thanks for your help.
 
Top