We always use conditional formatting to highlight the cells with certain color from the whole data in the Excel worksheet. Spire.XLS also supports to create a formula to apply conditional formatting in Excel in C#. This article will show you how to apply a conditional formatting rule.
View the steps of Microsoft Excel set the conditional formatting.
Step 1: Choose the column B and then click "New Rule" under "Conditional Formatting"
Step 2: Select the rule type, enter the rule by adding the formula and then add the highlight color for the format.
Here comes to the steps of how to set the conditional formatting rule by Spire.XLS in C#.
Step 1: Create a new excel workbook and load the document from file.
Workbook wb = new Workbook();
wb.LoadFromFile("Test.xlsx");
Step 2: Get the first worksheet and the second column from the workbook.
Worksheet sheet = wb.Worksheets[0]; CellRange range = sheet.Columns[1];
Step 3: Set the conditional formatting formula and apply the rule to the chosen cell range.
XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add(); xcfs.AddRange(range); IConditionalFormat conditional = xcfs.AddCondition(); conditional.FormatType = ConditionalFormatType.Formula; conditional.FirstFormula = "=($B1<$C1)"; conditional.BackKnownColor = ExcelColors.Yellow;
Step 4: Save the document to file.
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
Effective screenshot:
Full codes:
using Spire.Xls;
namespace CreateFormula
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
wb.LoadFromFile("Test.xlsx");
Worksheet sheet = wb.Worksheets[0];
CellRange range = sheet.Columns[1];
XlsConditionalFormats xcfs = sheet.ConditionalFormats.Add();
xcfs.AddRange(range);
IConditionalFormat conditional = xcfs.AddCondition();
conditional.FormatType = ConditionalFormatType.Formula;
conditional.FirstFormula = "=($B1<$C1)";
conditional.BackKnownColor = ExcelColors.Yellow;
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace CreateFormula
Class Program
Private Shared Sub Main(args As String())
Dim wb As New Workbook()
wb.LoadFromFile("Test.xlsx")
Dim sheet As Worksheet = wb.Worksheets(0)
Dim range As CellRange = sheet.Columns(1)
Dim xcfs As XlsConditionalFormats = sheet.ConditionalFormats.Add()
xcfs.AddRange(range)
Dim conditional As IConditionalFormat = xcfs.AddCondition()
conditional.FormatType = ConditionalFormatType.Formula
conditional.FirstFormula = "=($B1<$C1)"
conditional.BackKnownColor = ExcelColors.Yellow
wb.SaveToFile("result.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
