POI Utility Now Supports Formulas And Cell Aliasing In Custom Tags
Posted August 4, 2008 at 1:43 PM
Hot on the heels of my last update, I am releasing another mini update for the ColdFusion 8 version of my POI ColdFusion custom tags. Taking Zac Spitzer's most excellent advice, I haven't just included formula support, I've also built in support for cell aliasing. What this means is that rather than using values in your formulas like "D8", you can alias a cell and use that name in the cell formula (ex. SUM( @Start:@End )).
Right now, this is only part of the ColdFusion 8 version of the tags because of the REMatch() function (and some other syntax short-hands). But hopefully soon, I can reorganize my code a bit to have a little UDF support that will make the CF7 compatibility steps easier.
To alias a cell, all you have to do is add the "Alias" attribute. An alias can be composed of alpha-numeric data, underscores, and dashes:
Launch code in new window » Download code as text file »
- <poi:cell
- value="35000"
- type="numeric"
- numberformat="0.00"
- alias="StartSum"
- />
The above cell has been aliased with the name "StartSum". Now, when referring to this alias in a cell formula, you need to use the "@" symbol:
Launch code in new window » Download code as text file »
- <poi:cell
- value="SUM( @StartSum:@EndSum )"
- type="formula"
- numberformat="0.00"
- />
Now, your code can grow without you having to worry about recalculating the formulas with every edit. Let the POI custom tags keep track of that for you.
If you care to see a demo, here is the new code file that I am shipping with this release:
Launch code in new window » Download code as text file »
- <!--- Import the POI tag library. --->
- <cfimport taglib="./poi/" prefix="poi" />
-
-
- <!---
- Create an excel document and store binary data into
- REQUEST variable.
- --->
- <poi:document
- name="REQUEST.ExcelData"
- file="#ExpandPath( './incomes.xls' )#"
- style="font-family: verdana ; font-size: 10pt ; color: black ; white-space: nowrap ;">
-
-
- <!--- Define style classes. --->
- <poi:classes>
-
- <poi:class
- name="title"
- style="font-family: arial ; color: white ; background-color: green ; font-size: 18pt ; text-align: center ;"
- />
-
- <poi:class
- name="header"
- style="font-family: arial ; background-color: lime ; color: white ; font-size: 14pt ; border-bottom: solid 3px green ; border-top: 2px solid white ;"
- />
-
- <poi:class
- name="footer"
- style="background-color: red ; color: white ; border-top: 3px solid black ;"
- />
-
- </poi:classes>
-
-
- <!--- Define Sheets. --->
- <poi:sheets>
-
- <poi:sheet name="Incomes">
-
- <!--- Define global column styles. --->
- <poi:columns>
- <poi:column style="width: 150px ;" />
- <poi:column style="width: 150px ;" />
- </poi:columns>
-
-
- <!--- Title row. --->
- <poi:row class="title">
- <poi:cell value="Yearly Incomes" colspan="2" />
- </poi:row>
-
- <!--- Header row. --->
- <poi:row class="header">
- <poi:cell value="Name" />
- <poi:cell value="Income" />
- </poi:row>
-
-
- <poi:row>
- <poi:cell value="Katie" />
- <poi:cell value="35000" type="numeric" numberformat="0.00" alias="StartSum" />
- </poi:row>
-
- <poi:row>
- <poi:cell value="Allison" />
- <poi:cell value="72500" type="numeric" numberformat="0.00" />
- </poi:row>
-
- <poi:row>
- <poi:cell value="Libby" />
- <poi:cell value="57000" type="numeric" numberformat="0.00" />
- </poi:row>
-
- <poi:row>
- <poi:cell value="Kit" />
- <poi:cell value="110000" type="numeric" numberformat="0.00" />
- </poi:row>
-
- <poi:row>
- <poi:cell value="Anna" />
- <poi:cell value="63000" type="numeric" numberformat="0.00" alias="EndSum" />
- </poi:row>
-
-
- <!--- In the footer, create a formula for summing the income values. --->
- <poi:row class="footer">
- <poi:cell value=" " />
- <poi:cell value="SUM( @StartSum:@EndSum )" type="formula" numberformat="0.00" />
- </poi:row>
-
- </poi:sheet>
-
- </poi:sheets>
-
- </poi:document>
-
-
-
- <!--- Tell the browser to expect an Excel file attachment. --->
- <cfheader
- name="content-disposition"
- value="attachment; filename=incomes.xls"
- />
-
- <!---
- Tell browser the length of the byte array output stream.
- This will help the browser provide download duration to
- the user.
- --->
- <cfheader
- name="content-length"
- value="#REQUEST.ExcelData.Size()#"
- />
-
- <!--- Stream the binary data to the user. --->
- <cfcontent
- type="application/excel"
- variable="#REQUEST.ExcelData.ToByteArray()#"
- />
This code aliases the StartSum and EndSum cells that make up the first and last rows of our salary column data. Running the above code results in the following Excel file:
| | | | ||
| |  | | ||
| | | |
The value in the red at the bottom is the sum of the invoices above it.
You can download the new release from my POI Utility Project page.
Download Code Snippet ZIP File
Comments (8) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
OOPhoto - Handling Database Transactions With Ease
iPhone Wallpaper Template For Fireworks / Photoshop (Saucy)
Reader Comments
Ben and Zac,
Great functionality!
To make things even more variable, is it possible to change the cell reference in Excel to R1C1?
Then if the row an column numbers could be replaced by variables the following should be possible:
<cfloop from="1" to="12" index="I">
<poi:cell type="formula" NumberFormat="0" value="R24C#I#-R12C#I#"/>
</cfloop>
And that would make life so much easier!
Greetz,
Chris
Posted by Chris Pijl on Sep 5, 2008 at 4:53 AM
@Chris,
I am not sure that I follow what you are saying. The Value attribute is a ColdFusion attribute, so you can make it dynamic with ## signs as you use. But I am not sure if you are asking something more than that?
Posted by Ben Nadel on Sep 5, 2008 at 8:13 AM
Ben,
In Excel you can choose to refer to your cells using R(ow)C(olumn) names.
There is a option you can check in Extra -> Options -> General to change the referral type from A1 to R1K1.
If you'd be able to use that, things will get a lot easier!
Chris
Posted by Chris Pijl on Sep 5, 2008 at 9:07 AM
Is it possible to autosize column with the content?
Posted by Mahieu on Sep 10, 2008 at 10:06 AM
Ben,
I've found myself wanting to be able to influence the magnification default when Excel opens, since my data overflows the screen on a default resolution...
I've added the following to the sheet.cfm:
<!---
The magnification of the sheet. Defaults to 100%.
--->
<cfparam
name="ATTRIBUTES.ZoomIn"
type="string"
default="100"
/>
<cfparam
name="ATTRIBUTES.ZoomOut"
type="string"
default="100"
/>
<cfset VARIABLES.ZoomIn = ATTRIBUTES.ZoomIn />
<cfset VARIABLES.ZoomOut = ATTRIBUTES.ZoomOut />
<!---
Check if a custom magnification has been given
--->
<cfif (
(VARIABLES.ZoomIn NEQ 100) OR
(VARIABLES.ZoomOut NEQ 100)
)>
<!--- Set zoom level --->
<cfset VARIABLES.Sheet.setZoom(
JavaCast( "int", VARIABLES.ZoomOut ),
JavaCast( "int", VARIABLES.ZoomIn)
) />
</cfif>
Works great :)
PS. I've also found a work around for not being able to use the R1C1-reference for formulas.
I've created an array:
<cfset ColumnArray = ArrayNew(1)>
<cfset ColumnArray[1] = "A">
<cfset ColumnArray[2] = "B">
...
<cfset ColumnArray[51] = "AY">
<cfset ColumnArray[52] = "AZ">
(Don't need more columns at the moment)
Which allows me to do this:
<cfloop from="1" to="22" index="I">
<poi:cell type="formula" NumberFormat="0" value="#ColumnArray[2+I]#18 + #ColumnArray[2+I]#22" />
</cfloop>
Makes looping thru columns quite nice :)
Posted by Chris Pijl on Sep 16, 2008 at 5:38 AM
@Chris,
That looks pretty straightforward. I will see about adding it to the project. Thanks!
Posted by Ben Nadel on Sep 18, 2008 at 7:20 PM
@Chris,
Thanks for the Zoom tip. I am adding that to the project today.
Posted by Ben Nadel on Sep 24, 2008 at 3:24 PM
Great Ben, thanx ;)
Posted by Chris Pijl on Sep 25, 2008 at 6:05 AM
