SpreadsheetAddColumn

Description

Adds a column or column data to an Excel spreadsheet object.

Returns

Does not return a value.

Category

Microsoft Office Integration

Function syntax

SpreadsheetAddColumn(SpreadsheetObj, data[, startRow, startColumn, insert]);

See also

SpreadsheetAddImage, SpreadsheetAddRow, SpreadsheetAddRows, SpreadsheetDeleteColumn, SpreadsheetDeleteColumns, SpreadsheetFormatColumn, SpreadsheetFormatColumns, SpreadsheetShiftColumns

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 row being added to the column.

startRow

This parameter is optional.

The number of the row at which to start adding the column data. If insert="true", all rows in the column above the start row have empty cells.

If you omit this parameter the columns are inserted starting at the first row, following the last current column, and you cannot specify a column.

startColumn

This parameter is optional.

The number of the column in which to add the column data.

insert

This parameter is optional.

A Boolean value specifying whether to insert a column. If false, the function replaces data in the specified column entries.

Usage

The spreadsheetaddcolumn function can accept either two or five arguments.

You can specify the spreadsheetaddcolumn function using two parameters as follows:

<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3")>

You can specify the spreadsheetaddcolumn function using five parameters as follows:

<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3",2,3,false)>

Example

The following example creates an Excel spreadsheet object from a query and inserts a new column 2, with data starting at row 3. The existing columns 2 and greater 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 second column to the sheet, with data starting in row 3. 
    SpreadsheetAddColumn(theSheet, 
    "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced" 
    ,3,2,true); 
</cfscript> 
 
<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheet=1 sheetname="courses" overwrite=true>