Blog

22Jun
2009
More On ColdFusion And Spreadsheets

Last week I posted an entry on the spreadsheet support planned for ColdFusion Centaur. That post (unsurprisingly) generated lots of questions and comments, and so I thought it worthwhile to post a bigger and more sophisticated example, this time building a new spreadsheet from scratch.

We'll start with a simple Application.cfc to define the datasource needed for this app:

view plain print about
1<cfcomponent>
2    <cfset this.datasource="cfartgallery">
3</cfcomponent>

Now to create a spreadsheet using a database query:

view plain print about
1<!--- Get data --->
2<cfquery name="ArtOrders">
3SELECT orderid, customerfirstname, customerlastname, total
4FROM orders
5ORDER BY orderid
6</cfquery>
7
8<!--- Save it --->
9<cfspreadsheet action="write"
10    query="ArtOrders"
11    filename="Orders.xls"
12    overwrite="true">

That's all it takes. <cfspreadsheet> creates a spreadsheet populated with the retrieved data, using the query column names as column headers in the first spreadsheet row, and the data starting in the second row. Nice, huh?

But what if you want more control? What if you want to explicitly control data placement? What if you needed to provide row or column or cell level formatting? And what if you needed a total cell containing a formula to SUM the total column?

Here's a complete example:

view plain print about
1<!--- Get data --->
2<cfquery name="ArtOrders">
3SELECT orderid, customerfirstname, customerlastname, total
4FROM orders
5ORDER BY orderid
6</cfquery>
7
8<!--- Create new spreadsheet --->
9<cfset sObj=SpreadsheetNew()>
10
11<!--- Create header row --->
12<cfset SpreadsheetAddRow(sObj, "Order,First Name,Last Name,Amount")>
13<cfset SpreadsheetFormatRow(sObj, {bold=TRUE, alignment="center"}, 1)>
14
15<!--- Add orders from query --->
16<cfset SpreadsheetAddRows(sObj, ArtOrders)>
17
18<!--- Figure out row for formula, 2 after data --->
19<cfset rowDataStart=2>
20<cfset rowDataEnd=ArtOrders.recordCount+1>
21<cfset rowTotal=rowDataEnd+2>
22<cfset totalFormula="SUM(D#rowDataStart#:D#rowDataEnd#)">
23
24<!--- Add total formula --->
25<cfset SpreadsheetSetCellValue(sObj, "TOTAL:", rowTotal, 3)>
26<cfset spreadsheetSetCellFormula(sObj, totalFormula, rowTotal, 4)>
27
28<!--- Format amount column as currency --->    
29<cfset SpreadsheetFormatColumn(sObj, {dataformat="$00000.00"}, 4)>
30
31<!--- Save it --->
32<cfspreadsheet action="write"
33    name="sObj"
34    filename="Orders.xls"
35    overwrite="true">

Once again, we start with a database query. Then, SpreadsheetNew() is used to create a new spreadsheet object.

The code then creates the header row. SpreadsheetAddRow() is used to add a specific row, the column headers (as no row number was specified, SpreadsheetAddRow() adds the row to the next free row, the first). SpreadsheetFormatObject() is then used to format row 1, this function accepts a structure (which here is implicitly created inline).

Next comes the data. SpreadsheetAddRows() adds the entire query to the spreadsheet (again, as no row was specified the data gets added to the next free row).

Now for the formula which will total all orders. So as to not hard code the row, a few <cfset> statements are used to calculate the first and last data row, and the row for the total (2nd row after the end of the data, so leaving one empty row in between data and total). The formula too can't be hard coded, so instead of SUM(D2:D24), the row variables are used to build the formula string dynamically. SpreadsheetSetCellValue() is used to add a title, and SpreadsheetSetCellFormula() inserts the formula.

Next, the entire fourth column, containing the order amounts and the calculated total, is formatted to display as currency using SpreadsheetFormatColumn().

All that is left to do then is to save the file using <cfspreadsheet>.

<cfspreadsheet> and its 30+ supporting functions can do lots more, but this should give you a taste of just what's possible using this innovative new feature.

Related Blog Entries

Comments (36)



  • Mark

    Oh my! One of my least favorite tasks made dead simple. Thank you adobe!

    #1Posted by Mark | Jun 22, 2009, 08:16 PM
  • Evagoras Charalambous

    What about "xlsx" (Excel 2007) files? Can it handle those?

  • Evagoras Charalambous

    I guess I should have read the previous post first...where Ben answered the same question. :o)

  • Doug Cain

    Will we be able to set cells to use 16bit so UTF-8 characters work in spreadsheets?

    #4Posted by Doug Cain | Jun 23, 2009, 07:25 AM
  • Eric Belair

    Ben... In your example above, the order of the query columns is different from the order of the column headers you specify: "SELECT orderid, customerfirstname, customerlastname, total" vs. SpreadsheetAddRow(sObj, "Order,Last Name,First Name,Amount"). How does CF know where to put the data?

  • Ben Forta

    Eric, the columns (at least in all of my examples) are in the order specified in the SQL statement. And yep, my headers are switched, I'll fix the code.

    Oh, I also added a line to the code, a SpreadsheetFormatColumn() call to format the amounts column as currency.

    --- Ben

    #6Posted by Ben Forta | Jun 23, 2009, 09:13 AM
  • Eric Belair

    @Ben. Ok. I wasn't sure if I missed something. It makes perfect sense now. Looking forward to using this in the future.

  • Cory McHugh

    I am assuming (based on other CF excel stuff in 8 and 7) that this is based on POI. Is that correct, and if so, is there any word on which version of POI it will ship with? This is one of many great reasons to move to 9 lickety-split.

    P.S.
    MORE BOLT INFO! :D

    #8Posted by Cory McHugh | Jun 23, 2009, 03:28 PM
  • Ben Forta

    Cory, we use some POI, some Open Office APIs, and some of our own stuff, to make this all work.

    --- Ben

    #9Posted by Ben Forta | Jun 23, 2009, 04:28 PM
  • Josh Knutson

    When using POI I was able to make sure that when in excel and printed it would print in landscape mode and fit to page was turned on, along with setting some headers and footers. Will I have to modify the underlying JAVA, or will something be more built in.

  • eddie

    What about charts? Is it possible to create charts in
    excel through coldfusion?

    #11Posted by eddie | Jun 24, 2009, 02:44 PM
  • Bryn

    Does it support both Workbooks and contained spreadsheets ?

    Is it possible to do presentation type stuff, such as Graphing in the spreadsheet ?

    Can an existing SS be opened and the contents of specified SS cell values read and edited ?

    #12Posted by Bryn | Jun 25, 2009, 04:37 AM
  • Ben Forta

    Josh, there are indeed functions to manage print settings, but have not looked to see exactly what is supported and what not.

    Eddie, no, creating new charts is not supported yet. But updating values that existing charts are dependent on works.

    Bryn, sheets are supported, charts are not, and as for updates, sure, see the examples in the first post.

    --- Ben

    #13Posted by Ben Forta | Jun 25, 2009, 07:55 AM
  • TikiHowpu

    Hi Ben,

    I've tested some complex Excel reports and the <cfspreadsheet action="read" > worked great but is it also possible to extract images from a worksheet?

    #14Posted by TikiHowpu | Jul 14, 2009, 06:13 AM
  • Vinu

    @eddie: Its possible to insert a chart into a spreadsheet.
    1. Use cfchart to generate the chart. Insert the chart using spreadsheetaddimage:

    Example:

       <cfchart format="png" scalefrom="-100" scaleTo="100" gridlines="5" name="test">
       <cfchartseries type="line">
       <cfchartdata item="Point1" value="-50">
       <cfchartdata item="Point2" value="-25">
       <cfchartdata item="Point3" value="1">
       <cfchartdata item="Point4" value="25">
       <cfchartdata item="Point5" value="50">
       <cfchartdata item="Point6" value="75">
       <cfchartdata item="Point7" value="99">
       </cfchartseries>
       </cfchart>
       <cfset spreadsheet=spreadsheetNew()>
       <cfset spreadsheetAddImage(spreadsheet,test,"png","5,5,15,15")>

       <cfspreadsheet action="write" filename="#dirname#SingleSheet1.xls" name="excelVar" sheetname="chart" overwrite="true">

    #15Posted by Vinu | Jul 16, 2009, 12:38 AM
  • Vinu

    @TikiHowpu
    No its is currently not possible to extract images through coldfusion. Is there an use case you will have? We can raise an ER for this.

    - Vinu

    #16Posted by Vinu | Jul 16, 2009, 12:40 AM
  • eddie

    Vinu - Thanks. I will play with that and see what happens. I appreciate the sharing!

    Eddie

    #17Posted by eddie | Jul 16, 2009, 07:47 AM
  • TikiHowpu

    @Vinu

    We have a client which stores vessel information in an Excel report. (For example: information like the paint / coating condition of a vessel).

    The ''photo worksheet" includes several photos of the condition of the vessel (For example: a photo before a treatment and after a treatment). After the client is done, they upload the report in a webapplication.

    TikiHopwu

    #18Posted by TikiHowpu | Jul 20, 2009, 06:58 AM
  • Neil Pandeya

    Hi Ben, Excellent article! Very informative.
    I tried using the Excel functionality of CF9. It works great with formulas too. However I couldn't make the excel financial formulas (like IRR and NPV etc.) work with it. Is that me or are those formulas not supported yet. Do you have any information on that?
    Cheers!

    #19Posted by Neil Pandeya | Nov 3, 2009, 08:42 PM
  • Neil Pandeya

    Let me be more specific about my problem. Using CF9, I'm "writting" to excel sheet some data in C1 to C14 and formula to calculate say IRR in C15. The data and calculated IRR show accurately in ecxel sheet when I open to view the Excel sheet. However if I "read" back the excel sheet column where I calculated IRR, the value as well as formula are read back as IRR(C1:C14). I wonder if there is any trick which I'm missing??? However if I use math functions like SUM etc. it works fine and I get the value when I read the column for its value. Can any one help please?

    #20Posted by Neil Pandeya | Nov 4, 2009, 01:30 PM
  • marker

    Hi Ben

    Here is a dump of my query:

    CACHED: false
    EXECUTIONTIME: 16
    SQL: SELECT P0FIELD2,P1FIELD5,P2FIELD3,P3FIELD1
    FROM
    dataQuery
    RESULTSET:

       [Record # 1]
       P0FIELD2: {ts '2009-11-08 13:32:27'}
       P1FIELD5: 1
       P2FIELD3: 2
       P3FIELD1: STATUS2

       [Record # 2]
       P0FIELD2: {ts '2009-11-06 13:32:27'}
       P1FIELD5: 1
       P2FIELD3: 0
       P3FIELD1: STATUS0

       [Record # 3]
       P0FIELD2: {ts '2009-11-07 13:32:27'}
       P1FIELD5: 1
       P2FIELD3: 1
       P3FIELD1: STATUS1

    This how it looks in the Excel:

    2   true   STATUS2   {ts '2009-11-08 13:32:27'}
    0   true   STATUS0   {ts '2009-11-06 13:32:27'}
    1   true   STATUS1   {ts '2009-11-07 13:32:27'}
    3   true   STATUS3   {ts '2009-11-09 13:32:27'}

    Which is pretty messed up!
    What is wrong here?

    #21Posted by marker | Nov 6, 2009, 07:52 AM
  • marker

    OK, figured it out.
    I was getting a Array from Flex. I use an ArrayToQuery function and then pass the Query to the SpreadsheetAddRow() function. In that case, CF doesnt care at all about how the query looks, it just does something.... bad bad bad!
    So, I send some MetaData from Flex now, with a List which has the right order of fields to use in a QueryOfQuery Statement. So, I now QueryOfQuery my Object sent from Flex and then pass it to the SpreadsheetAddRow() function. Finallly it works correct this way.
    I consider this a bug, because why should CF care about a SQL statement?? Why not just use the QueryObject I pass in the correct way?

    #22Posted by marker | Nov 6, 2009, 08:28 AM
  • Kunal Saini

    @ Neil
    I have logged the issue 80615. This looks like a bug. We will investigate more on this.

    Thanks
    Kunal Saini
    Adobe ColdFusion Team

    #23Posted by Kunal Saini | Nov 9, 2009, 01:19 AM
  • Alan

    Kunal,

    I believe I have the same question/issue as Neil.

    I am reading an existing Excel SS and would like fields containing formulas to be read as VALUES. So instead of "SUM(A1:A20)" I would just like to see "394.22" or whatever the sum is.

    I am using SpreadSheetGetCellValue() to retrieve the cell but am still getting the formula.

    Help is very much appreciated!

    #24Posted by Alan | Jan 5, 2010, 12:08 AM
  • Alan

    I may have found the cause of my issue:

    It seems that ColdFusion cannot process external links to other Excel sheets. In these cases, CF9 just returns the cell's formula, not the value.

    I turned off the "Update Links" prompt in Excel and that didn't help.

    Can anyone address this?

    #25Posted by Alan | Jan 7, 2010, 04:08 PM
  • Brian H.

    Hey Alan.

    I am having similar issues, though SpreadsheetGetCellValue() does actually return the value when a formula references another cell, but it barfs when the cell (or the cell that the cell references) contains some specific functions.

    For instance, it had no problem evaluating a cell which had a formula "=E18*Assumptions!$D$19" which is a ref to another cell in another sheet. However, if a cell contains any of the following (non-exhustive) list of functions, it will return the function and not the value (this includes any cells that reference cells containing these functions): "NPV(), IIR()". So what I am going to try to do is replace the functions in those cells with something else that IS compatible.

    To reiterate, if I call upon spreadsheetGetCellValue() to evaluate a cell containing =IRR(E34:H34,500%) it will fail (and return the function), yet if i change that function to =SUM(E34:H34,500%), then it works fine.

    I don't know why CF would care what function is used, since I figured that would be evaluated in the Excel engine and not CF, but who knows. A solution would be appreciated!

    #26Posted by Brian H. | Jan 25, 2010, 03:35 PM
  • Brian H.

    Hey Alan.

    I am having similar issues, though SpreadsheetGetCellValue() does actually return the value when a formula references another cell, but it barfs when the cell (or the cell that the cell references) contains some specific functions.

    For instance, it had no problem evaluating a cell which had a formula "=E18*Assumptions!$D$19" which is a ref to another cell in another sheet. However, if a cell contains any of the following (non-exhustive) list of functions, it will return the function and not the value (this includes any cells that reference cells containing these functions): "NPV(), IIR()". So what I am going to try to do is replace the functions in those cells with something else that IS compatible.

    To reiterate, if I call upon spreadsheetGetCellValue() to evaluate a cell containing =IRR(E34:H34,500%) it will fail (and return the function), yet if i change that function to =SUM(E34:H34,500%), then it works fine.

    I don't know why CF would care what function is used, since I figured that would be evaluated in the Excel engine and not CF, but who knows. A solution would be appreciated!

    Edit:

    I ended up writing a custom java class to house my own NPV and IRR functions. What I do is I "correct" my sheet by performing my own calculations and overwriting the cell values (IRR and NPV functions) with the calculated values. Then I can let the rest of the sheet logic do it's magic. The code is here. If anyone needs help, you can reach me at spam2 AT splicedigital DOT com.

    Just compile this class with javac irrNPV.java, and move the class file info c:\coldfusion9\lib\ Restart CF and you can instance it by <cfset L.IrrNPV = CreateObject("java", "IrrNPV")>. Again this solution will not work for everyone, and I was lucky enough to have a pretty simple sheet where it came to IRR and NPV.


    public class IrrNPV {
    /*
    public static void main(String[] args) {

       double npv_disc = 0.1;
       double[] the_arr = new double[4];
       the_arr[0] = -200320;
       the_arr[1] = 5060430;
       the_arr[2] = 5060430;
       the_arr[3] = 5060430;

       System.out.println("" + IRR(the_arr, 5) );

       //System.out.println("" + npvOld(npv_disc, the_arr[0], the_arr[1], the_arr[2], the_arr[3]) );
       //System.out.println("" + npv(npv_disc, the_arr, 0) );

    }

    */


       public double npv(double r, double[] the_arr, int index) {
          double i = the_arr[index];
       
          if(the_arr.length-1 == index)
          {
             return i / java.lang.Math.pow(1+r, index);
          }
          else
          {
             return (i / java.lang.Math.pow(1+r, index)) + npv(r, the_arr, index+1);
          }
       }


       private final double MINDIF=.0000001;



    public double IRR(double[] list, double guess) {
        int i;
       
        /* Testing */
        /* for (i=0;i<list.length;i++) {
           System.out.println("The array cell " + i + " = " + list[i]);
        } */
       

    double irr=0;

          /* System.out.println("The array length is " + list.length); */
       
           if (list[0]==0)
          {
          /* System.out.println("Returning NaN because first element in list was 0"); */
          return Double.NaN;
          }
       
           int ineg=0;
       
           int ipos=0;
       
          
       
           for (i=0;i<list.length;i++) {
       
              irr+=list[ i ];
       
              if (list[ i ]>0) ipos++;
       
              else if (list[ i ]<0) ineg++;
       
           }
       
           if (ineg==0 || ipos==0)
          {
          /* System.out.println("Returning NaN because ineg= " + ineg + " ipos= " + ipos); */
          return Double.NaN;
          }
       
       
       
           double a1=list[0];
       
           if (guess<=0) guess=.5;
       
           if (irr<0) irr=-guess; else irr=guess;
       
           boolean was_hi=false;
       
           double a3;
       
           for (int iter=0;iter<=50;iter++) {
       
              a3=a1;
       
              int j=1;
       
              for (i=1;i<list.length;i++) {
       
                a3+=list[ i ]/Math.pow(1.0+irr,j);
       
                j++;
       
              }
       
              if (Math.abs(a3)<.01)
       {   /* System.out.println("Returning a "+ irr); */
       return irr;
       }
       
       
              if (a3>0) {
       
                if (was_hi) guess/=2;
       
                irr+=guess;
       
                if (was_hi) {
       
                 guess-=MINDIF;
       
                 was_hi=false;
       
                }
       
              } else {
       
                guess/=2;
       
                irr-=guess;
       
                was_hi=true;
       
              }
       
              if (guess<=MINDIF)
       {   /* System.out.println("Returning b "+ irr); */
       return irr;
       }

    }

       /* System.out.println("Returning NaN becase at end of routine"); */
    return Double.NaN;

    }


    }

    #27Posted by Brian H. | Jan 26, 2010, 12:37 AM
  • Jeff Cook

    Could someone post an example of how to set a cell format to the Excel currency format (e.g.
    9 -> $9
    1234 -> $1,234

    #28Posted by Jeff Cook | Jan 26, 2010, 04:04 PM
  • Shannon Hicks

    I want to total up all the columns of a row dynamically. Problem is, I don't see any good way to convert a column number into a column name... i.e. how do I know that column 182 is really "CCJ" so that I can set my formula to SUM(A15:CCJ15) ?

  • Jeff

    Also looking for how to format as currency.... looking at the docs I shuld be able to:

    <cfset SpreadsheetFormatColumn(sObj, {dataformat="$#,##0.00"}, 4)>

    But on running that, CF thrown an error....


    Jeff

    #30Posted by Jeff | Aug 24, 2010, 07:23 PM
  • Dave DuPlantis

    Just in case anyone like Jeff is looking for solutions for his problem ... ColdFusion is most likely interpreting the # signs, so you would need to double them to get the effect you're expecting.

    Try {dataformat="$##,####0.00"}. That worked for me in CF 9, but of course YMMV.

    #31Posted by Dave DuPlantis | Jan 14, 2011, 05:34 PM
  • Jay

    I am experiencing some strange behavior, using IE 7 CF9 and CFspreadsheet. The file is written fine and when I try to open it using IE 7 by clicking on the link I dont get a spread sheet rather I get a page of wing-dings. I right click and do "save target as" and specify xls then open it, it works fine. In all other browsers, i can click on the link, and it will open as expected. Any ideas what is causing this? I also set the format to csv and that had no effect.

    #32Posted by Jay | Mar 1, 2011, 02:53 PM
  • Eliseo

    I was just wondering, in the event one isn't lucky enough to be running CF9, and running CF7 with POIUtility.cfc (Ben Nadel's extension of the POI library from Apache), would anyone know how to recognize any formatting within individual spreadsheet cells... so if the word "BLAH" were in cell A1 in bold... is there a way one could detect that so as to allow us to render it as <B>BLAH</B>?

    #33Posted by Eliseo | Mar 27, 2011, 11:35 PM
  • Ken Gladden

    Looks like the last code sample got cut off or is it because i am reading this post one my iPad?

  • Manuel

    Hi Ben, I'm development an application, where I open a differents kind of Excel Files and Coldfusion 9 doesn't support correctly the formules and the new version.


    For example Excel 2003 is very fast Vs Excel 2010. (When I read the data of this files 2010 the time required is 4 seconds)
    In theory Coldfusion 9 must support any kind Excel Files, but when there a lot formules in this files send a crash error and the application take a long time for response.
    Is possible read a Excel file and convert the formules to data?.

    There are any solution or fix for this problem?

    Thanks

    #35Posted by Manuel | Nov 11, 2011, 09:50 AM
  • JD

    Hi, Ben.

    What if one needed to insert a drop down list in a particular cell? (Like in http://office.microsoft.com/en-us/excel-help/creat...)

    Thanks,

    JD

    #36Posted by JD | Dec 30, 2013, 11:47 AM