C# Excel 条件格式

最新推荐文章于 2023-02-15 21:22:53 发布

Eiceblue

最新推荐文章于 2023-02-15 21:22:53 发布

阅读量2.9k

点赞数 2

分类专栏： .NET Excel 文章标签：数据 excel 条件格式 Spire-XLS NET插件

本文链接：https://blog.csdn.net/Eiceblue/article/details/47339275

版权

.NET Excel 专栏收录该内容

62 篇文章 3 订阅

订阅专栏

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

友情提示：可以从以下三个资源站点下载使用Free Spire.XLS: 官网E-iceblue; 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");

效果图：
这里写图片描述

全部代码：

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.
感谢阅读，欢迎测试使用我司产品。

Eiceblue

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
C# Excel 条件格式

Excel中条件格式是指：根据条件使用数据条、色阶和图标集，以突出显示相关单元格，强调异常值，以及实现数据的可视化效果。在我看来，这是一种进阶版的筛选，即先设置条件选出符合某些条件的数据，随后赋予其特定格式。鉴于Excel在处理数据方面有着天然的优势，条件格式是我们必须掌握的一项技巧。
复制链接

扫一扫

专栏目录