SpreadsheetAddColumn

Description

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

Returns

Does not return a value.

Function syntax

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

History

ColdFusion 11: Added the datatype attribute.

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:

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

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.

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


<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheet=1 sheetname="courses" overwrite=true>

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

SpreadsheetAddColumn

Applies to