SpreadsheetAddColumn

Last updated on Apr 27, 2021 06:02:55 AM GMT | Also Applies to ColdFusion

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

Syntax

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

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>