spreadsheetAddImage() error: org.apache.poi.hssf.usermodel.HSSFClientAnchor cannot be cast to org.apache.poi.xssf.usermodel.XSSFClientAnchor

[DaveAndersonxx]

Trying to migrate an CF9 app to Lucee5, and after finding this wonderful extension, I'm getting...

"org.apache.poi.hssf.usermodel.HSSFClientAnchor cannot be cast to org.apache.poi.xssf.usermodel.XSSFClientAnchor" when attempting to insert a chart into a spreadsheet with spreadsheetAddImage().

Any thoughts on this?

[Leftbower]

Hi Dave - full disclosure: I did not write the original code for this, but rather just helped port the old Railo extension to be able to work with Lucee 4.xx and then repackage it again for Lucee 5.xx

That being said, looking at line 432 of the code, I see this:
<cfset theAnchor = loadPoi("org.apache.poi.hssf.usermodel.HSSFClientAnchor").init() />

This unfortunately appears to be hard-coding the addImage function for binary support (i.e. "xls") rather than xml (i.e. "xlsx") --- Apache POI uses different libraries to handle the different formats.

To test this you could create an xls file and see if you still have the same issue. If it works, that could be the quickest fix for you (assuming you can live with xls.) You could perhaps even resave as xlsx after the fact if that format was a must.

It would seem that the proper workaround would be to add a simple check in the function liked above and load the correct poi library based on the file type you are using. While this is likely an easy fix, I'm afraid I'll not have the bandwidth any time soon to take a deeper look at it. Of course, I am always open to pull requests if someone would like to help with some of these bugs/incompatibilities/etc.!

[DaveAndersonxx]

Hey, awesome! Good call. It was indeed an xlsx file I was working with. Thanks for the thoughtful response!

[ageax]

To fix the issue with XLSX, use the CreationHelper class (which works with both formats) on lines L363 and L432.

Old:

L363 : <!--- <cfset var creationHelper = CreateObject("java", "org.apache.poi.hssf.usermodel.HSSFCreationHelper") /> --->
...
L432: <cfset theAnchor = loadPoi("org.apache.poi.hssf.usermodel.HSSFClientAnchor").init() />

New:

L363: <cfset var creationHelper = getWorkbook().getCreationHelper()>
...
L432: <cfset theAnchor = creationHelper.createClientAnchor() />

[ravenlost]

I'm running into the same issue with xlsx files when trying to addImage(). This issue hasn't been resolved in the extension I suppose.... ? Thanks.