Adding Date support to POIUtil.cfc (Jakarta POI) when WRITING excel sheets

The POIUtil.cfc by Ben Nadel is a great wrapper to the Apache POI project. I am using it in a current project and have come to certain caveats that I needed to extend the library. I did a post regarding how to read excel dates, this is the second part, how to write dates. This can apply to various types of formatting that could be done on an excel sheet. To come up with all the variations would take a very big big POI coldfusion wrapper. So I am coding to my needs.

Anyways, to sum up the problem

Excel stores dates as numbers therefore the only way to determine if a cell is actually stored as a date is to look at the formatting.

Enter a little digging in the docs and the API and you find the famous HSSFDataFormat class. This class will help you get the correct formatting for any kind of date, datetime, etc.

So the first thing you need to do is find the WriteExcelSheet method, then add the following var scoped variable:

Then you need to go to the following line, which should be around line 1731:

We just are initing a cellStyle to a blank structure, for every loop in the cell values. Now to the interesting part. We now go and add another if into the datetime metadata comparison. Look below for code:

This basically sets a data formated style to be of type date for this iteration of the cell contents. I also need to map it to a string. Remember that if your use case is different, then changes will occur here. The final step is to set the style of the cell if it exists. Find the following line, right below all the if else statements:

The last bit is where we check if the cellStyle exists and is not empty in the LOCAL structure. If it exists, then we set the cell style to our cell style object and wallah!! We are writing date formats. The same can be done to produce all kinds of formmatting. So you might read on the docs for this.