本文将介绍通过使用Spire.XLS for .NET应用条件格式到Excel单元格数据的方法。示例中包含如下几种条件格式类型用于查找、筛选符合条件格式的数据。

• 1. AddAverageCondition(AverageType averageType) 应用于低于或高于平均值的数据
• 2. AddTopBottomCondition(TopBottomType topBottomType, int rank)应用于最高或最低值的数据
• 3. ConditionalFormatType.UniqueValues应用于唯一值数据
• 4. ConditionalFormatType.DuplicateValues应用于重复出现的数据
• 5. AddTimePeriodCondition(TimePeriodType timePeriodType)应用于满足条件格式日期的数据

//创建Workbook类的对象，并加载测试文档
Workbook wb = new Workbook();
wb.LoadFromFile("test.xlsx");

//获取指定工作表
Worksheet sheet= wb.Worksheets[0];

//添加条件格式1并指定数据范围
XlsConditionalFormats format1 = sheet.ConditionalFormats.Add();
format1.AddRange(sheet.Range["A2:A12"]);
//高亮低于平均数值的单元格
IConditionalFormat cf1 = format1.AddAverageCondition(AverageType.Below);
cf1.BackColor = Color.Lavender;
//高亮高于平均数值的单元格
IConditionalFormat cf2 = format1.AddAverageCondition(AverageType.Above);
cf2.BackColor = Color.LightBlue;

//添加条件格式2并指定数据范围
XlsConditionalFormats format2 = sheet.ConditionalFormats.Add();
format2.AddRange(sheet.Range["B2:B12"]);
//高亮最高值
IConditionalFormat cf3 = format2.AddTopBottomCondition(TopBottomType.Top, 1);
cf3.BackColor = Color.Green;
//高亮最低值单元格
IConditionalFormat cf4 = format2.AddTopBottomCondition(TopBottomType.Bottom, 1);
cf4.BackColor = Color.RosyBrown;

//添加条件格式3并指定数据范围
XlsConditionalFormats format3 = sheet.ConditionalFormats.Add();
format3.AddRange(sheet.Range["C2:C12"]);
//高亮唯一值的单元格
IConditionalFormat cf5 = format3.AddDuplicateValuesCondition();
cf5.FormatType = ConditionalFormatType.UniqueValues;
cf5.BackColor = Color.Cyan;

//添加条件格式4并指定数据范围
XlsConditionalFormats format4 = sheet.ConditionalFormats.Add();
format4.AddRange(sheet.Range["D2:D12"]);
//高亮重复数值的单元格
IConditionalFormat cf6 = format4.AddDuplicateValuesCondition();
cf6.FormatType = ConditionalFormatType.DuplicateValues;
cf6.BackColor = Color.Beige;

//添加条件格式5并指定数据范围
XlsConditionalFormats format5 = sheet.ConditionalFormats.Add();
format5.AddRange(sheet.Range["E2:E12"]);
//高亮本周日期的单元格
IConditionalFormat cf7 = format5.AddTimePeriodCondition(TimePeriodType.ThisWeek);
cf7.BackColor = Color.Orange;

//保存文档
wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);

'创建Workbook类的对象，并加载测试文档
Dim wb As New Workbook()
wb.LoadFromFile("test.xlsx")

'获取指定工作表
Dim sheet As Worksheet = wb.Worksheets(0)

'添加条件格式1并指定数据范围
Dim format1 As XlsConditionalFormats = sheet.ConditionalFormats.Add()
format1.AddRange(sheet.Range("A2:A12"))
'高亮低于平均数值的单元格
Dim cf1 As IConditionalFormat = format1.AddAverageCondition(AverageType.Below)
cf1.BackColor = Color.Lavender
'高亮高于平均数值的单元格
Dim cf2 As IConditionalFormat = format1.AddAverageCondition(AverageType.Above)
cf2.BackColor = Color.LightBlue

'添加条件格式2并指定数据范围
Dim format2 As XlsConditionalFormats = sheet.ConditionalFormats.Add()
format2.AddRange(sheet.Range("B2:B12"))
'高亮最高值
Dim cf3 As IConditionalFormat = format2.AddTopBottomCondition(TopBottomType.Top, 1)
cf3.BackColor = Color.Green
'高亮最低值单元格
Dim cf4 As IConditionalFormat = format2.AddTopBottomCondition(TopBottomType.Bottom, 1)
cf4.BackColor = Color.RosyBrown

'添加条件格式3并指定数据范围
Dim format3 As XlsConditionalFormats = sheet.ConditionalFormats.Add()
format3.AddRange(sheet.Range("C2:C12"))
'高亮唯一值的单元格
Dim cf5 As IConditionalFormat = format3.AddDuplicateValuesCondition()
cf5.FormatType = ConditionalFormatType.UniqueValues
cf5.BackColor = Color.Cyan

'添加条件格式4并指定数据范围
Dim format4 As XlsConditionalFormats = sheet.ConditionalFormats.Add()
format4.AddRange(sheet.Range("D2:D12"))
'高亮重复数值的单元格
Dim cf6 As IConditionalFormat = format4.AddDuplicateValuesCondition()
cf6.FormatType = ConditionalFormatType.DuplicateValues
cf6.BackColor = Color.Beige

'添加条件格式5并指定数据范围
Dim format5 As XlsConditionalFormats = sheet.ConditionalFormats.Add()
format5.AddRange(sheet.Range("E2:E12"))
'高亮本周日期的单元格
Dim cf7 As IConditionalFormat = format5.AddTimePeriodCondition(TimePeriodType.ThisWeek)
cf7.BackColor = Color.Orange

'保存文档
wb.SaveToFile("result.xlsx", ExcelVersion.Version2013)

条件格式应用效果：

在Excel中应用单元格值（ConditionalFormatType.CellValue）、公式（ConditionalFormatType.Formula）、图标集（ConditionalFormatType.IconSet）类型的条件格式可参阅这篇文章。