How can I define the number of rows on cfspreadsheet object?

Question

I am in the process of learning ColdFusion and I am trying to work with spreadsheets using spreadsheetFormatRows(spreadsheetObject, dataFormat, rangeOfRowsFormated)

How can I set the range to include all of the rows, except the header row, which is for column name? Is there a function that returns the number of the rows on cfspreadsheet object, so I can set the range to '2-rowCount'?

I tried spreadsheetFormatRows(theSheet, headerFormat, 2-50); and works fine and formats rows 2 to 50, but I don't want to have that hard-coded.

Thank you in advance.

Keep track of the number of rows as you populate them and save the value to a variable. Simpler yet, if they are query results, use the recordcount variable from cfquery. — Dan Bracuk, Mar 21 '17 at 21:31
Yes, it is a query and yes recordcount works perfect. Post it as an answer so I can marked it as the correct one for those one who comes to read the post later. Thank you. — DoArNa, Mar 21 '17 at 22:57

Twillen · Accepted Answer · 2017-03-22T18:05:07.727

4

The spreadsheet object has an attribute rowcount. You can do spreadsheetFormatRows(theSheet, format, "2-#theSheet.rowCount#");

<cfscript>
    mySheet = spreadSheetNew("My Sheet");
    spreadSheetAddRow(mySheet, "'Col. A','Col. B','Col. C'");
    for(i=1; i <= RandRange(1, 100); i++){
        spreadSheetAddRow(mySheet, "'Row A#i#','Row B#i#','Row C#i#'");
    }
    spreadSheetFormatRow(mySheet, {bold = true, fontsize = 24}, 1);
    spreadSheetFormatRows(mySheet, {fontsize = 16}, "2-#mySheet.rowcount#");
    cfheader(name = "Content-Disposition", value = 'inline; fileName="test.xls"');
    cfcontent(type="application/vnd.ms-excel", variable="#spreadSheetReadBinary(mySheet)#");
</cfscript>

Try Online

edited Mar 22 '17 at 18:05

answered Mar 22 '17 at 13:05

Twillen

1,458
15
22

Would you not have to add 1 to format the last row? – Dan Bracuk Mar 22 '17 at 16:47
@DanBracuk In the spreadsheet, header row/s are like any other row. If you have a data set of 25 items and 1 header row, you have 26 rows in your spreadsheet so 2-26. We do have a hard coded offset for the start of the formated range which is the same as 1+headers row count. – Twillen Mar 22 '17 at 17:46
So editing your answer will in fact improve it then. – Dan Bracuk Mar 22 '17 at 19:26

score 2 · Answer 2 · answered Mar 22 '17 at 03:00

2

Keep track of the number of rows as you populate them and save the value to a variable. Simpler yet, if they are query results, use the recordcount variable from cfquery.

Remember to add 1 so you format the last row.

answered Mar 22 '17 at 03:00

Dan Bracuk

20,699
4
26
43

How can I define the number of rows on cfspreadsheet object?

2 Answers2