Skip navigation
etd1968
Currently Being Moderated

ColdFusion 7 output to Excel Spreadsheet

Nov 6, 2011 5:36 AM

Tags: #output #coldfusion #mx7 #spreadsheet

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

 
Replies
  • Currently Being Moderated
    Nov 6, 2011 5:49 AM   in reply to etd1968

    prepend an apostrophe.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 6, 2011 12:09 PM   in reply to etd1968

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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 7, 2011 9:49 AM   in reply to etd1968

    #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.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 8, 2011 9:34 AM   in reply to etd1968

    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==-

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 10, 2011 11:58 AM   in reply to -==cfSearching==-

    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/

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 10, 2011 1:01 PM   in reply to insuractive

    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. 

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 10, 2011 1:47 PM   in reply to Reed Powell

    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-A nd-Writing-Excel-Files-In-ColdFusion.htm

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 9, 2012 3:24 PM   in reply to etd1968

    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>

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (1)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points