Whatever message this page gives is out now! Go check it out!

SpreadsheetAddRows

Last update:
May 18, 2026
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:
  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.

Description

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

Returns

Does not return a value.

Category

Spreadsheet functions, Microsoft Office integration functions

Function syntax

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

See also

SpreadsheetAddColumn, SpreadsheetAddImage, SpreadsheetAddRow, SpreadsheetDeleteRow, SpreadsheetDeleteRows, 
SpreadsheetFormatRow, SpreadsheetFormatRows, SpreadsheetShiftRows

History

  • ColdFusion (2018 release): Spreadsheet object as an array is added.
  • ColdFusion (2016 release): Added the includeColumnNames parameter.
  • 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 query object with the row data or an array of literal values.
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.
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.
includeColumnNames
(Optional) A boolean value that enables or disables writing of column headers to spreadsheet object. By default, the value is false.

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.
<cfscript>
       // Get data from a query
       ArtOrders=QueryExecute("SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders
       ORDER BY orderid",[],{datasource="cfartgallery"});
       // Create a spreadsheet object with sheetname Art
       spObj=spreadsheetNew("Art",true);
       // Add rows with data from query result. The data start from row 1, col 1. The spreadsheet 
       // will have column names.
       spreadsheetAddrows(spObj,ArtOrders,1,1,true,[""],true);
       dirName=GetDirectoryFromPath(GetCurrentTemplatePath());
       // Write spreadsheet to a file
       SpreadsheetWrite(spObj,"#dirName#addrows.xlsx",true);
</cfscript>

Output

Figure: spreadsheetaddrows output
spreadsheetaddrows output

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

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

Example using the includeColumnNames parameter

<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:
Figure: Using includeColumnNames parameter
Using includeColumnNames parameter

Share this page

Was this page helpful?
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Thank you for your feedback. Your response will help improve this page.

On this page