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 ExcelApplication.

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

Solution

In versions of ExcelApplication prior to 6.5.1, the only way to set alternating background colors on a table was to loop over all the rows you wanted to format and set their background colors one at a time. This can take a long time for large amounts of data, and requires quite a bit of code. With the introduction of the ConditionalFormat object in ExcelWriter 6.5.1, alternating row colors in a Range with the ExcelApplication object is much simpler and faster.

The first step is to choose colors for the odd and even rows. In this example I’ll color even-numbered rows a light blue and odd-numbered rows white. You can use any colors you like, as long as they exist in the Palette or you add them yourself.

The second step is to create the ConditionalFormat object and the conditions within it. Each Condition has a comparison rule that gets set up in the constructor and a Style object that will be applied to each cell for which that rule is true. To alternate row colors we need two Conditions: one that will format the odd-numbered rows and one for the even-numbered rows.

To do this we use the FormulaEvaluation comparison type and create formulas that evaluate to true depending on the numbering of the row. The MOD() function returns the remainder left after dividing its first argument by its second, and the ROW() function returns the 1-indexed number of the current cell’s row. Thus, MOD(ROW(), 2)=1 will be true for all odd-numbered cells, and MOD(ROW(), 2)=0 will be true for odd-numbered cells.

After creating the conditions, we change the formatting of the Style objects associated with them. For this example all we’ll do is change the background color, but we could apply any formatting that can be set with through the Style object. As in the example below, we suggest that you modify the existing Condition.Style object rather than creating a new Style object and setting Condition.Style to it, as some attributes are not preserved properly with the second method.

The final step is to create the Range this formatting should be applied to and associate the ConditionalFormat with the Range. The following code illustrates these steps and produces the screenshot below.


// Create the colors we'll use on alternating rows. In Excel you can only
// use colors in the Palette, so we need to create a Palette object and use
// that to get the colors.
Palette p = wb.Palette;
Color evenColor = p.GetClosestColor(153, 204, 255);
Color oddColor = p.GetClosestColor(255, 255, 255);

// Create the conditional format and its conditions. Each condition
// has a formula that can evaluate to true or false.
ConditionalFormat oddEvenColors = wb.CreateConditionalFormat();
Condition evenRows;
evenRows = oddEvenColors.CreateCondition(Condition.Comparison.FormulaEvaluation, "=MOD(ROW(), 2)=0");
Condition oddRows; oddRows = oddEvenColors.CreateCondition(Condition.Comparison.FormulaEvaluation, "=MOD(ROW(), 2)=1");

// Change the background color for each condition. This is what will change
// for the cells where the condition is true.
evenRows.Style.BackgroundColor = evenColor;
oddRows.Style.BackgroundColor = oddColor;

// Create the range we want to format and set the formatting.
Range toFormat = ws.CreateRange("A1:F10");
toFormat.SetConditionalFormat(oddEvenColors);

Note that the conditional formatting is applied rather than set. This means that only the differences between any existing styles and the conditional formatting will take effect. For example, if some of the cells in the range had a border or had changed the font size, those changes will not be affected when we change the background color. See our documentation for more on setting and applying styles.

Follow

Related posts: