Populating a spreadsheet with an array using SpreadsheetAddrows

[Gregory]

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

[Adam Cameron.]

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

[Gregory]

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

[Adam Cameron.]

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

[itisdesign]

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

[JaneUK]

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

[-__cfSearching__-]

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.

[JaneUK]

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.

[-__cfSearching__-]

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-7b7c.html