Conditional formatting: highlighting alternating rows

Home Documentation Writing Reading Pictures1 Pictures2 Formulas Date/Time SheetByName
Merging Grouping InsertRowCol NumFormats Formats Fonts Buffer1 Buffer2 Copying TopNFilter
Sorting StringFilter NumberFilter FilterByValues Protection Replacing RichString BeginWith
ColorScale OpRule AltRows

Conditional formatting: highlighting alternating rows

This example highlights alternating rows (banded rows) and make the data in a worksheet easier to scan. It's possible to do this with the formula expression "=MOD(ROW(),2)=0" in a conditional formatting rule. If you want to alternate columns please use this formula: "=MOD(COLUMN(),2)=0".

#include "libxl.h"

using namespace libxl;

int main()
{
    Book* book = xlCreateXMLBook();  

    book->setRgbMode();
    
    Sheet* sheet = book->addSheet(L"my");

    ConditionalFormat* cFormat = book->addConditionalFormat();
    cFormat->setFillPattern(FILLPATTERN_SOLID);
    cFormat->setPatternBackgroundColor(book->colorPack(240, 240, 240));

    ConditionalFormatting* cFormatting = sheet->addConditionalFormatting();
    cFormatting->addRange(4, 20, 1, 10);
    cFormatting->addRule(CFORMAT_EXPRESSION, cFormat, L"=MOD(ROW(),2)=0");

    for (int row = 4; row <= 20; ++row)
        for (int col = 1; col <= 10; ++col) sheet->writeNum(row, col, (double)row + (double)col);
   
    book->save(L"alternatingrows.xlsx");

    book->release();
    
    return 0;
}