SpreadsheetAddRow

Description

Adds a row to an Excel spreadsheet object.

Returns

Does not return a value.

Category

Microsoft Office Integration

Function syntax

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

See also

SpreadsheetAddColumn, SpreadsheetAddImage, SpreadsheetAddRows, 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 comma delimited list of cell entries, one per column.

row

The number of the row to insert. The row numbers of any existing rows with numbers equal to or greater than this value are incremented by one. 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, and you cannot specify a column.

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.

Usage

Example

The following example adds a row of data as row 10. The data starts at column 2, and any existing row numbers 10 and higher increment by one.

<!--- 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 Spreadsheet object and add the query data. 
    theSheet = SpreadsheetNew("CourseData"); 
 
    SpreadsheetAddRows(theSheet,courses); 
    //Insert a new eighth row to the sheet, with data starting in column 1. 
    SpreadsheetAddRow(theSheet,"150,ENGL,95,Poetry 1",8,1); 
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheet=1 sheetname="courses" overwrite=true>