How To Easily Format An Excel Document Generating With POI And ColdFusion
Posted February 12, 2007 at 6:50 PM by Ben Nadel
I am at a point now where I find it fairly easy to create a Microsoft Excel document using ColdFusion and POI. What remains difficult is really formatting the document the way I would like it to be formatted. I added some basic CSS support to my POI Utility, but that only happens at the Header, Row, and Alternate Row level. But what happens if I want an entire column to be formatted differently? Or what if I only want certain cells to be formatted (ex. turn red if balance below zero).
Right now, to handle fine-tuned formatted, I would have to reach directly into the POI API. But that goes against the idea of ColdFusion wrapper for POI. What I need is a nice way to handle large-scale but fine tuned formatting issues.
Before I can figure it out, I think I need to figure out what the possible formatting scenarios are. I think it is based on the amount of formatting (fine tuning) vs. the size of the data. I think that Excel documents with super fine tuning will NOT have a lot of data. I also think that documents that have a ton of data will also not have a ton of formatting.
That means that formatting solutions that require very granular control will not need to scale well for large amounts of data. It also means that large scaling formatting solutions will not provide many formatting hooks. And that means that I am going to need two solutions for formatting.
Well maybe I am crazy? (this is all stream of consciousness, by the way).
I think for simple formatting solutions it would be sufficient to provide a number of conditional rules that can be applied to the document as it renders. Things like:
- ColumnIndex: N, CSS:.....
- RowIndex: N, CSS:....
- RowMod: Mod, CSS:....
- CellValueEqual: Value, CSS:....
This would allow individual rows, columns, and cell values to be formatted in a cascading manor. I love the whole CSS idea and I don't want to abandon that.
You know, at first, I thought the above would not allow for fine-tuned formatting, but maybe it would? I guess it would, but you might have to add a 1000 rules to get a highly formatted Excel file.
For a very fine tuned formatting solution, what I was thinking is more of an XML style document. Something like:
- <excel>
- <row>
- <col style="CSS GOES HERE">
- My Value Here.
- </col>
- <col style="CSS GOES HERE">
- My Value Here.
- </col>
- </row>
- <row>
- <col style="CSS GOES HERE">
- My Value Here.
- </col>
- <col style="CSS GOES HERE">
- My Value Here.
- </col>
- </row>
- </excel>
Something like this would really allow someone to look at the content and see how the formatting would take place (in a much more intuitive way than lists of conditionals). But of course, this would not scale very well for massive documents as it would involve XML parsing, which as a good deal of overhead on it's own.
Anyway, just some ideas I have been tossing around in my head.
Reader Comments
Ben,
now are you talking about something very similar to what I've seen done in C#. Check this out for ideas: http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx. Basically, you can write Excel type xml files with styles and everything else. The only problem is that the Excel xml is only supported in Office 2003 or higher.
Hi Ben
I've defined styles in my poi wrapper, and then I say something like
compCostsRep.addCell(4,Amount,"currency")
where compCostsRep is the xls object, addCell is a function, 4 is the column number, amount is the value, and currency is the format.
I prepared a bunch of formats (currency, time, date, etc) and whenever I need a new format I craft if in the wrapper and then drop it in with the above syntax.
This way, I (or anyone else) doesn't need to know anything about how poi works internally, and just picks from a library of formats.
Antony
@Boyan,
I am not familiar with that solution, however, I have done a bit of experimenting with the XML that is generated when you save an XLS document as XML. It is quite verbose and someone unclear as to what does what in the code.
@Antony,
I think that is a good idea, but it requires someone to actually use a cell-by-cell implementation of the API. What I am trying to do is keep it more high-level. At least for general formatting issues, keep it high-level. Of course, for more fine-tuned formatting control, something like what you have done might just have to be done. Thanks for the ideas.
I do something similar to what Antony does. In my POI wrapper, I pass in:
cfargument name="Query" type="query" required="true"
cfargument name="Filename" type="string" required="true"
cfargument name="Sheetname" type="variableName" required="false" default="Data"
cfargument name="ColumnList" type="string" required="false" default=""
cfargument name="ColumnTypes" type="any" required="false"
cfargument name="HeaderNames" type="any" required="false"
I can use ColumnList to specify the order of the columns from the query. ColumnTypes is a list of CF_SQL_* parameters that map to styles for that data type. For POI, of course, this actually means two mappings: a "Cell Type" and a "Format Type". Most of your Cell Types are CELL_TYPE_NUMERIC, but they might also be CELL_TYPE_STRING. As for the formats, you've got things like dates, currency, etc. (Hence why I use the CF_SQL_* versions instead of the POI versions: there's no need for the user of the object to have to research POI constants.)
Mine's not as fancy as it sounds like you want, but it does sound like you're heading in an interesting direction.
@Rick,
I think what we having going on is similar. I do not allow the user to pass in the ColumnType as I actually grab that out of the Query itself (using GetMetaData()). However, that is causing problems as it is CFMX7 only (GetMetaData() was not for queries until MX7). But of course, the query column type is not always appropriate such as with currency and date formatting.
I think we have to remember though, there are two types of formatting:
1. Data formatting
2. Cell formatting
I think right now, we are doing a great job in terms of data formatting (which is what I do using the query column type and you do using the column type argument). What is not being done well is the Cell formatting (background colors, fonts, borders, etc).
I started implementing basic CSS support in my POI utility, but it is VERY limited and only works a on three generic sections of the document.
What I am trying to move towards is fine tuning CSS style implementations. However, I certainly do need to also get better with the data formatting.
It's all very exciting :D
Hi Ben
Stumbled upon this at riaforge - thought you might find it useful.
Antony,
That is pretty cool look. It has a lot of functionality that I have not even touched yet such as the header, footer, functions, and regions. I hope to move in that direction eventually.
This POI wrapper seems to be a functionality wrapper; meaning, it wraps around the low level functionalities that POI provides such as reading / writing single cells, merging regions. These things are very cool. What I am concentrating on right now is bulk actions - reading a whole work book, writing an entire sheet with good formatting.
Once I get done with this "bird's eye view" stuff, I would like to start integrating more of that this guy has done. Thanks for pointing it out to me.
1) Is it possible in coldfusion to append the data into excel sheet which has some rows already filled in it.
2) How we can change the font to the variable passing into the excel with <cffile output = #value#>
@Praveen,
If you look at the my POI Custom Tag project:
http://www.bennadel.com/projects/poi-utility.htm
... I am pretty sure there is an action to append data to the XLS file. As far as changing the font, though, then you'll have to get a bit more complicated. You'd have to start messing with the Java objects directly.
