While helping a colleague out with a tricky problem accessing Excel files, I downloaded the Java Excel API library from SourceForge. After a quick scan of the documentation, I started to write some ColdFusion code.
<cfscript> jxlWorkbook = createObject('java','jxl.Workbook'); file = createObject('java','java.io.File').init("C:\\temp\\test.xls"); excelFile = jxlWorkbook.getWorkbook(file); </cfscript>
I’m immediately alerted with the message
The selected method getWorkbook was not found. Either there are no methods with the specified method name and argument types, or the method getWorkbook is overloaded with arguments types that ColdFusion can't decipher reliably. If this is a Java object and you verified that the method exists, you may need to use the javacast function to reduce ambiguity.
I dump out the object, and there are actually 5 method signatures for getWorkbook() and the one I want accepts a single argument of type java.io.File. I also know that the JavaCast() function certainly can’t handle a complex type like java.io.File. What’s a poor coder to do? I thought this was the end of the line, but figured I’d try something anyway…
<cfscript> jxlWorkbook = createObject('java','jxl.Workbook'); excelFile = jxlWorkbook.getWorkbook(createObject('java','java.io.File').init("C:\\temp\\test.xls")); </cfscript>
Lo, and behold, that worked perfectly! I never knew that you could use createObject() inside a method call.
There was another snag that I’d never ran across before: another method of the library wouldn’t accept integers passed from ColdFusion. In a simple from-to loop producing an index, the value of the index wasn’t being accepted by the method.
<cfloop from="0" to="#transactions.getRows() - 1#" index="row"> <cfloop from="0" to="#transactions.getColumns() - 1#" index="col"> <cfset currentCell = transactions.getCell(col, row)> <cfoutput>#currentCell.getContents()#</cfoutput> </cfloop> <br/> </cfloop>
I had to use JavaCast() to force them into the native int type.
<cfset currentCell = transactions.getCell(JavaCast("int", col), JavaCast("int", row))>