Custom Expression SpreadsheetGear API Sample

Range Samples Conditional Formats Custom Expression

Description

Demonstrates using a custom Expression-based Conditional Format to highlight data rows based on whether the Expression evaluates to true or false. In this case, all versions of .NET that were released in the month of November for any given year will be highlighted.

Sample Source Code

// Open workbook with a data range that will be formatted with a conditional format.
SpreadsheetGear.IWorkbook workbook = 
    SpreadsheetGear.Factory.GetWorkbook(@"Files/CustomExpressionSampleData.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;

// Get range that Conditional Formatting will be applied to.
SpreadsheetGear.IRange dataRange = worksheet.Cells["A2:B20"];

// Create an Expression-based conditional format that locates any release date
// in November.
SpreadsheetGear.IFormatCondition cf = dataRange.FormatConditions.Add(
    SpreadsheetGear.FormatConditionType.Expression,
    SpreadsheetGear.FormatConditionOperator.None, @"=ISNUMBER(SEARCH(""November"",$B2))",
    null);

// For every other row, apply a highlight-like Theme Color
cf.Interior.ThemeColor = SpreadsheetGear.Themes.ColorSchemeIndex.Accent4;
cf.Interior.TintAndShade = 0.8;  // 80% lighter