Skip to main content
E
etd1968
Participant
November 6, 2011
Question

ColdFusion 7 output to Excel Spreadsheet

  • November 6, 2011
  • 3 replies
  • 6623 views

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

This topic has been closed for replies.

3 replies

G
GuyMcMickle
Inspiring
January 9, 2012

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>

-
-__cfSearching__-
Inspiring
November 6, 2011

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:"\@"

E
etd1968Author
Participant
November 7, 2011

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

E
etd1968Author
Participant
November 8, 2011

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?

D
Dan_Bracuk
Inspiring
November 6, 2011

prepend an apostrophe.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices