CF9.0.1 - Small Quirk: New XLS Spreadsheets are missing CreationDate property

Testing the new cfspreadsheet extension for Railo reminded me of an ACF spreadsheet quirk I saw in one of the forums a few weeks back. For some reason, the creation date property is not populated for new binary spreadsheets (ie *.xls). Nor is that property accessible via the SpreadsheetAddInfo function. Granted it is just a metadata value. So it is hardly a major issue. But the omission does seem a bit odd. Especially since ACF does set the creation date for new .xlsx files.

It is easy enough to that property on an existing spreadsheets. Just read in the file. Grab the SummaryInformation and set the property by invoking setCreateDateTime(). SummaryInformation is basically the same core document properties returned by the SpreadsheetInfo function. Unfortunately, CF does not add SummaryInformation for new spreadsheets until they are saved to disk. So if you tried the code below on a new spreadsheet object it would fail.

<cfset sampleFile = ExpandPath("newSpreadsheet.xls") />
<cfset sheet = SpreadsheetRead( sampleFile ) />
<cfset wb = sheet.getWorkBook() />
<cfset wb.getSummaryInformation().setCreateDateTime( now() ) />
<cfset spreadsheetWrite( sheet, sampleFile, true ) />

As it turns out there is an easy way to add the creation date property under CF9.0.1, albeit undocumented. CF9.0.1 includes a slightly newer version of POI (3.6-beta1). The newer version includes a new convenience method called createInformationProperties(). When used on a new workbook it creates and initializes the missing summary properties. Even before the new file is written to disk. Once the properties are intialized, you can easily set the creation date.

<cfset sheet = SpreadsheetNew()>
<cfset spreadsheetSetCellValue( sheet, "foo", 1, 1 )>
<!--- get underlying workbook object --->
<cfset wb = sheet.getWorkBook()>
<!--- initialize the summary information and set the creation date --->
<cfset wb.createInformationProperties()>
<cfset wb.getSummaryInformation().setCreateDateTime( now() ) />
<!--- save the spreadsheet to disk --->
<cfset spreadsheetWrite( sheet, ExpandPath("newSpreadsheet.xls"), true )>