Ask a Question related to Macromedia ColdFusion, Design and Development.
-
hupeng #1
string becomes numeric?
Hi,
I am trying to dump a query out into an excel. I found that a varchar
datatype column will be converted to numeric in the Excel. For example, if the
data in that column is "0001", it becomes "1" in the Excel. Since this is an
ID type column, we do not want to lose the "0". Is there a way to prevent
this? Thanks for any advice.
I used the the tag such as
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=Test..xls">
<CFCONTENT TYPE="application/msexcel"><cfoutput>TestColumn</cfoutput>
<cfoutput query="GetTest">#TestColumnData#
</cfoutput>
Helena
hupeng Guest
-
CS3 Numeric Extensions
I need an extension to format php MySQL numeric data to display in currency. I downloaded one that does not work. -
#40274 [NEW]: Sessions fail with numeric string root keys
From: ben at vanilla dot net Operating system: FreeBSD 6.2 PHP version: 5.2.0 PHP Bug Type: *Web Server problem Bug... -
numeric part of a string
Can anyone tell me how can I get the numeric part of a string . for example I'm using this..Select substring('walnut 2224... -
How to sort a String column as Numeric?
I have a datagrid (dgResults) that I am populating from a SQL stored procedure. After I fill my dataset (ds), I cache the dataset this way:... -
Problem converting array string back to numeric
I'm afraid I don't know enough about your situation to help. How are you sending your values to ASP? It sounds to me like this is more like an ASP... -
MikerRoo #2 Re: string becomes numeric?
I haven't tested this in all cases but you can usually force text formatting by
apostrophe-escaping the number.
Change this:
<cfoutput query="GetTest">#TestColumnData#
To this:
<cfoutput query="GetTest">'#TestColumnData#
Cheers,
-- MikeR
MikerRoo Guest
-
hupeng #3 Re: string becomes numeric?
Hi,
Thanks. Adding apostrophe will force that data to be exported as string, but the data will have an apostrophe before it. Is there a trick to remove it in our exporting process.
Helena
hupeng Guest
-
MikerRoo #4 Re: string becomes numeric?
You do not need to remove the apostrophe. In fact, as soon as you do, you
will lose the leading zeros.
In Excel, having the apostrophe isn't really part of the data. It just forces
text formatting.
For example, paste the following into two excel cells:
'003
'004
Now select one of the cells, press Control-C and paste the result into a text
file. You will see that the apostrophe is not included.
Most math formulas still work too. For example setting a cell to =A1+A2 will
still yield the correct value of 7.
Bottom line is: you need that apostrophe to force text formatting. You see it
in the formula bar but it has no other effect. (Text formatting, however,
will break some formulas -- compared to number formatted cells).
-- MikeR
MikerRoo Guest
-
hupeng #5 Re: string becomes numeric?
Thanks for the note. I just tried some experiments.
If I entered '003 into a cell, the apostrophe will not show up as the data but
the cell will have a little green triangle at the upper left corner of the
cell.
However, if I used the codes to export the query output into the excel
spreadsheet, that apostrophe will appear as part of the data. If I copied that
cell into a text file, that apostrophe will be part of the data copied. But if
I highlighted this cell, clicked the data from the top text box (part of the
top menu) (not from the cell), then that apostrophe "got recognized" as a
"string formatting" mechanism, disappeared and the green corner appears.
It seems that the export will not make that apostrophe automatically
recognized as a string formatting mechanism. I could have thousands of lines
of records in the Excel. Is there a batch tool to make it recognize this
apostrophe?
Thanks.
hupeng Guest
-
MikerRoo #6 Re: string becomes numeric?
OK, if you save a well-formed table, you can USUALLY use Miscrosoft's hidden
styles. HOWEVER, this behavior changes from one Excel version to the next and
you may have to use the whole Excel XML schema to get it to work reliably.
Saving your table cells like this:
<td style='mso-number-format:"\@"'>00004</td>
will usually set text formatting -- without the apostrophe.
Good luck,
-- MikeR
MikerRoo Guest
-
hupeng #7 Re: string becomes numeric?
I used the following codes to export the query ouput directly into Excel from
webpage, where should I insert the lines of codes <td
style='mso-number-format:"\@"'> you suggest?
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=Test..xls">
<CFCONTENT TYPE="application/msexcel"><cfoutput>TestColumn</cfoutput>
<cfoutput query="GetTest">#TestColumnData#
</cfoutput>
Thanks a lot for your help.
hupeng Guest
-
MikerRoo #8 Re: string becomes numeric?
Write your output in a table.
Use the attached code as a start.
-- MikeR
<CFHEADER name="Content-Disposition" value="inline; filename=Test..xls">
<CFCONTENT type="application/msexcel">
<CFOUTPUT>
<html>
<head>
</head>
<body>
<table>
<tr>
<th>TestColumn</th>
</tr>
<CFLOOP query="GetTest">
<tr>
<td style='mso-number-format:"\@"'>#TestColumnData#</td>
</tr>
</CFLOOP>
</table>
</body>
</html>
</CFOUTPUT>
MikerRoo Guest
-
hupeng #9 Re: string becomes numeric?
MikeR.
Thanks a lot for the help. It is working great!! This format helps me solve quite a few other problems too. Cheers :)
Helena
hupeng Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
