Whatever message this page gives is out now! Go check it out!
SpreadsheetAddColumn
Last update:
May 18, 2026
Note: 
You can find the CFFiddle demo of this function and other spreadsheet functions as part of a project that is shared with you.
Click the button below to launch CFFiddle.
To copy the project in your workspace in CFFiddle, follow the steps below:
- Log in with your Gmail or Facebook credentials.
- Navigate to the project in the left pane.
- Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
- Give the project a suitable name and click Save.
Description
Adds a column or column data to an Excel spreadsheet object.
Returns
Nothing
Category
Syntax
spreadsheetObj(SpreadsheetObj, data[, startRow, startColumn, insert], datatype);See also
History
ColdFusion (2018 release): Changed Parameter name SpreadSheetAddColumn to spreadsheetObj.
ColdFusion 11: Added the datatype parameter.
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. |
| datatype | Array of datatype expressions. For more information on expressions, see the datatype parameter description in the function SpreadsheetAddRow. |
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 1
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>Example 2 | Using the datatypeparameter
<cfscript>
// create a query with data to write an excel file.
q_data = QueryNew("n1, n2, n3, s4, s5, d6", "",
[
{n1:"1.203E+4", n2:"1.230E4", n3:"103E4", s4:"1.203E+4", s5:"1.230E4", d6:"4 jan 15"},
{n1:"1.203E+4", n2:"1.230E4", n3:"103E4", s4:"01.203E+4", s5:"9.230E4", d6:"15 dec 15"}
]);
cfdump(var="#q_data#", format="html", label="qry-data");
datatype_arr = ["NUMERIC:1-3; STRING:4,5; DATE:6","STRING:1-3; NUMERIC:4,5; STRING:6"];
/*///////////////////////////// spreadsheetAddColumn method : datatype parameter. //////////////////////////////*/
/*// method signature : SpreadsheetAddColumn(SpreadsheetObj, data[, startRow, startColumn, insert], datatype) //*/
qry_xl_fl = expandpath("./") & "adcol-dt-ty_qry.xlsx";
xl_obj = spreadsheetNew("test-adcol-dty", true);
q_cl_lst = listsort(q_data.columnlist, "TextNoCase", "DESC");
for( r=1; r LTE q_data.recordcount; r++)
{
rw_data_lst = "";
for(c=1; c LTE listLen(q_cl_lst); c++)
rw_data_lst = rw_data_lst & q_data[listGetAt(q_cl_lst, c)][r] & ",";
spreadSheetAddColumn(xl_obj, rw_data_lst, 1, r, true, "NUMERIC:1-3; STRING:4,5; DATE:" );
}
spreadsheetwrite(xl_obj, qry_xl_fl, "", true, datatype_arr); //pass an empty str for password parameter.
cfspreadsheet( action="read", src=qry_xl_fl, query="q_data_out");
cfdump(var="#q_data_out#", format="html", label="data-read-from-adcol-xl");
</cfscript>
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Share additional feedback to help us improve.
0/255
|
