Cast error trying to read an excel spreadsheet with cfspreadsheet

New Here ,
Feb 17, 2016 Feb 17, 2016

Copy link to clipboard

Copied

I'm trying to read an Excel spreadsheet using CF spreadsheet and keep getting the error below.

java.lang.ClassCastException: org.apache.poi.xssf.usermodel.XSSFRichTextString cannot be cast to org.apache.poi.hssf.usermodel.HSSFRichTextString

Here is the line of code that causes the error:

<cfspreadsheet action="read" src = "#xfile#" sheetname="Pilot Instructions" name="pilot_sheet">

This is CF 11 running in IIS. Any thoughts on why this error is being generated?

Thanks

DW

TOPICS
Advanced techniques

Views

1.8K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Feb 27, 2016 Feb 27, 2016

Copy link to clipboard

Copied

XLSX and XLS are processed by different parts of the POI engine. XSSF implies XLSX and HSSF implies XLS.

So the error message suggests that you may be attempting to pass off an XLSX file as XLS. Did you, for example, save an XLSX file in XLS format?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 29, 2016 Feb 29, 2016

Copy link to clipboard

Copied

I didn't create the file, so I'm not sure which format is the true format. The file that I read in was XLSX, so I changed the extension to XLS to see if that would make any difference, but I received the same error. In further testing, I realized that my first post gave the incorrect line of code that was throwing the error. It wasn't the read command, but the update command immediately following:

<cfspreadsheet action="read" src = "#xfile#" sheetname="Pilot Instructions" name="pilot_sheet">

<cfspreadsheet action="update" filename="#new_file#" sheetname="Pilot Instructions" name="pilot_sheet" password="pwd">

You were the one trying to help with the issue that I had with my spreadsheet reading in all three sheets at once instead of one-at-a-time and couldn't get the password to work. This is the same spreadsheet. What I attempted to do here was to create a file from scratch with a dummy sheet in it, read the other file and use the update function to add the three sheets that I needed and then delete the dummy sheet. Everything works just fine until I try using this specific Excel file. Is it possible that this file is corrupted in some way that it opens fine in Excel, but other apps have issues? Or, could it be that there it is a bit too complicated? There are some hidden sheets and other non-standard types of things in it.

Thanks

DW

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Feb 29, 2016 Feb 29, 2016

Copy link to clipboard

Copied

I can help test the file. I sent you my e-mail address.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Mar 01, 2016 Mar 01, 2016

Copy link to clipboard

Copied

Thanks for sending me the file. When I ran your code using the file, I had no problem whatsoever. But it enabled me to see that your code has an error.

The sheet name has an underscore. So use instead

sheetname="Pilot_Instructions"

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Mar 02, 2016 Mar 02, 2016

Copy link to clipboard

Copied

Thanks for looking at this. I’m at a loss as to why it doesn’t work for me. I’m still getting the same error after making the change with the underscore. There must be something different in the environment that is causing this.

Thanks

DW

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Mar 02, 2016 Mar 02, 2016

Copy link to clipboard

Copied

What happens when you do the following test. Store the code

excelTest.cfm

<cfset xfile=expandpath('Entity_Profile.xlsx')>

<cfset new_file=expandpath('New_Entity_Profile.xlsx')>

<cfspreadsheet action="read" src = "#xfile#" sheetname="Pilot_Instructions" name="pilot_sheet">

<cfspreadsheet action="update" filename="#new_file#" sheetname="Pilot_Instructions" name="pilot_sheet" password="pwd">

Done

in the same directory as the file Entity_Profile.xlsx. Then run it.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Mar 03, 2016 Mar 03, 2016

Copy link to clipboard

Copied

I ran your code and it created the spreadsheet with all three sheets instead of just one. Also, it wasn’t locked. I’ve attached the file that was generated. This was the behavior that I was getting before I tried to create a file and then populate the individual sheets.

Thanks

DW

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Mar 03, 2016 Mar 03, 2016

Copy link to clipboard

Copied

Am I right to assume you didn't get an error this time?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Mar 03, 2016 Mar 03, 2016

Copy link to clipboard

Copied

No, I did not get the error, but I’m back to where I was before and can’t get the file to lock.

DW

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Mar 03, 2016 Mar 03, 2016

Copy link to clipboard

Copied

LATEST

This thread is about an error. If after you implemented my last code suggestion you got no more errors, then it means that there probably was something wrong with your paths. Dump the values of xfile and new_file to confirm. That would imply that the original question of this thread has been answered.

Mark this then as answered. Then create a new thread for the locking problem or place a link here that points to an existing thread.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines