ColdFusion 9.0 Resources

SpreadsheetAddRows

Description

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

Returns

Does not return a value.

Function syntax

SpreadsheetAddrow(spreadsheetObj, data[, row, column, insert])

History

ColdFusion 9: Added the function.

Parameters

Parameter	Description
`spreadsheetObj`	The Excel spreadsheet object to which to add the column.
`data`	A query object with the row data.
`row`	The number of the row at which to insert the rows. The row numbers of any existing rows with numbers equal to or greater than this value are incremented by number of added rows. If you omit this parameter the rows are inserted following the last current row.
`column`	The number of the column in which to add the column data. All columns in the row to the left of the start column have empty cells.
`insert`	This parameter is optional. The default value is `true`. A Boolean value specifying whether to insert a row. If `false`, the function replaces data in the specified row entries.

Usage

Example

The following example creates a spreadsheet by creating a new Excel spreadsheet object and using the AddRows function to add the data from a query.

<!--- Get the spreadsheet data as a query. ---> 
<cfquery 
       name="courses" datasource="cfdocexamples" 
       cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
       SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
       FROM COURSELIST 
</cfquery> 
 
<cfscript> 
    ///We need an absolute path, so get the current directory path. 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & courses.xls"; 
    //Create a new Excel spreadsheet object and add the query data. 
    theSheet = SpreadsheetNew("CourseData"); 
 
    SpreadsheetAddRows(theSheet,courses); 
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>