The Syncfusion .NET Excel Library (XlsIO) enables users to apply formatting to cells based on specific conditions dynamically. This feature improves the readability and automation of cell styles in the worksheet.
This example code shows how to apply conditional formatting to an Excel worksheet using the Syncfusion .NET Excel Library with just a few lines of code in C#.
// Initialize Excel engine and application
using ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
// Applying conditional formatting to "A1"
IConditionalFormats condition = worksheet.Range["A1"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
// Represents conditional format rule that the value in target range should be between 10 and 20
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
worksheet.Range["A1"].Text = "Enter a number between 10 and 20";
// Setting back color and font style to be applied for target range
condition1.BackColor = ExcelKnownColors.Light_orange;
condition1.IsBold = true;
condition1.IsItalic = true;
// Applying conditional formatting to "A3"
condition = worksheet.Range["A3"].ConditionalFormats;
IConditionalFormat condition2 = condition.AddCondition();
// Represents conditional format rule that the cell value should be 1000
condition2.FormatType = ExcelCFType.CellValue;
condition2.Operator = ExcelComparisonOperator.Equal;
condition2.FirstFormula = "1000";
worksheet.Range["A3"].Text = "Enter the Number as 1000";
// Setting fill pattern and back color to target range
condition2.FillPattern = ExcelPattern.LightUpwardDiagonal;
condition2.BackColor = ExcelKnownColors.Yellow;
// Applying conditional formatting to "A5"
condition = worksheet.Range["A5"].ConditionalFormats;
IConditionalFormat condition3 = condition.AddCondition();
// Setting conditional format rule that the cell value for target range should be less than or equal to 1000
condition3.FormatType = ExcelCFType.CellValue;
condition3.Operator = ExcelComparisonOperator.LessOrEqual;
condition3.FirstFormula = "1000";
worksheet.Range["A5"].Text = "Enter a Number which is less than or equal to 1000";
// Setting back color to target range
condition3.BackColor = ExcelKnownColors.Light_green;
// Save the Excel document
using FileStream outputStream = new FileStream("ConditionalFormat.xlsx", FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
Discover how the conditional formatting features in the Syncfusion Excel Library enhance data visualization by applying dynamic formatting rules to highlight key data based on specific conditions.
Apply conditional formatting to highlight specific cells based on criteria, enhancing data visibility by drawing attention to important values. This makes it easier to identify trends or outliers in your reports.
The top/bottom conditional formatting option emphasizes the highest and lowest values within a selected range, making it useful for quickly identifying top performers or areas that need attention, thereby aiding in data analysis.
Data bars visually represent the magnitude of values in cells by adding colored bars directly within the cells. This provides a quick, at-a-glance understanding of data distribution and comparison across your dataset.
Color scales allow you to apply a gradient color format to cells based on their values, enabling easy comparison and analysis. This helps users visualize data trends and variations effectively.
Icon sets enable you to add visual indicators, such as arrows or symbols, to cells based on their values. This enhances data presentation by providing immediate context and interpretation, making reports more intuitive.
Discover valuable resources from our blog and knowledge base to enhance your efficiency in using conditional formatting.
Knowledge base
Explore these resources for comprehensive guides, knowledge base articles, insightful blogs, and ebooks.
Product Updates
Technical Support
You need to select the maximum number of rows and column values, which are 1,048,576 rows and 16,384 columns, while creating a conditional format using the Syncfusion Excel Library.
The Syncfusion Excel Library supports 5 arrows, 5 arrows gray, 5 boxes,5 quarters, 5 ratings, 4 arrows,4 arrows gray, 4 ratings, 4 red to black, 4 traffic lights, 3 arrows, 3 arrows gray, 3 flags, 3 signs, 3 stars, 3 symbols, 3 symbols 2, 3 traffic lights 1, 3 traffic lights 2, 3 triangles icon sets for creating or editing conditional formatting in Excel documents.
Yes. You can use negative data bar conditional formatting in the Syncfusion Excel Library.
You can add formulas in conditional formatting using FirstFormula and SecondFormula properties using the Syncfusion Excel Library?
The Syncfusion Excel Library supports 2-color scale and 3-color scale types in conditional formatting.
The Syncfusion Excel Library supports context, right to left, and left to right data bar conditional formatting.
Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion® is proud to hold the following industry awards.