ColdFusion - spreadsheetaddrows errors out

[jgram]

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:

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

[GauravM]

Moving to ColdFusion

[WolfShade]

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,

^_^