More On ColdFusion And Spreadsheets

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

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

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. Ok. I wasn't sure if I missed something. It makes perfect sense now. Looking forward to using this in the future.

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

--- Ben

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

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

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

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

Eddie

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!

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?

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

Thanks
Kunal Saini
Adobe ColdFusion Team

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?

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;

}


}