First Attempt At Excel Document Formatting Hooks Using ColdFusion And POI

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:

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:

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.

What Other People Are Searching For

[ local search ] coldfusion poi
[ local search ] created xls files poi
[ local search ] create excel document with coldfusion

You Might Also Be Interested In: