SpreadsheetAddColumn

spreadsheetObj(SpreadsheetObj, data[, startRow, startColumn, insert], datatype);

<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3")>

<cfset spreadsheetAddColumn(SpreadsheetObj,"newcol1,newcol2,newcol3",2,3,false)>

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