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

SpreadsheetAddRow

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 a row to an Excel spreadsheet object.

Returns

Does not return a value.

Category

Microsoft Office Integration

Function syntax

SpreadsheetAddrow(spreadsheetObj, data [,row, column, insert, datatype])

See also

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

History

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 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.
datatypePossible 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:
  1. 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.
  2. NUMERIC: 1;STRING : The data in the first column of this row is of type numeric. The rest is of type string.
  3. 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.
  4. 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.
<!--- 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 eighth row to the sheet, with data starting in column 1. 

SpreadsheetAddRow(theSheet,"150,ENGL,95,Poetry 1",8,1); 

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

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