Problem

A common way to display data in Excel is to alternate the background color of every other row when displaying a large table of data. With ExcelWriter there are multiple ways to accomplish this. This post covers some possible ways to apply alternating row colors with ExcelTemplate.

There is another post that discusses how to do this with ExcelApplication.

Solution

Option 1: Format as Table in Excel 2007/2010

Starting in Excel 2007, Excel provides pre-formatted table styles which already contain alternating row or column colors. This is the easiest way to format your data with alternating row colors. Note: these table styles may not render properly in Excel 2003 or in the XLS file format.

To format an area of cells as a table:

1. Highlight the area of cells.

2. Go to Format as Table in the ribbon.

3. Select a table style from the available styles.

4. If you chose to include your table header row, make sure to check off “My table has headers” in the confirmation dialog.

There are basic options for modifying the banding patterns:

You can also create new table styles:

You can do this for ExcelTemplate templates:

When the rows of data are inserted, the color banding will be applied:

Option 2: Use Conditional Formatting

The other approach is to use conditional formatting in the template to achieve alternating row colors. This may be more appropriate if you are not certain if your end-users will have Excel 2007/2010.

1. Create an ExcelWriter template with data markers in Excel.

2. Highlight the cells with data markers that correspond to the data you wish to display with alternating background colors.

3. From the menu, choose Format>Conditional Formatting. The formatting you define for this row will be applied to every new row that will be inserted by ExcelTemplate at runtime.

4. First define the formatting for even rows. In the “Condition1” field, choose “Formula Is” and in the formula field, type the following formula:

 =MOD(ROW(),2) = 0 

This formula uses the MOD( ) function to determine if the number of the current row (returned by the ROW( ) function) can be evenly divided by 2.

5. Click on the “format” button.

6. Click on the “patterns” tab and select a background color.

7. Now set a condition for odd rows, by clicking “ADD” and following the same steps as above but with a different formula:

 =MOD(ROW(),2) = 1 

8. Save the template and use it in your ExcelWriter application.

When ExcelTemplate imports new rows of data, the conditional formatting will also be applied to all the new rows:

Follow

Related posts: