Can I add a spreadsheet row and then format it?

Question

I'm using cfspreadsheet to generate an excel spreadsheet. I'm adding rows one by one. Immediately after I add the row, I want to format it. Something like this:

<cfset SpreadsheetAddRow(mySpreadsheet, "hi,this,is,a,test") />
<cfset SpreadsheetFormatRow(mySpreadsheet, 
   {
      fgcolor:red;
   }) />

However, for the formatrow function, you have to provide a row number. Is there any way to format the row I just added without keeping a running counter of what row I'm up to?

score 5 · Accepted Answer · answered May 08 '12 at 12:51

5

The spreadsheet object itself knows how many rows are in it, similar to a query object.

<cfset CurrentRow = mySpreadsheet.RowCount />

Updating your example so that it works in ACF9:

<cfset SpreadsheetFormatRow(mySpreadsheet, 
   {
      fgcolor = 'red'
   }, mySpreadSheet.RowCount ) />

answered May 08 '12 at 12:51

Busches

1,964
16
19

3

Keep in mind `RowCount` returns the overall row count, while `SpreadSheetAddRows(sheet, data)` works relative to the last populated row. So you must still account for any offset (ie if starting from row other than 1) or gaps between rows. – Leigh May 08 '12 at 14:09
While this is true, the OP stated they're adding the rows one by one, so this solution works for them. – Busches May 08 '12 at 18:18
Quite possibly. Starting at row 1 would be my assumption, but they do not state that explicitly. So the code may or may not work as is. Knowing how to adjust the logic is good information for them (as well as future readers) to have :) – Leigh May 08 '12 at 18:28
I'm not starting at row 1 :) There's a header row first. But I can easily use this and just add 1... Thanks! – froadie May 08 '12 at 18:29
For some reason this didn't work :( I ended up just using a row counter – froadie May 08 '12 at 20:35
@froadie - Adding a counter is simple enough. But I am a little curious why it did not work. Feel free to post the code you tried. Perhaps we can spot the problem. – Leigh May 08 '12 at 20:50
@Leigh - I tried this exact code, and it just didn't show up at all. – froadie May 08 '12 at 21:01
@froadie - The snippet works fine for me. That suggests something about your actual code is different. – Leigh May 08 '12 at 22:46
I tested with this further, and it's working for me now... Thanks! I'm wondering if a rowCounter may be clearer code, but I'm going to accept this as it answers the question – froadie May 09 '12 at 06:22

Can I add a spreadsheet row and then format it?

1 Answers1