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:

  1. Log in with your Gmail or Facebook credentials.
  2. Navigate to the project in the left pane.
  3. Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
  4. Give the project a suitable name and click Save.


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





spreadsheetObj(SpreadsheetObj, data[, startRow, startColumn, insert], datatype);

See also


ColdFusion (2018 release): Changed Parameter name SpreadSheetAddColumn to spreadsheetObj.

ColdFusion 11: Added the datatype parameter.

ColdFusion 9: Added the function.





The Excel spreadsheet object to which to add the column.


A comma delimited  list  of cell entries, one per row being added to the column.


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.


This parameter is optional.The number of the column in which to add the column data.


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.


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. ---> 
name="courses" datasource="cfdocexamples" 
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 

///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"); 
//Insert a new second column to the sheet, with data starting in row 3. 

<!--- 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


// 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");

Adobe logo

Sign in to your account