SpreadsheetAddRows

Description

Adds multiple rows from a query to an Excel spreadsheet object.

Returns

Does not return a value.

Function syntax

SpreadsheetAddrows(spreadsheetObj, data [, row, column, insert, datatype, includeColumnNames])

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 query object with the row data or an array.
row	(Optional) The number of the row at which to insert the rows. The row numbers of any existing rows with numbers equal to or greater than this value are incremented by number of added rows. 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.
includeColumnNames	(Optional) A boolean value that enables or disables writing of column headers to spreadsheet object. By default, the value is false.
column	(Optional) 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	(Optional) 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	(Optional) Array of datatype expressions. For more information on expressions, see the datatype parameter description in the function SpreadsheetAddRow.

Example

The following example creates a spreadsheet by creating a new Excel spreadsheet object and using the AddRows function to add the data from 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 Excel spreadsheet object and add the query data.
theSheet = SpreadsheetNew("CourseData");

SpreadsheetAddRows(theSheet,courses);
SpreadsheetAddRows(theSheet,["1,a", "2,B,b"]);
</cfscript>


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

The following example shows the usage of 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");

 /*//////////////////////// spreadSheetAddRows method : datatype parameter. /////////////////////////*/
 /*// method signature :	SpreadsheetAddrows(spreadsheetObj, data[, row, column, insert], datatype) //*/

qry_xl_fl = expandpath("./") & "adrws-dt-ty_qry.xlsx";
xl_obj = spreadsheetNew("test-addrws-dty", true);
datatype_arr = ["NUMERIC:1-3; STRING:4,5; DATE:6","STRING:1-3; NUMERIC:4,5; STRING:6"];
spreadSheetAddRows(xl_obj, q_data, 1, 1, true, datatype_arr );

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

<cfscript>
	out_fl = "#expandpath("./")#header.xlsx";
        xlobj = SpreadsheetNew("2d_arr_data", true); 
        qry_data = queryNew("product, customer, qtr");
        queryAddRow(qry_data, {product:"aniseed syrup", customer="annie", qtr="1"});
        queryAddRow(qry_data, {product:"camembert pierrot", customer="pierre", qtr="2"});
        queryAddRow(qry_data, {product:"scones", customer="connie", qtr="4"});
	datatype = [""];
        spreadsheetAddRows(xlobj, qry_data,1,1,"true",datatype,true);
        cfspreadsheet(action="write", filename="#out_fl#", name="xlobj", overwrite=true);
        cfspreadsheet(action="read", src="#out_fl#", query="qryxl");
        writeDump(qryxl);
</cfscript>

In the above example, the output is header.xlsx, where the file reads the column headers, product, customer, and qtr. Under each column header are the row values corresponding to each column. The output is shown below:

SpreadsheetAddRows

Applies to

Description

Returns

Category

Function syntax

See also

History

Parameters

Example

Example using the includeColumnNames parameter