Conditional formatting: highlighting alternating rows

Home Documentation Writing Reading Pictures1 Pictures2 Formulas DateTime 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;
}