c#/vb.net 应用条件格式到 excel 单元格数据-凯发线上登陆下载网址

本文将介绍通过使用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）类型的条件格式可参阅。