Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFinNC 2009 (Raleigh, North Carolina) with: Matthew Senn and Michael Senn

How To Easily Format An Excel Document Generating With POI And ColdFusion

By Ben Nadel on
Tags: ColdFusion

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

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

Reply to this Comment

@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.

Reply to this Comment

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.

Reply to this Comment

@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

Reply to this Comment

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.

Reply to this Comment

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#>

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.