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:
- Log in with your Gmail or Facebook credentials.
- Navigate to the project in the left pane.
- Once you make some changes in any cfm in the project, a pop up displays asking you to save the project.
- 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. |
| 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:
|
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>
Sign in to your account