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
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?
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
Copy link to clipboard
Copied
I can help test the file. I sent you my e-mail address.
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"
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
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.
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
Copy link to clipboard
Copied
Am I right to assume you didn't get an error this time?
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
Copy link to clipboard
Copied
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.