Excel中条件格式是指：根据条件使用数据条、色阶和图标集，以突出显示相关单元格，强调异常值，以及实现数据的可视化效果。条件格式功能是进阶版的筛选，即先设置条件选出符合某些条件的数据，随后赋予其特定格式。鉴于Excel在处理数据方面有着天然的优势，条件格式是我们必须掌握的一项技巧。此文旨在介绍如何使用我司免费版产品Free Spire.XLS在C#中实现Excel条件格式的设置。
友情提示：可以从以下三个资源站点下载使用Free Spire.XLS: 官网E-iceblue; 下载完成后，请将下载包Bin文件夹的.Dll添加作为Visual Studio的引用。使用Spire.XLS可独立创建Excel文件，微软Excel仅用于查看效果。
步骤一：使用免费版的Spire.XLS创建工作薄和表单。
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
步骤二：为表单单元格添加样本数据，并设置单元格行高，列宽，对齐方式。
sheet.Range["A1"].Value = "姓名/学科";
sheet.Range["A2"].Value = "小明";
sheet.Range["A3"].Value = "小红";
sheet.Range["A4"].Value = "李雷";
sheet.Range["A5"].Value = "韩美美";
sheet.Range["A6"].Value = "隔壁王";
sheet.Range["A7"].Value = "诸葛亮";
sheet.Range["B1"].Value = "语文";
sheet.Range["C1"].Value = "数学";
sheet.Range["D1"].Value = "英语";
sheet.Range["E1"].Value = "体育";
sheet.Range["B2"].NumberValue = 56;
sheet.Range["B3"].NumberValue = 73;
sheet.Range["B4"].NumberValue = 75;
sheet.Range["B5"].NumberValue = 89;
sheet.Range["B6"].NumberValue = 65;
sheet.Range["B7"].NumberValue = 90;
sheet.Range["C2"].NumberValue = 78;
sheet.Range["C3"].NumberValue = 99;
sheet.Range["C4"].NumberValue = 86;
sheet.Range["C5"].NumberValue = 45;
sheet.Range["C6"].NumberValue = 98;
sheet.Range["C7"].NumberValue = 83;
sheet.Range["D2"].NumberValue = 79;
sheet.Range["D3"].NumberValue = 70;
sheet.Range["D4"].NumberValue = 90;
sheet.Range["D5"].NumberValue = 87;
sheet.Range["D6"].NumberValue = 65;
sheet.Range["D7"].NumberValue = 78;
sheet.Range["E2"].NumberValue = 65;
sheet.Range["E3"].NumberValue = 55;
sheet.Range["E4"].NumberValue = 100;
sheet.Range["E5"].NumberValue = 85;
sheet.Range["E6"].NumberValue = 60;
sheet.Range["E7"].NumberValue = 75;
sheet.AllocatedRange.RowHeight = 15;
sheet.AllocatedRange.ColumnWidth = 17;
sheet.AllocatedRange.VerticalAlignment = VerticalAlignType.Center;
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;
步骤三：为表单B2:E3单元格设置条件格式：60分以下用红色标记，60分以上用绿色标记。
ConditionalFormatWrapper format1 = sheet.Range["B2:E3"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.CellValue;
format1.FirstFormula = "60";
format1.Operator = ComparisonOperatorType.Less;
format1.FontColor = Color.Red;
ConditionalFormatWrapper format2 = sheet.Range["B2:E3"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.CellValue;
format2.FirstFormula = "60";
format2.Operator = ComparisonOperatorType.GreaterOrEqual;
format2.FontColor = Color.ForestGreen;
步骤四：为表单B4:E5单元格数据设置条件格式：数据条。
ConditionalFormatWrapper format3 = sheet.Range["B4:E5"].ConditionalFormats.AddCondition();
format3.FormatType = ConditionalFormatType.DataBar;
format3.DataBar.BarColor = Color.DeepSkyBlue;
步骤五：为表单B6:E7单元格设置条件格式：色阶。
ConditionalFormatWrapper format4 = sheet.Range["B6:E7"].ConditionalFormats.AddCondition();
format4.FormatType = ConditionalFormatType.ColorScale;
步骤六：保存文件为2010版的Excel，并启动查看效果。
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("sample.xlsx");效果图：
全部代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace How_to_set_data_bar
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "姓名/学科";
sheet.Range["A2"].Value = "小明";
sheet.Range["A3"].Value = "小红";
sheet.Range["A4"].Value = "李雷";
sheet.Range["A5"].Value = "韩美美";
sheet.Range["A6"].Value = "隔壁王";
sheet.Range["A7"].Value = "诸葛亮";
sheet.Range["B1"].Value = "语文";
sheet.Range["C1"].Value = "数学";
sheet.Range["D1"].Value = "英语";
sheet.Range["E1"].Value = "体育";
sheet.Range["B2"].NumberValue = 56;
sheet.Range["B3"].NumberValue = 73;
sheet.Range["B4"].NumberValue = 75;
sheet.Range["B5"].NumberValue = 89;
sheet.Range["B6"].NumberValue = 65;
sheet.Range["B7"].NumberValue = 90;
sheet.Range["C2"].NumberValue = 78;
sheet.Range["C3"].NumberValue = 99;
sheet.Range["C4"].NumberValue = 86;
sheet.Range["C5"].NumberValue = 45;
sheet.Range["C6"].NumberValue = 98;
sheet.Range["C7"].NumberValue = 83;
sheet.Range["D2"].NumberValue = 79;
sheet.Range["D3"].NumberValue = 70;
sheet.Range["D4"].NumberValue = 90;
sheet.Range["D5"].NumberValue = 87;
sheet.Range["D6"].NumberValue = 65;
sheet.Range["D7"].NumberValue = 78;
sheet.Range["E2"].NumberValue = 65;
sheet.Range["E3"].NumberValue = 55;
sheet.Range["E4"].NumberValue = 100;
sheet.Range["E5"].NumberValue = 85;
sheet.Range["E6"].NumberValue = 60;
sheet.Range["E7"].NumberValue = 75;
sheet.AllocatedRange.RowHeight = 15;
sheet.AllocatedRange.ColumnWidth = 17;
sheet.AllocatedRange.VerticalAlignment = VerticalAlignType.Center;
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;
ConditionalFormatWrapper format1 = sheet.Range["B2:E3"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.CellValue;
format1.FirstFormula = "60";
format1.Operator = ComparisonOperatorType.Less;
format1.FontColor = Color.Red;
ConditionalFormatWrapper format2 = sheet.Range["B2:E3"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.CellValue;
format2.FirstFormula = "60";
format2.Operator = ComparisonOperatorType.GreaterOrEqual;
format2.FontColor = Color.ForestGreen;
ConditionalFormatWrapper format3 = sheet.Range["B4:E5"].ConditionalFormats.AddCondition();
format3.FormatType = ConditionalFormatType.DataBar;
format3.DataBar.BarColor = Color.DeepSkyBlue;
ConditionalFormatWrapper format4 = sheet.Range["B6:E7"].ConditionalFormats.AddCondition();
format4.FormatType = ConditionalFormatType.ColorScale;
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("sample.xlsx");
}
}
}
感谢阅读，欢迎测试使用我司产品。
标签：sheet,C#,Excel,Value,NumberValue,Range,format1,format2,格式 From： https://blog.51cto.com/u_15656056/5979844