4 Replies Latest reply on Oct 31, 2012 4:10 AM by BKBK

    SpreadsheetGetCellValue error

    plarts Level 1

      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 ?

        • 1. Re: SpreadsheetGetCellValue error
          plarts Level 1

          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.

          • 2. Re: SpreadsheetGetCellValue error
            Dan Bracuk Level 5

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

            • 3. Re: SpreadsheetGetCellValue error
              Adam Cameron. Level 5

              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

              • 4. Re: SpreadsheetGetCellValue error
                BKBK Adobe Community Professional & MVP

                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)>