Designing web pages for Excel Web Queries

  • Thread starter Thread starter Chris Miller
  • Start date Start date
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?
 
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:office: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.
 
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).
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Handling hhmmss time format 6
Stopping Excel from stripping off leading zeros? 2
Using column with numbers and leading zeros 2
Set datatype of a cell? 5
Excel time leading 1st ZERO 3
zero filled changed to non-zero filled 1
Displaying zeros in cells 2
Leading Zeros 2

Back
Top