ColdFusion - spreadsheetaddrows errors out

Question

When trying to use the includecolumnnames parameter CF throw and error saying the spreadsheetaddrows function only supports 2 to 6 parameters, but documentation shows 7. Even the example in the spreadsheetaddrows documentation fails:

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>

WolfShade · Answer

I've never had luck with spreadsheetaddrows(). I prefer, instead, to use spreadsheetSetCellValue() or spreadsheetSetCellFormula(). It's more coding, but you get much more granular control.

While it means looping through your query and manually setting each cell, this can be easily achieved by using a loop within a loop. Just set a variable to a comma-delimited list for your column names, loop through the list to set your headers, then use a loop within a loop (first loop is the query; second is each column of current query iteration) to set the values.

NOTE: If you insert any values that are pure integer, this will still be entered as a string. You have to set the format, loop through the query to set values, then reformat.

HTH,

^_^

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.