SpreadsheetGetCellValue error

[plarts]

With the following code under CF9,

<cfset c_dir=#GetDirectoryFromPath(GetCurrentTemplatePath())#>
<cfset ex_file=c_dir & "eshopping.xlsx">
#ex_file#<br>
<!--- 80 rows --->
<!--- 454 colomns --->

<cfset val=SpreadsheetGetCellValue(ex_file, "6", "454")>
<hr>
value : #val#

I have the following error : Internal server error : Error casting an object of type java.lang.String to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed. java.lang.String

(Excel 2007, extension xlsx)

I think I neeed to know how to define dthe "spreadsheetobject" ?

is this the Excel file adress ? or something ?

Thanks for HELP,  I thought It will be easy of use ?

[plarts]

So now, I used this code :

<cfspreadsheet 

    action="read"

format = "CSV"

    src = "#ex_file#"

    columns = "247"

    name = "val"

    rows = "8" >

<cfdump var="#val#">

Simpler and it does work.

But colums are limited at 256, and my Sheet has 484 columns.

Thanks for any suggetsion.

Pierre.

[Dan_Bracuk]

If it's a csv file you can open it with either cffile or cfhttp.

[Adam Cameron.]

That's converting it to CSV, not reading it from CSV.

256 columns is an undocumented limitation of spreadsheet operations in CF, I'm afraid.  It might be worth voting for the ticket I just raised to get this sorted out (the limit should not be there).

https://bugbase.adobe.com/index.cfm?event=bug&id=3355173

The only thing I can think of is to use POI directly (which is all <cfspreadsheet> uses; it just doesn't use it very cleverly, it seems).  You should be able to read the whole thing in.

--

Adam

[BKBK]

plarts wrote:

With the following code under CF9,

<cfset c_dir=#GetDirectoryFromPath(GetCurrentTemplatePath())#>
<cfset ex_file=c_dir & "eshopping.xlsx">
#ex_file#<br>
<!--- 80 rows --->
<!--- 454 colomns --->

<cfset val=SpreadsheetGetCellValue(ex_file, "6", "454")>
<hr>
value : #val#

I have the following error : Internal server error : Error casting an object of type java.lang.String to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way than it was designed. java.lang.String

(Excel 2007, extension xlsx)

I think I neeed to know how to define dthe "spreadsheetobject" ?

is this the Excel file adress ? or something ?

You have guessed correctly what the problem is. The variable ex_file is a string representing a URL, not a spreadsheet object. You may define the spreadsheet object like this:

<cfset c_dir=#GetDirectoryFromPath(GetCurrentTemplatePath())#>

<cfset ex_file=c_dir & "eshopping.xlsx">

<cfset spreadSheetObj  = spreadSheetRead(ex_file)>