ColdFusion 7 output to Excel Spreadsheet

[etd1968]

I am working in ColdFusion MX7 (sorry). I am outputting database data into a spreadsheet and have run into issues with outputting integers correctly.

In one of the Excel cells I have to interpolate text (counties) and output number codes for each like this (the database field being queried is "County"):

<cfcontent type="application/msexcel">

<cfoutput query="GetSelect" group="number">

<cfif County eq 'Franklin'><cfset CC = '049'>

<cfelseif County eq 'Delaware'><cfset CC= '041' >

<cfelseif County eq 'Union'><cfset CC='159'>

<cfelseif County eq 'Muskingum'><cfset CC='119'>

<cfelse><cfset CC= #County#></cfif>

#CC##tabchar#

</cfoutput>

The problem that Excel output the numbers like 159 or 119 correctly, but if the code number begins with a 0, it drops the zero and outputs 49 or 41 instead of 049 or 041.

Any suggestions on how to achieve this?

-Eric Davis

[Dan_Bracuk]

prepend an apostrophe.

[-__cfSearching__-]

If you are outputting html, rather than a true binary excel file, you could also use the proprietary mso schema's to formatting the value as text.  Just do a search on:     mso-number-format:"\@"

[etd1968]

Thanks for your answer. I think I'm on the right path now.

Here's my latest attempt:

I define the style in the template header (stewart_excel.cfm):

<!--- Define text style to control number output for County Codes --->

<STYLE TYPE="text/css">

                    .number2dec {mso-number-format: Fixed;}

</STYLE>

Then I call this style in the output of the body like this:

<!--- Calculate County Codes --->

<cfif County eq 'Franklin'><cfset CC = 049>

<cfelseif County eq 'Delaware'><cfset CC= 041>

<cfelseif County eq 'Union'><cfset CC= 159>

<cfelseif County eq 'Muskingum'><cfset CC= 119>

<cfelse><cfset CC= #County#></cfif><div class="number2dec">#CC#</div>#tabchar#

The Excel file now outputs the following in the County Code cell:

<div class="number2dec">041</div>

<div class="number2dec">049</div>

The number is now output correctly (with the "0"), but how do I keep the code from showing as well?

Sorry if stupid!

-Eric

[-__cfSearching__-]

#tabchar#

The html code should not be visible. What type of file are you generating? The mso-schemas only work with html. If you are generating delimited output (tab or csv), this approach will not work.

[etd1968]

Yes, I am generating delimited output, and the data is all in <cfoutput> tags:

<cfquery name="GetSelect" datasource="select">

...

</cfquery

<cfsetting enableCFOutputOnly = "Yes">

<!--- Set variables for special characters --->

<cfset tabChar = chr(9)>

<cfset newLine = chr(13) & chr(10)>

<!--- Set the content-type so Excel is invoked --->

<cfcontent type="application/msexcel">

<cfoutput query="GetSelect">

... [lots of other cells]

<!--- Calculate County Codes --->

<cfif County eq 'Franklin'><cfset CC = 049>

<cfelseif County eq 'Delaware'><cfset CC= 041>

<cfelseif County eq 'Union'><cfset CC= 159>

<cfelseif County eq 'Muskingum'><cfset CC= 119>

<cfelse><cfset CC= #County#></cfif>#CC##tabchar##newLine#

... [lots of other cells]

</cfoutput>

So, the output SHOULD accept CF formatting, but it doesn't, i.e., #NumberFormat(County)#. I assume that it's the Excel side that is messing with my datatypes?

Any other suggestions?

[-__cfSearching__-]

So, the output SHOULD accept CF

formatting, but it doesn't, i.e., #NumberFormat(County)#.

No, you are just using the wrong code. If you want to generate a leading zero with NumberFormat(), you need to apply the proper "mask".

I assume that it's the Excel side that is messing with my datatypes?

Well there are no data types in a text file, just strings. Delimited files are versatile, but offer no way to control how Excel will interpet and display the values. When you import the file, Excel will guess how the values should be displayed. However using the import wizard, you can override the defaults and customize the format for each column.  If you require more control that, consider generating a "true" excel file instead -or- an html file that MS Excel can interpret.

Message was edited by: -==cfSearching==-

[insuractive]

In case you are interested in generating a native excel document (e.g. an XLS file), the Apache POI java library is a very common tool used in the CF community to accomplish this.  At least, until <cfspreadsheet> came out in CF 9.  Depending on your version of CF, you might be ineterested in checking it out:

http://poi.apache.org/

[Reed_Powell-ttnmOb]

The POI is a great library. I used to use the approach you are using and always ran into issues with datatypes, formulas, etc. etc. Plus if you are just naming the files XLS when they aren't really XLS binaries, then the newer versions of Excel will give the user a "warning" that the file is not of the correct format, which can be scary for the end user sometimgs.

If you want to make use of the POI library in a painless (ie, don't have to write an java code) way, check out the CFX_EXCEL custom tag.  I think it cost something like $49 and works really well.  Too bad Adobe didn't use it for a model of the CFSPREADSHEET tag, beause for 99% of what most people want to do with a spreadsheet it is a whole lot simpler to use.  I think the company that sells is is Mazreal or something similar. 

[-__cfSearching__-]

If you choose to use POI, you may want to take a look at the POIUtility.cfc . Unless something has changed, it should work right out of the box, even under MX7 (which includes an older version of POI too). 

http://www.bennadel.com/blog/865-POIUtility-cfc-Examples-For-Reading-And-Writing-Excel-Files-In-ColdFusion.htm

[GuyMcMickle]

A simple solution to piping HTML table to Excel:

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_c_11.html

<!--- CFCONTENT Example 5
This example causes the browser to treat the HTML table as Excel data.
Excel interprets the table format.
Because Excel can include executable code, the browser prompts the user whether
to save the file or open it in a browser. --->

<cfheader name="Content-Disposition" value="inline; filename=acmesalesQ1.xls">
<cfcontent type="application/vnd.ms-excel">
<p>Table #1</p>
<table border="2">
<tr><td>Month</td><td>Quantity</td><td>$ Sales</td></tr>
<tr><td>January</td><td>80</td><td >$245</td></tr>
<tr><td>February</td><td>100</td><td>$699</td></tr>
<tr><td>March</td><td>230</td><td >$2036</td></tr>
<tr><td>Total</td><td>=Sum(B2..B4)</td><td>=Sum(C2..C4)</td></tr>
</table>