C# Excel 条件格式

古城微笑少年丶 2023-10-16 22:04 10阅读 0赞

Excel中条件格式是指：根据条件使用数据条、色阶和图标集，以突出显示相关单元格，强调异常值，以及实现数据的可视化效果。条件格式功能是进阶版的筛选，即先设置条件选出符合某些条件的数据，随后赋予其特定格式。鉴于Excel在处理数据方面有着天然的优势，条件格式是我们必须掌握的一项技巧。此文旨在介绍如何使用我司免费版产品**Free Spire.XLS**在C\#中实现Excel条件格式的设置。

***友情提示***：可以从以下三个资源站点下载使用Free Spire.XLS: [官网E-iceblue][E-iceblue]; [CSDN下载][CSDN]；[Nuget][]。 下载完成后，请将下载包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");

**效果图：**  
![这里写图片描述][20150807144139453]

**全部代码：**

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");
    
            }
        }
    }

**如需技术支持，请邮件联系support@e-iceblue.com**.  
*感谢阅读，欢迎测试使用我司产品。*

[E-iceblue]: http://www.e-iceblue.com/Download/download-excel-for-net-free.html
[CSDN]: http://download.csdn.net/detail/eiceblue/8818131
[Nuget]: https://www.nuget.org/packages/FreeSpire.XLS/
[20150807144139453]: https://img-blog.csdn.net/20150807144139453