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:
- 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.
spreadsheetObj(SpreadsheetObj, data[, startRow, startColumn, insert], datatype);
ColdFusion (2018 release): Changed Parameter name SpreadSheetAddColumn to spreadsheetObj.
ColdFusion 11: Added the datatype parameter.
ColdFusion 9: Added the function.
Parameter |
Description |
|---|---|
spreadsheetObj |
The Excel spreadsheet object to which to add the column. |
data |
A comma delimited |
startRow |
This parameter is optional.The number of the row at which to start adding the column data. If insert="true", all rows in the column above the start row have empty cells.If you omit this parameter the columns are inserted starting at the first row, following the last current column, and you cannot specify a column. |
startColumn |
This parameter is optional.The number of the column in which to add the column data. |
insert |
This parameter is optional.A Boolean value specifying whether to insert a column. If false, the function replaces data in the specified column entries. |
| datatype | Array of datatype expressions. For more information on expressions, see the datatype parameter description in the function SpreadsheetAddRow. |
The
<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3")>
<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3",2,3,false)>
The following example creates an Excel spreadsheet object from a query and inserts a new column 2, with data starting at row 3. The existing columns 2 and greater 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 second column to the sheet, with data starting in row 3.
SpreadsheetAddColumn(theSheet,
"Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,
Advanced"
,3,2,true);
</cfscript>
<!--- Write the spreadsheet to a file, replacing any existing file. --->
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheet=1 sheetname="courses" overwrite=true>
<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"];
/*///////////////////////////// spreadsheetAddColumn method : datatype parameter. //////////////////////////////*/
/*// method signature : SpreadsheetAddColumn(SpreadsheetObj, data[, startRow, startColumn, insert], datatype) //*/
qry_xl_fl = expandpath("./") & "adcol-dt-ty_qry.xlsx";
xl_obj = spreadsheetNew("test-adcol-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] & ",";
spreadSheetAddColumn(xl_obj, rw_data_lst, 1, r, 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-adcol-xl");
</cfscript>
Twitter™ and Facebook posts are not covered under the terms of Creative Commons.