Skip to main content

Color Alternating Rows Upon Output in Excel


Use ForEach/EndForEach, Out Tags and MS Office's native formatting to create a simple table that will have colored alternating rows when you Output. 


This feature should be used when it is desired to create Excel output with alternate row shading.  Note that in Microsoft Word, this is handled natively by the alternating row color table formatting.



For information about the tags used in this article, see ForEach and EndForEach, and Out Tag reference pages.


The directions below will show you how to reference individual data items in the for data set that you select; this is called array referencing. For more information, see Array Referencing


To make the rows alternate, we need to create 2 rows of identical Out Tags, modify the ForEach Tag so that it returns data in increments of 2, and then modify the Out Tags' increments to be 0 and 1 (the first row is 0, the second row is 1). Once you have formatted the template as you like it, run the report.

Step 1

In Excel insert your ForEach and EndForEach Tags, then create two rows in the table with identical Out Tags referencing your data within the ForEach loop -- as opposed to a single row (which is the usual format). Note that the first row has been colored grey, the second has been left unformatted.


Tip! The example above shows ForEach tags that have been placed in column A, which will appear as an empty column when you OutPut. If you don't want an empty column, you can put the beginning ForEach Tag into the same cell as your first Out Tag (in this case B2) and you can place the EndForEach Tag in cell in the first cell outside your tagged table (in this case B4).  Of course, if you were starting in column A, the cells used would be:

A2: [ForEach Tag][Out Tag]

B2: [Out Tag]

A3: [Out Tag] B3: [Out
A4: [EndForEach Tag]   


Step 2 

Next, you will need to alter the ForEach Tag using the Tag Editor. Double click the tag to bring up the Tag Builder, then double click on 'Advanced.'  Set the attribute 'step' to equal '2' so that the ForEach Tag increments by 2 rows each time, instead of 1 row (1 row increment is the default).


NOTE:  If you Output an odd number of rows, and the attribute is set to increments of 2, you will end up with one empty row.  To prevent this you can use an If Tag. In the template attached, an If Tag has been placed around the Out Tag printing the row number as an example of how to test if it is the last row using the forEach varStatus variable with the option ${forEachStatusVar.last} == 'False'.  This will print the Out Tag as long as the data item is not on the last row.


See this sample template as an example.

Step 3

Now modify the select statement of each of the Out Tags by changing the increments.  (See ForEach Tag Reference article for more on Row Access and numbering)

Out Tags on Row 2 (first row)

In each cell for the first row, edit the Out Tag's select statement to read as follows:

  • =autotag("wr:out select='${varName1[0].FirstName}'nickname='FirstName]' datasource='MSSQL'/>  
  • =autotag("wr:out select='${varName1[0].LastName}'nickname='LastName]' datasource='MSSQL'/>  




Out Tags on Row 3 (second row)

In each cell for the seconds row, edit the Out Tag's select statement to read as follows:

  • =autotag("wr:out select='${varName1[1].FirstName}'nickname='FirstName]' datasource='MSSQL'/>  
  • =autotag("wr:out select='${varName1[1].LastName}'nickname='LastName]' datasource='MSSQL'/>  


Step 4

Finally, shade one row differently in your template. You can also add a header row, borders, etc. When you Output, the color pattern you set up in the two rows will alternate. 


Output Report with formatting