SpreadsheetAddRow

Description

Adds a row to an Excel spreadsheet object.

Returns

Does not return a value.

Function syntax

SpreadsheetAddrow(spreadsheetObj, data [,row, column, 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 column.
row	The number of the row to insert. The row numbers of any existing rows with numbers equal to or greater than this value are incremented by one. If you specify a value for this parameter, you must also specify a value for column.If you omit this parameter the rows are inserted following the last current row, and you cannot specify a column.
column	The number of the column in which to add the column data. All columns in the row to the left of the start column have empty cells. If you specify a value for this parameter, you must also specify a value for row.
insert	This parameter is optional. The default value is true.A Boolean value specifying whether to insert a row. If false, the function replaces data in the specified row entries.
datatype	Possible datatypes of a cell in a row are STRING, NUMERIC, or DATE. You can use datatype as an expression to describe the type of data. Here are some examples of using the datatype parameter: STRING:1,3;NUMERIC:2: The data in the first and third columns of this row is of type string, and the data in the second column is of type numeric. NUMERIC:1;STRING: The data in the first column of this row is of type numeric. The rest is of type string. NUMERIC:2-5;STRING:1,6;DATE: The data in the second and fifth columns is of type numeric. The data in the first and the sixth columns is of type numeric. The rest is of type date. STRING: All cells in this row will are of type string.

Usage

Example

The following example adds a row of data as row 10. The data starts at column 2, and any existing row numbers 10 and higher 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 eighth row to the sheet, with data starting in column 1.
SpreadsheetAddRow(theSheet,"150,ENGL,95,Poetry 1",8,1);
</cfscript>


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

Example 2 | Using the datatype parameter

<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"];

/*//////////////////////// spreadSheetAddRow method : datatype parameter. /////////////////////////*/
/*// method signature : SpreadsheetAddrow(spreadsheetObj, data [,row, column, insert, datatype]) //*/
qry_xl_fl = expandpath("./") & "adrw-dt-ty_qry.xlsx";
xl_obj = spreadsheetNew("test-addrw-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] & ",";
		spreadSheetAddRow(xl_obj, rw_data_lst, r, 1, 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-adrw-xl");

</cfscript>