Today I was trying to get CF9’s new spreadsheet functionality to stripe the rows in the spreadsheet I was generating.  From what I could find, there’s nothing inherent to the tag (cfspreadsheet) or any of the related functions to do this.  I find this a bit curious, but stewing over should-have-been’s doesn’t get the job done.  So I decided to roll my own solution.

The first thing to check out is the documentation for the spreadsheetformatrows() function.  This takes 3 arguments:

  • spreadsheetObj: This is the spreadsheet in which the rows exist
  • format: An object of appropriate formatting rules to apply to the selected rows
  • row: The rows to which the formatting specification is applied

The “row” argument is the most important.  While you can’t specify something like “please stripe the rows with these two formatting objects” (wish list), you can either specify a range of rows (e.g., “3-12”) or a list of rows (e.g., “3,5,7,9”).

Ah, so if we can use a list of rows, the answer becomes apparent: all we have to do is roll through the query and create some lists of odd and even numbers!

To do this, I created a simple function:

function getnumlist(start,end) {
     // create structure to hold odd and even number lists
     numbers = structnew();
     numbers.even = '';
     numbers.odd = '';
     // loop over total length of query, list, or whatever
     for(i=start;i<=end;i++) {
          // if even, add to "even" list
          if(i%2==0) {
                numbers.even = listappend(numbers.even,i);
          }
          // otherwise, add to "odd" list
          else {
                numbers.odd = listappend(numbers.odd,i);
          }
     }
     // return the goodness
     return numbers;
}

Pretty easy: just creates a simple structure to contain lists of odd and even numbers.

Now, onto the spreadsheet!!

// create a new spreadsheet with a default worksheet
excel = spreadsheetnew("About Me",true);
// specify format object for "odd" rows
oddformat = {};
     oddformat.fgcolor="white";
     oddformat.topborder="thin";
     oddformat.topbordercolor="grey_40_percent";
     oddformat.bottomborder="thin";
     oddformat.bottombordercolor="grey_40_percent";
     oddformat.leftborder="thin";
     oddformat.leftbordercolor="grey_40_percent";
     oddformat.rightborder="thin";
     oddformat.rightbordercolor="grey_40_percent";
// specify format object for "even" rows
evenformat = {};
     evenformat.fgcolor="grey_25_percent";
     evenformat.topborder="thin";
     evenformat.topbordercolor="grey_40_percent";
     evenformat.bottomborder="thin";
     evenformat.bottombordercolor="grey_40_percent";
     evenformat.leftborder="thin";
     evenformat.leftbordercolor="grey_40_percent";
     evenformat.rightborder="thin";
     evenformat.rightbordercolor="grey_40_percent";
// add some header columns to spreadsheet
spreadsheetaddcolumn(excel,"Name",1,1,false);
spreadsheetaddcolumn(excel,"Favorite Movie",1,2,false);
// add dynamic data to columns
spreadsheetaddcolumn(excel,quotedvaluelist(data.name),2,1,false);
spreadsheetaddcolumn(excel,quotedvaluelist(data.movie),2,2,false);
// set the last row
lastrow = data.recordcount+1;
// invoke getnumlist, starting from row 2 (non-header row), ending on last row (total count - 1)
numbers = getnumlist(2,lastrow);
// apply formatting to odd rows
spreadsheetformatrows(excel,oddformat,numbers.odd);
// apply formatting to even rows
spreadsheetformatrows(excel,evenformat,numbers.even);
// convert to binary so it can be pushed as a download
thefile = spreadsheetreadbinary(excel);

That’s about it. Now you have complete control over the odd/even striping of rows in Excel files generated with cfspreadsheet.  Hope you enjoy 🙂