spreadsheetAddRows

Adds multiple rows from a query or array to an Excel spreadsheet object.

spreadsheetAddRows(spreadsheetObj, data,[ row, column , insert, datatype, includeColumnNames]) → returns void

spreadsheetAddRows Argument Reference

spreadsheetObj variableName
Required

The spreadsheet object variable

data any
Required

A query or array

row numeric

The row number in the spreadsheet at which to insert the data. If omitted rows are appended.

column numeric

The column number to start, all columns to the left will be empty.

insert boolean
Default: true

When true appends the row data to the spreadsheetObj. When false attempts to update the spreadsheet object rows.
Values:
  • true
  • false

datatype array

CF 11+ An array of datatype expressions with values STRING NUMERIC or DATE. For example use `DATE:1;NUMERIC:2-2;STRING

includeColumnNames boolean
Default: false

CF 2016+ When true writes column names as headers in the spreadsheet.

Examples sample code invoking the spreadsheetAddRows function


Adding rows to a spreadsheet from a Query

<cfset q = queryNew("name,beers",
    "varchar,integer",
    [ {"name":"John","beers":2}, {"name":"Pete","beers":1} ])> 

<!--- Make a spreadsheet object ---> 
<cfset s = spreadsheetNew()> 
<!--- Add header row ---> 
<cfset spreadsheetAddRow(s, "Name,Beers")> 
<!--- Add query ---> 
<cfset spreadsheetAddRows(s, q)> 
<cfdump var="#s#" />

CF2016: Adding rows to a spreadsheet from a Query using column names as headers

You need to state the row, column, insert and datatype arguments before you can change includeColumnNames to true as ACF internal functions don't allow named arguments.

<cfset q = queryNew("name,beers",
    "varchar,integer",
    [ {"name":"John","beers":2}, {"name":"Pete","beers":1} ])> 

<!--- Make a spreadsheet object ---> 
<cfset s = spreadsheetNew()> 
<!--- Add query and preserve column names ---> 
<cfset spreadsheetAddrows(s,q,1,1,true,[""],true)> 
<cfdump var="#s#" />

Fork me on GitHub