Viewing By Entry / Main

Adding Date support to POIUtil.cfc when reading excel sheets

Posted At : October 1, 2007 7:58 PM |

Posted By : Luis Majano

Related Categories: Coldfusion

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. Ben, hope you are reading, I sent you an email with the update.

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 HSSFDateUtil class. This class contains a bundle of static methods for you to check and validate date formatting in excel.

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

// Define the local scope.
var LOCAL = StructNew();
//UTIL To determine dates by Luis Majano
var HSSFDateUtil = CreateObject("java","org.apache.poi.hssf.usermodel.HSSFDateUtil");

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

if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_NUMERIC) {
LOCAL.CellValue = LOCAL.Cell.getNumericCellValue();
}

Now to the interesting part. We need to first test if the cell value is a date formatted value. If there is no formatting here, then you will have some false positives. Then its a matter of creating a java calendar object and getting the date values on it. Look below for code:

if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_NUMERIC) {
//Override by Luis Majano to test for date values as excel
//stores date values as numbers.
LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();

//Test if its a date formatted field
if( HSSFDateUtil.isCellDateFormatted(LOCAL.Cell) ){
// format in form of M/D/YY
LOCAL.thisCalendar = CreateObject("java","java.util.GregorianCalendar").init();

LOCAL.thisCalendar.setTime(HSSFDateUtil.getJavaDate(LOCAL.CellValue));

LOCAL.tmpYear = LOCAL.thisCalendar.get(LOCAL.thisCalendar.YEAR).toString().substring(2);

LOCAL.CellValue = LOCAL.thisCalendar.get(LOCAL.thisCalendar.MONTH)+1 & "/" & LOCAL.thisCalendar.get(LOCAL.thisCalendar.DAY_OF_MONTH) & "/" & LOCAL.tmpYear;
}
}

And there you go. You have just added date formatting to the utility. You can do more formatting or date time if you want. All up to you. This is the basics

Comments (2) |

Print |

Send |

del.icio.us |

Linking Blogs

Related Blog Entries

Adding Date support to POIUtil.cfc (Jakarta POI) when WRITING excel sheets (October 8, 2007)

Comments

[Add Comment]

Hi Luis,
I did date-function in POI and forward to Ben long time ago, but seems to be he is busy never updated POI.

Anyway thanks for sharing this info with CF community.

# Posted By sana |

10/2/07 4:26 AM

Hey guys, this stuff looks good. Sana, I still have your email in my inbox :) I just can't seem to get around to it - I am sorry. I have about 3 updates in the queue for the POI stuff and then I also want to do some overhaul on it as well.

I will try and make this a priority this week. Thanks for all of your feedback!

# Posted By Ben Nadel |

10/2/07 2:30 PM

[Add Comment]

ColdBox 2.6.3 : RENEWED