cfspreadsheet cannot add rows to object created using query result

Question

'excelFileQuery' contains a query.

<cfset spreadsheetAddRows(s, excelFileQuery)>
<cfset spreadsheetAddRows(s,"#volunteeralias# Export Report (#dateformat(now(),'mmmm d, yyyy')# at #timeformat(now(),'h:mm tt')#)",1,1)>
<cfheader name="content-disposition" value="attachment; filename=myexcel.xlsx">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

But when the file is generated, on the query rows are showing up, not the second row. Am I doing it wrong?

You might find it easier to append the extra data to your query object instead of the spreadsheet. — Dan Bracuk, Feb 24 '14 at 13:55
@DanBracuk yes. I was following that procedure. But was just thinking about avoid creating new query and iterating data into it, so that the performance is better. Thanks for your help. — rrk, Feb 25 '14 at 03:17

score 7 · Accepted Answer · answered Feb 24 '14 at 13:27

I am not quite sure about your goal, but spreadSheetAddRows expects a query or an array. So the second statement should pass in an array, not a string. If you prefer to use a string, you must use the spreadsheetSetCellValue function instead.

If your goal is to append that last string after the query results, then convert the string to an array first. Also, remove the row/column number so the data is appended to the first empty row after the query results.

<cfset spreadsheetAddRows(s, excelFileQuery)>
<cfset spreadsheetAddRows(s, ["#volunteeralias# Export Report (#dateformat(now(),'mmmm d, yyyy')# at #timeformat(now(),'h:mm tt')#)"] )>
...

If you want to prepend it (as a header), do the same thing, but obviously you swap the two statements.

cfspreadsheet cannot add rows to object created using query result

1 Answers1