8 Replies Latest reply on Sep 4, 2015 11:52 PM by itisdesign

    Populating a spreadsheet with an array using SpreadsheetAddrows

    Gregory@ETR

      Greetings,

       

      I want to populate a row in a spreadsheet using the SpreadsheetAddrows(spreadsheetObj, data[, row, column, insert]) function.

       

      CF9 documentation says for the data parameter can be "A query object with the row data or an array."

       

      My code

       


          SpreadsheetAddRow (exportSheet,
          #attributes.columnHeader#);

       

       

       

      throws the error, Complex object types cannot be converted to simple values.

       

      What am I missing here?

       

      Regards,

      Greg

        • 1. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
          Adam Cameron. Level 5

          I think your issue is two-fold.

           

          1) attributes.ColumnHeader doesn't sound like the sort of variable name which would hold an array to me.  It sounds like a string.  Is it an array?

          2) there's a bug in spreadsheetAddRows() in that with an array, one must specify the row/column values.

           

          This code works:

           

          <cfscript>
              oX = spreadsheetNew("withArray");

           

              a = ["tahi", "rua", "toru", "wha"];
              spreadsheetAddRows(oX, a, 1, 1);

           

              sXlsPath= expandPath("./withArray.xls");
             
              spreadsheetWrite(oX, sXlsPath, true);
          </cfscript>

           

          <cfheader name="content-disposition" value="attachment; filename=#getFileFromPath(sXlsPath)#">
          <cfcontent file="#sXlsPath#" reset="true" type="application/spreadsheet">

           

          However according to the docs, I should be able to simply do this:

          spreadsheetAddRows(oX, a);

           

          ie: without specifying row/column values.  However this errors with:

           

          An exception occurred while calling the function addRow.
          java.lang.IllegalArgumentException: Invalid row number (-1) outside allowable range (0..65535)

           

          So it looks like the internal workings of CF isn't defaulting its arguments properly.  I think this error msg is a bit sh!t, btw:  spreadsheetAddRows() should not be throwing errors relating to whatever other functions it is calling internally, because this is of no relevance to me: I'm calling spreadsheetAddRows() not addRow(), and it's misleading.  It should be catching internally-raised exceptions and then throwing its own exceptions.  Sloppy coding there from Adobe, IMO.

           

          Anyway, it looks to me like you're passing a string when you ought to be passing an array.  And that's your problem.  it's difficult to be sure with the amount of code you supply though (if you're using variables, always include the code that sets the variable!).

           

          --

          Adam

          • 2. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
            Gregory@ETR Level 1

            Adam,

             

            Thanks for the thoughtful response.

             

            attributes.ColumnHeader is indeed an array so that's not the issue.

             

            Your second point is true and insightful.  I was able to work around my problem last night in a similar way as you did by explicitly declaring the array values instead of passing the array.

             

            I had hoped that spreadsheetAddRows could accept the array directly because some of the array items  contain commas and this creates problems for the spreadsheetAddRows  function since it utilizes commas as the delimiter for items. I simply replaced all the commas.

             

            All of the spreadsheet functionality is new to CF9 and hopefully Adobe will take the necessary steps to make these functions work as advertised.

             

            Regards,

            Greg

            • 3. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
              Adam Cameron. Level 5

              I had hoped that spreadsheetAddRows could accept the array directly because some of the array items  contain commas and this creates problems for the spreadsheetAddRows  function since it utilizes commas as the delimiter for items. I simply replaced all the commas.

               

              All of the spreadsheet functionality is new to CF9 and hopefully Adobe will take the necessary steps to make these functions work as advertised.

               

              You could help 'em out by raising a bug for it: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html.

               

              If you post the bug URL back, I'll go vote for it too.

               

              --

              Adam

              • 4. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
                JaneUK Level 1

                Thanks for that: adding the 1,1 for row and column got rid of the same error for me, and I'd certainly never have guessed it on my own.

                 

                I have a follow-on problem, though. Here's my array:

                  <cfset dummyArray = ArrayNew(2)>
                  <cfset dummyArray[1] = ["Col1","Col2","Col3"]>
                  <cfset dummyArray[2] = ["a","2","1.23"]>
                  <cfset dummyArray[3] = ["b","42","0.456"]>

                 

                and then

                <cfscript>
                theSheetObj = SpreadsheetNew("report");
                SpreadsheetAddrows(theSheetObj, dummyArray,1,1);
                </cfscript>
                <cfspreadsheet action="write" overwrite="yes" filename="myfilename.xls"  name="theSheetObj">

                 

                Sadly, this gets me a spreadsheet with only one column, not three.

                Col1
                Col2
                Col3
                a
                2
                1.23
                b
                42

                0.456

                • 5. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
                  -==cfSearching==- Level 4

                  I do not see any mention in the documentation of how multi-dimensional arrays will be handled.  From your results - obviously not how you were expecting. But it is not an insurmountable problem. Just use single dimension arrays or use a query object.

                  • 6. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
                    JaneUK Level 1

                    Yes, I found ways round it, involving two nested loops - not insurmountable, as you say, just annoying. How would you go about converting that array to a query, though? Is there a simple one-call function, ArrayToQuery, or something? I looked, but couldn't find anything.

                    • 7. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
                      -==cfSearching==- Level 4

                      There is no ArrayToQuery function. But using queryAddColumn (once per column) is nearly as simple.

                       

                      http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7b 7c.html

                      • 8. Re: Populating a spreadsheet with an array using SpreadsheetAddrows
                        itisdesign Most Valuable Participant

                        Adam Cameron. wrote:

                         

                        I had hoped that spreadsheetAddRows could accept the array directly because some of the array items  contain commas and this creates problems for the spreadsheetAddRows  function since it utilizes commas as the delimiter for items. I simply replaced all the commas.

                         

                        All of the spreadsheet functionality is new to CF9 and hopefully Adobe will take the necessary steps to make these functions work as advertised.

                         

                        You could help 'em out by raising a bug for it: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html.

                         

                        If you post the bug URL back, I'll go vote for it too.

                         

                        --

                        Adam

                        Hi Adam and Greg,

                         

                        Just noting a bug was filed for this: https://bugbase.adobe.com/index.cfm?event=bug&id=3043160

                         

                        Just ran into this issue and realized, from my vote, that it wasn't the first time =P

                         

                        Thanks!,

                        -Aaron