First Attempt At Excel Document Formatting Hooks Using ColdFusion And POI
Posted February 15, 2007 at 2:30 PM
As I stated before, I have been trying to come up with a very easy way to generate Excel document with good formatting by using ColdFusion and POI at a high level. I am not talking about a simple wrapper for POI that makes it ColdFusion friendly. That kind of low-level, very granular stuff has been done before. I am talking about making it very easy for people to pass in a query or an array or a CSV value and convert that into a very nicely formatted Excel document with just a few lines of code.
I am not posting the implementation yet, as it is very early, but I thought I would share the thoughts to date. I have taken my POI Utility ColdFusion component and broken it out into a few objects:
- POIUtility.cfc
- POISheet.cfc
- POIHelper.cfc
The POI Helper ColdFusion component just has some utility functions such as CSV parsing and CSS stacking (read: cascading of CSS rules). The POI Sheet ColdFusion component houses the information about a given sheet that needs to be created. Then the POI Utility ColdFusion component is the glue that brings it all together.
Let's run through an example of where I am now:
Launch code in new window » Download code as text file »
- <!---
- Let's start out by creating an array of arrays that will
- act as the data for our Excel sheet. Behind the scenes,
- this array is actually getting converted to a query (but
- that is not important at this time).
- --->
- <cfset arrData = ArrayNew( 1 ) />
- <cfset arrData[ 1 ] = ListToArray( "Azure,9.0,10.0,Brunette" ) />
- <cfset arrData[ 2 ] = ListToArray( "Sarah,9.0,9.0,Brunette" ) />
- <cfset arrData[ 3 ] = ListToArray( "Kit,7.0,7.0,Brunette" ) />
- <cfset arrData[ 4 ] = ListToArray( "Kim,8.0,n/a,Black" ) />
- <cfset arrData[ 5 ] = ListToArray( "Annie,7.0,9.0,Brunette" ) />
- <cfset arrData[ 6 ] = ListToArray( "India,6.0,5.0,Blonde" ) />
- <cfset arrData[ 7 ] = ListToArray( "Cici,5.0,3.0,Blonde" ) />
-
-
- <!---
- Now, let's create an instance of the POI Utility. This call
- assumes that the POIUtility.cfc is in the same directory as
- the calling code.
- --->
- <cfset objPOI = CreateObject(
- "component",
- "POIUtility"
- ).Init()
- />
-
-
- <!---
- Get a POI Sheet object from the POI Utility. The sheet
- object is in now way tied to the POI Utility. This is
- just a comvenience method. You could just have easily
- called the CreateObject() method and instantiated the
- sheet yourself.... that is why CreateSheet() returns
- an uninitialized sheet (so that you can pass in the
- good stuff).
-
- When initializing, we can pass in the Name of the sheet,
- the data array we created above, the column names (for
- the header row) and the default CSS information).
- --->
- <cfset objSheet = objPOI.CreateSheet().Init(
- Name = "Hot Chicks",
- DataArray = arrData,
- ColumnNames = "Name,Hotness,Coolness,Hair Color",
- HeaderCSS = "font: italic 16pt verdana ; background: lime ; color: white ; border-bottom: 3px solid green ;",
- RowCSS = "border-bottom: 1px solid gold ; font-size: 12pt ;",
- AltRowCSS = "background-color: lemon_chiffon ;"
- ) />
-
-
- <!---
- Now, here's where it gets exciting. Let's format cells in
- the Excel file using conditions and CSS.
- --->
-
-
- <!---
- Here, we are are going to find any cell whose value is
- "n/a" and update the CSS with font and color.
- --->
- <cfset objSheet.AddFormatting(
- Condition = "{value} EQ 'n/a'",
- CSS = "font-style: italic ; color: GREY_40_PERCENT ;"
- ) />
-
- <!---
- Here, we are going to find any value in the 2nd column
- whose value is greater than or equal to 9 and we are
- going to update the CSS with color and font.
- --->
- <cfset objSheet.AddFormatting(
- Condition = "({column} EQ 2) AND (Val( {value} ) GTE 9)",
- CSS = "color: red ; font-weight: bold ;"
- ) />
-
- <!---
- Here, wer are going ot find any value in the 3rd column
- whose value is greater than or equal to 9 and update
- the CSS with color.
- --->
- <cfset objSheet.AddFormatting(
- Condition = "({column} EQ 3) AND (Val( {value} ) GTE 9)",
- CSS = "color: red ;"
- ) />
-
-
- <!---
- Now that our data and formatting are set, we are going to
- write the Excel file by sending in the file path and the
- sheet object we created above.
- --->
- <cfset objPOI.WriteExcel(
- FilePath = ExpandPath( "./test.xls" ),
- Sheets = objSheet
- ) />
The AddFormatting() method is what I am all jazzed up about now. You use that to add conditional formatting based on a expression that will get Evaluated during the Excel creation. Right now, there are three "place holders" that you can use in the conditional expression:
{column}
The current column index (1-based like ColdFusion).
{row}
The current row index (1-based like ColdFusion). This offset does NOT include the header row.
{value}
The actual data value of the cell.
The Condition expression actually gets evaluated using the ColdFusion method Evaluate() so you can use any methods or expression available to a standard ColdFusion expression. Even things like this are totally valid:
Launch code in new window » Download code as text file »
- "FindNoCase( 'sexy', {value} )"
As long as the condition evaluates to TRUE, the CSS is stacked on top of the existing CSS for that cell. Formatting conditions are applied in the order in which they were added to the sheet. The CSS formatting rules will cascade as much like standard CSS as I could manage (I am only human). If you look at the Excel below, you will notice that all of our conditions stacked appropriately with the RowCSS and AltRowCSS passed into the Sheet constructor.
Here is the Excel sheet that was produced with the above code:
| | | | ||
| |  | | ||
| | | |
NOTE: I resized the columns manually. Currently, the columns widths cause a bit of an issue (since I am trying to get text wrapping to apply).
How easy was that? I LOVE being able to use standard (and fuzzy) CSS rules to format a cell. CSS is awesome and natural and easy to read and understand. If you ain't using CSS, you'ze a sqaure.
As it stands right now, here are the methods available to the POISheetObject():
Init()
AddFormatting()
GetAltRowCSS()
GetColumnList()
GetColumnNames()
GetDataQuery()
GetFormattingConditions()
GetHeaderCSS()
GetName()
GetRowCSS()
SetAltRowCSS()
SetColumnList()
SetColumnNames()
SetCSV()
SetDataArray()
SetDataQuery()
SetHeaderCSS()
SetName()
SetRowCSS()
I am not going to detail them at this point as I feel many of them are self explanatory. They are pretty much getters and setters. Behind the scenes, every data value gets converted to a ColdFusion query. Data Arrays and CSV values all get converted to an array behind the scene. I just find queries easy to work with and they make for a very nice, uniform interface regardless of the data origin.
So anyway, that's the first go-round on this idea. Any thoughts?
And remember, I am not trying to make a ColdFusion API for the POI project. I am trying to make a very easy to use Data-to-Excel translation. I am not trying to solve very detailed problems here (think cell functions, regions, printable areas). At least not yet; all that stuff will come with time. What I am trying to do at first is accomplish one goal: Make it easy to create formatted Excel files based on simple sets of data.
While this sounds simple, it is proving harder than expected. I believe that the low-level stuff (setting functions, splitting screens) is actually going to be easier as less "magic" will be happening behind the scenes as the defining of such things will probably require reaching more directly into the POI API.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Populating An Existing, Formatted Excel Document Using ColdFusion And POI
Older Post
Ask Ben: Form Processing Demo With Anti-Spam Features
Reader Comments
Ben,
Have you also looked at JExcelAPI?
It would be interesting to see a comparison with POI.
http://jexcelapi.sourceforge.net/
Sami
Sami,
I looked at JExcel very briefly. I actually tried working with that before I even knew about POI. I kept trying to load it as a JAR file without putting it in the class paths (I like the idea of having stuff very modular - another conversation all together). I could read fine, but writing seemed be throwing errors.
I talked to Mark Mandell about it and he seemed to think it was a ClassLoader conflict. I should try it again using his Java class loader.
Long story short though, I was not able to get very far with JExcel without installed it. POI, on the other hand is already installed, so I went starting going that route.
Ben,
We have written some custom Java stuff to 'read and write' excel files that piggybacks off of POI.
In our world (mortgage rates, etc...) on import we are reading 'range specific locations' on an excel worksheet and parse that information.
For export, we drop blocks of data into a 'pre formatted' excel sheet at different range locations/worksheets. While we're not handling the formatting directly, it allows for easy reading of specific locations and writing of data to specific locations... in a 'pretty' but templated excel workbook.
There are some limitations, of course. But if you think this might be a workable solution for you, please drop me a line.
Alan,
That sounds freakin' brilliant! It never in a million years would have occurred to me to read in an existing file that had formatting. You magnificent bastard! I will give that a go to experiment.
Thanks!
Alan,
You are a genius:
http://www.bennadel.com/index.cfm?dax=blog:532.view
Thanks.
How funny I found two solutions I needed from your web site, two days apart!
I also wanted to offer you my condolences on your father's passing. I'm sure you are making him proud.
Now to my question, can you post what became of your inquiry to Alan about feeding the formatted xls (see two or three comments up).
@DL,
Glad to help, and thank you very much for your thoughts of my father. The preformatted XLS stuff is actually in the link right above your comment... but the link is not working for some reason. Here is a working link :)
http://www.bennadel.com/index.cfm?dax=blog:532.view
Ben,
Great work. Thanks for everything so far.
Whee can we get a copy of the POISheet.cfc and POIHelper.cfc??
Maybe I am not seeing another post where you posted these files..
David Sterling
@Dave,
I have not posted it anywhere. It's kind of on the back-burner for the moment. I will try and give it some time/effort. Thanks for checking in.
