Small Bug with SpreadSheetCreateSheet

I was running some comparisons with CF9's spreadsheet functions and noticed a small bug with SpreadSheetCreateSheet(). When you omit the sheet name argument, CF automatically generates the sheet's name for you, using the standard naming convention Sheet1, Sheet2, etcetera). But apparently CF, or more accurately POI, does not verify the new name is not already in use.

If you run the code below, it creates two sheets both named Sheet1.  Obviously if you saved the file to disk and tried to open it, Excel would complain about the duplicate sheet names. It is possible it may also cause problems with other spreadsheet functions that rely on sheet name, rather than index.

<cfset sheet = SpreadSheetNew() />
<cfset SpreadSheetCreateSheet(sheet) />
<cfset info = SpreadSheetInfo(sheet)/>
<cfdump var="#info#" label="Duplicate sheet names" />

Now I suspect most people prefer to provide their own (more descriptive) sheet names, in which case the issue does not apply. But in case you do choose to use this feature, just be aware of this "gotcha".

...Read More

Converting Relative to Absolute URL's

The very first time I used the ExpandPath() function I remember wondering why there was not a similar function for expanding url's. At some point later, I did sit down and write a very rudimentary ExpandURL function. I have long since forgotten the code. But I do have a vague recollection of an ugly series of strings functions. Well the other day I came across a tip on stackoverflow.com that would have made it much easier: java.net.URL.


You simply pass in an object representing the base url (ie http://www.mysite.com/blog/archive/) and a string representing the relative path (ie "../../somePage.cfm"). The java.net.URL class normalizes the paths and returns an absolute url. In this example it would be: http://www.mysite.com/somePage.cfm. With a little effort it could be used to create a custom ExpandURL() function.

<!--- 
   ... or using the base url of current request 
   <cfset theBaseURL = getPageContext().getRequest().getRequestURL()>
--->
<cfset theBaseURL = "http://www.mysite.com/blog/archive/" />
<cfset theRelativeURL = "../../somePage.cfm" />
<cfset baseURL = createObject("java", "java.net.URL").init( theBaseURL ) />
<cfset absURL = createObject("java", "java.net.URL").init( baseURL, theRelativeURL ) />

<cfoutput>
   <p>theBaseURL = #theBaseURL#</p>
   <p>theRelativeURL = #theRelativeURL#</p>
   <p>absURL = #absURL.toString()#</p> 
</cfoutput>

Now obviously I am glossing over a lot.  There are definitely nuances to be aware of, but you can find a complete description of how the paths are resolved in the java api (and referenced specifications for url's and uri's). This method is not perfect, but overall it is a lot more robust and elegant than using string functions.

...Read More

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

...Read More

Running ACF Database Examples on Railo

I was excited to read there is an alpha cfspreadsheet extension available for Railo (Written by Andy Jarrett as a fork of the CFPOI project by Matt Woodward.)  As a user of both ACF and Railo I was curious how the two versions of the tag compared.  Being lazy, I decided to use the cfspreadsheet examples from the ACF documentation for some basic tests. While they are convenient, they naturally make use of sample databases included only with ACF.  But it occurred to me, why not just copy the sample databases into Railo and set up a datasource? Then I could easily use the examples with both engines.

So first I located the cfcodexamples database and copied the entire directory beneath my Railo root.

My Local Settings:

ACF:   c:/dev/ColdFusion9/db/cfcodexamples/
Railo: c:/dev/railo321/db/cfcodeexamples/

Next I created the datasource. A quick search turned up these handy instructions for configuring an Apache Derby datasource on Railo, posted by Todd Rafferty.

1) First copy the derby.jar into {railo_server}\lib and restart Railo

My Local Settings:    
c:/dev/railo321/lib/derby.jar

2) Create a new "Other" datasource in Railo. (As this was just for testing, I accepted all the default settings)

My Local Settings:

Name:  cfdocexamples

Class: org.apache.derby.jdbc.EmbeddedDriver

Dsn:   jdbc:derby:db/cfdocexamples;create=true

Note:   The database path in the dsn is relative to the application root. I.e.

Root:         c:/dev/railo321/
Database:     c:/dev/railo321/db/cfcodexamples
Relative Path: db/cfdocexamples

Once I verified the datasource.  I was able to run the same cfspreadsheet examples under my Railo install too. Now obviously there are differences between the two engines. So it would be silly to expect every tag example to work exactly the same. But I thought this was an easy way to run some quick comparisons between the two cfspreadsheet tags.

...Read More