SpreadsheetAddRows

Description

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

Returns

Does not return a value.

Category

Microsoft Office Integration

Function syntax

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

See also

SpreadsheetAddColumn, SpreadsheetAddImage, SpreadsheetAddRow, SpreadsheetDeleteRow, SpreadsheetDeleteRows, SpreadsheetFormatRow, SpreadsheetFormatRows, SpreadsheetShiftRows

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 or an array.

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 specify a value for this parameter, you must also specify a value for column.

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. If you specify a value for this parameter, you must also specify a value for row.

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.

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);SpreadsheetAddRows(theSheet,["1,a", "2,B,b"]); 
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>