Sagewire Logo

CFCONTENT to excel

7 Message(s) by 4 Author(s) originally posted in cfml discussion


From: ranger Date:   Thursday, September 13, 2007
I am creating an excel file in cf7 and all works well except when a part number
begins with a 0 - I need the number to always have 5 digits as in 01234 or
12345.

I have tried doing a Numberformat(partnum, '00000') to no avail.

can I force using "TabChar" or something?

noticed the the field in the created excel file won't accept a 0 even type d
in manually.

thanks in advance


From: Adam Cameron Date:   Friday, September 14, 2007
I am creating an excel file in cf7 and all works well except when a part number
begins with a 0 - I need the number to always have 5 digits as in 01234 or
12345.



You need ot tell Excel it's a string , not a numner. Express it as:
'01234

Just like you'd in Excel itself.

--
Adam


From: MEsquivel Date:   Friday, September 14, 2007
This usually happens when Excel "load s" the results (or HTML table ) to the
workbook.

As in every load or data convertion, Excel will try to determine the type of
the value, and the value "01234"'d be considered a number, not a character
value, to the spreadsheet.

If you are building a HTML table to send it to Excel, maybe you could use
something like this in your code :

<td align="left" x:str>#HTMLEditFormat(Numberformat(partnum,'0000'))#</td>

Including the "x:str" code has worked in our code when we export DB results to
Excel.

Best of luck.


From: ranger Date:   Friday, September 14, 2007
adam, MEs,

tnx your reply but as user will be parsing excel file to upload data their
local ap,

needs to be numeric... one thing that kind of worked was to create a new tag

<CFSET TabChar1 = Chr(160)> which throws in a nonbreaking space

in: #TabChar##TabChar1##NumberFormat(partnum, '00000')#

not sure if will parse ok yet. must be a better solution

- any idea how to set col. width in this app?


From: Adam Cameron Date:   Sunday, September 16, 2007
tnx your reply but as user will be parsing excel file to upload data their
local ap,
needs to be numeric...



"01234" isn't a number, it's a string. Numbers do not start with a zero.
Just because your string is comprised of numerals does not make it a number.> <CFSET TabChar1 = Chr(160)> which throws in a nonbreaking space

How is this different from telling Excel it's a string? You're basically
MAKING it a string by adding non-numeric characters, except you're doing it
in a crap way (CF just using the single quote).> not sure if will parse ok yet. must be a better solution

Yes. The one I gave you is a better solution.> - any idea how to set col. width in this app?

You've to understand that <CFCONTENT> does not "make" your data into an
XLS file. All <CFCONTENT> does is set the MIME type in the response from
the server. All it does is say "hey, browser: I know yo're expecting
text/html, but treat THIS data like you'd an XLS file (however that
is)"; it does not *make* it an Excel file: it's all still just text.

If you want to actually make an XLS file (which will enable you to control
presentation-time things like column width), you're going to have to have a
look at the XLS file-format documentation. Which, I believe, is proprietry
to Microsoft, and accordingly probably not published. You might need to
take a different route here, to fulfil your requirements.

--
Adam


From: ranger Date:   Sunday, September 16, 2007
adam,

tnx you reply...

I do understand that the nonbreaking space makes it a string... the issue is
when the excel is parsed for the users tracking software (whatever it might
be)...

figure a space better than an apostrophie (sp) is all. since excel can create
a numeric row with leading zeros, hoped might be a way to send command with
cfcontent.

I put quotes in line here to highlight... not in actual function... what I am
sending is space (can not use CF space as just dispays text) and number.


From: gr8white Date:   Thursday, October 25, 2007
There are styles you can use to control leading zeroes, e.g.
style="mso-number-format:00000;"
for a 5-digit number. There are others involving "@xxxxxxxxxxx"; you can probably find
more info searching google for mso-number-format.

>You need ot tell Excel it's a string, not a numner. Express it as:
'01234

Unfortunately this does not work as it does when you enter it in Excel; in ColdFusion
it actually causes the ' to be displayed.

Ken



Next Message: Multiple datasources on Application.CFC file


Blogs related to CFCONTENT to excel

cfcontent excel session and url variables
A question relating to cfcontent and session variables, with a hint of URL variables. We are migrating from a relatively open system to one that has a login and session management. We have stumbled across something that seems to be ...

cfcontent excel session and url variables
Also check your exception log. Even if an error does make it to the browser it should be logged there. Jaime Metcher > -----Original Message----- > From: Kris Jones [mailto:kris.jones2@verizon.net] > Sent: Saturday, 7 April 2007 6:58 AM ...

cfcontent excel session and url variables
Initially, taking out the <cfcontent> tags did not yield any negative results. The page came back fine to the browser. Putting the tags back on, and looking at the log files more or less confirmed what ...

Generating Excel and PDF Documents in ColdFusion 7
Elena, I just use cfheader like this. excel# eq "yes"> <cfcontent type="application/msexcel"> ...

cfcontent - excel - numbers beginning with 0
I am creating an excel file in cf7 and all works well except when a part number begins with a 0 - i need the number to always have 5 digits as in 01234 or 12345. i have tried doing a Numberformat(partnum, '00000') to no avail. can i ...

Re: cfcontent - excel - numbers beginning with 0
Download the latest ColdFusion 8 utilities including Report Builder, plug-ins for Eclipse and Dreamweaver updates. http;//www.adobe.com/cfusion/entitlement/index.cfm?e=labs%5adobecf8%5Fbeta Archive: ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional