ColdFusion - spreadsheetaddrows errors out

New Here ,
Apr 27, 2016 Apr 27, 2016

Copy link to clipboard

Copied

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>

Views

217

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Employee ,
Apr 27, 2016 Apr 27, 2016

Copy link to clipboard

Copied

Moving to ColdFusion

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 28, 2016 Apr 28, 2016

Copy link to clipboard

Copied

LATEST

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines