• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

294

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

Votes

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,

^_^

Votes

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