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:
Now to create a spreadsheet using a database query:
SELECT orderid, customerfirstname, customerlastname, total
FROM orders
ORDER BY orderid
That’s all it takes.
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:
SELECT orderid, customerfirstname, customerlastname, total
FROM orders
ORDER BY orderid
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
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
More On ColdFusion And Spreadsheets
36 responses to “More On ColdFusion And Spreadsheets”
-
Oh my! One of my least favorite tasks made dead simple. Thank you adobe!
-
What about "xlsx" (Excel 2007) files? Can it handle those?
-
I guess I should have read the previous post first…where Ben answered the same question. :o)
-
Will we be able to set cells to use 16bit so UTF-8 characters work in spreadsheets?
-
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?
-
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 -
@Ben. Ok. I wasn’t sure if I missed something. It makes perfect sense now. Looking forward to using this in the future.
-
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! 😀 -
Cory, we use some POI, some Open Office APIs, and some of our own stuff, to make this all work.
— Ben -
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.
-
What about charts? Is it possible to create charts in
excel through coldfusion? -
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 ? -
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 -
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? -
@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" g -
@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 -
Vinu – Thanks. I will play with that and see what happens. I appreciate the sharing!
Eddie -
@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 ex -
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! -
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?
-
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? -
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? -
@ Neil
I have logged the issue 80615. This looks like a bug. We will investigate more on this.
Thanks
Kunal Saini
Adobe ColdFusion Team -
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! -
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! -
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:coldfusion9lib 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;
}
} -
Could someone post an example of how to set a cell format to the Excel currency format (e.g.
9 -> $9
1234 -> $1,234 -
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) ?
-
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 -
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. -
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.
-
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>?
-
Looks like the last code sample got cut off or is it because i am reading this post one my iPad?
-
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 -
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/create-or-remove-a-drop-down-list-HP010342357.aspx)
Thanks,
JD
Leave a Reply