SpreadsheetAddRows

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

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

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