C# 应用Excel条件格式(一)

图片 11

Excel中的条件格式功能是个十分强大且方便的功能,通过对使用条件格式功能可以在很大程度上改进表格的设计和可读性,用户可以指定单个或者多个单元格区域应用一种或者多种格式,如此一来,也在大大提高了表格的可操作性。下面将介绍在C#编程中如何来设置并应用Excel条件格式。

上一篇文章中介绍了关于设置Excel条件格式,包括基于单元格值、自定义公式等应用条件格式、应用数据条条件类型格式、删除条件格式等内容。在本篇文章中将继续介绍C#
设置条件格式的方法。

使用Range对象
Range对象表示电子表格中的单元格范围。范围可以包含一个单元格,多个连续的单元格,甚至多个不连续的单元格。您可以在Excel中选择时按住Ctrl键选择多个不连续的单元格。

示例要点概述:

  1. 基于单元格值应用条件格式

  2. 基于自定义公式应用条件格式

  3. 应用数据条条件类型格式

  4. 删除条件格式

  4.1 删除指定数据范围中的条件格式

  4.2 删除全部条件格式

要点概述:

  1. 应用条件格式用于高亮重复、唯一数值

  2. 应用条件格式用于高亮峰值(最高、最低)

  3. 应用条件格式用于高亮低于、高于平均值的数值

 

获取特定Cell或Cells范围对象
Excel提供了多种获取Range对象的方法。
Range对象是您要在Excel工作表中处理单元格或单元格范围时使用的对象。在Application对象的描述中提到了两种获取Range对象的方法。
Application.ActiveCell在活动窗口中返回活动选择的左上角单元格。
Application.Selection返回一个表示活动窗口中活动选择的对象。如果活动选择是单元格范围,则可以将Application.Selection转换为Range对象。如果在活动窗口(例如形状或图表)中选择了其他选项,Application.Selection将返回所选对象。

使用工具

  • Free Spire.XLS for .NET
    8.3(免费版)
  • Visual Studio

 

使用工具:

  • Spire.XLS for
    .NET

注:在编辑代码时注意在程序中添加引用Spire.Xls.dll,dll文件可在安装路径下的Bin文件夹中获取。

图片 1

工作表还提供了几种获取Range对象的方法。
Worksheet.get_Range方法是从工作表获取Range对象的最常用方法。此方法接收可以传递字符串的必需对象参数。它具有可以传递第二个字符串的第二个可选参数。您传递的字符串是所谓的A1样式参考格式。解释A1样式参考格式的最简单的方法是给出几个例子。

示例代码(供参考)

测试文档如下:

图片 2

C#代码示例(供参考)

参考A1指定A列1行的单元格。参考D22指定D列22行的单元格。参考AA11指定行11,列AA(第27列)处的单元格。

【示例 1 】应用条件格式

using Spire.Xls;
using System.Drawing;

namespace ConditionalFormatting_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化workbook对象并加载文档
            Workbook wb = new Workbook();
            wb.LoadFromFile("sample.xlsx");

            //获取第一个工作表
            Worksheet sheet = wb.Worksheets[0];

            //获取数据范围
            CellRange range = sheet.Range["A2:H27"];

            //在所选范围添加条件格式1
            ConditionalFormatWrapper format1 = range.ConditionalFormats.AddCondition();

            //条件格式类型1基于单元格值
            format1.FormatType = ConditionalFormatType.CellValue;
            //将数值在60到90之间的单元格进行字体加粗,并设置字体颜色为橙色
            format1.FirstFormula = "60";
            format1.SecondFormula = "90";
            format1.Operator = ComparisonOperatorType.Between;
            format1.FontColor = Color.Orange;
            //format1.BackColor = Color.Orange;

            //添加条件格式2
            ConditionalFormatWrapper format2 = range.ConditionalFormats.AddCondition();
            format2.FormatType = ConditionalFormatType.CellValue;
            format2.FirstFormula = "60";
            format2.Operator = ComparisonOperatorType.Less;
            format2.FontColor = Color.Red;
            //format2.BackColor = Color.Red;
            format2.IsBold = true;
            //添加边框格式(边框颜色、边框类型)到条件格式2
            format2.LeftBorderColor = Color.Red;
            format2.RightBorderColor = Color.DarkBlue;
            format2.TopBorderColor = Color.DeepSkyBlue;
            format2.BottomBorderColor = Color.DeepSkyBlue;
            format2.LeftBorderStyle = LineStyleType.Medium;
            format2.RightBorderStyle = LineStyleType.Thick;
            format2.TopBorderStyle = LineStyleType.Double;
            format2.BottomBorderStyle = LineStyleType.Double;

            //条件格式3的类型为自定义公式
            ConditionalFormatWrapper format3 = range.ConditionalFormats.AddCondition();
            format3.FormatType = ConditionalFormatType.Formula;

            //自定义公式将低于60的单元格所在的行填充背景色
            format3.FirstFormula = "=OR($C2<60,$D2<60,$E2<60,$F2<60,$G2<60,$H2<60)";
            format3.BackColor = Color.Gray;

            //保存并打开文档
            wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

调试运行程序,生成文档,如下:

图片 3

【示例 1】应用条件格式用于高亮重复、唯一数值

C#

using Spire.Xls;
using System.Drawing;

namespace HightDuplicateData_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化Workbook类,加载测试文档
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

            //应用条件格式1到指定数据范围,高亮重复数值的单元格
            ConditionalFormatWrapper format1 = sheet.Range["A3:A13"].ConditionalFormats.AddCondition();
            format1.FormatType = ConditionalFormatType.DuplicateValues;
            format1.BackColor = Color.Cyan;

            //应用条件格式2到指定数据范围,高亮唯一值的单元格
            ConditionalFormatWrapper format2 = sheet.Range["A3:A13"].ConditionalFormats.AddCondition();
            format2.FormatType = ConditionalFormatType.UniqueValues;
            format2.BackColor = Color.Yellow;

            //保存文档并打开            
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

完成代码后,调试运行程序,生成文档,如下图:

 图片 4

参考$ A $
1也指的是第1行列A中的单元格。如果在A1样式引用中使用$符号,则它们将被忽略。

【示例2】应用数据条类型的条件格式

using Spire.Xls;
using System.Drawing;

namespace ConditionalFormatting_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化workbook对象并加载文档
            Workbook wb = new Workbook();
            wb.LoadFromFile("sample.xlsx");

            //获取第2个工作表
            Worksheet sheet = wb.Worksheets[1];

            //获取数据范围
            CellRange range = sheet.Range["B2:D7"];

            //添加条件类型4为data bars
            ConditionalFormatWrapper format4 = sheet.AllocatedRange.ConditionalFormats.AddCondition();
            format4.FormatType = ConditionalFormatType.DataBar;
            format4.DataBar.BarColor = Color.ForestGreen;

            //保存并打开文档
            wb.SaveToFile("result1.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("result1.xlsx");  
        }
    }
}

测试结果:

图片 5

【示例2】应用条件格式用于高亮峰值(最高、最低)

 C#

using Spire.Xls;
using System.Drawing;

namespace HighlightTopData_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化workbook类
            Workbook workbook = new Workbook();
            //加载测试文档
            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

            //应用条件格式1到指定范围,并高亮最高(依次排序)的两个数值
            ConditionalFormatWrapper format1 = sheet.Range["B17:B24"].ConditionalFormats.AddCondition();
            format1.FormatType = ConditionalFormatType.TopBottom;
            format1.TopBottom.Type = TopBottomType.Top;
            format1.TopBottom.Rank = 2;
            format1.BackColor = Color.Green;

            //应用条件格式2到指定范围,并高亮最低(依次排序)的两个数值
            ConditionalFormatWrapper format2 = sheet.Range["B17:B24"].ConditionalFormats.AddCondition();
            format2.FormatType = ConditionalFormatType.TopBottom;
            format2.TopBottom.Type = TopBottomType.Bottom;
            format2.TopBottom.Rank = 2;
            format2.BackColor = Color.RosyBrown;

            //保存并打开文档
            workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("output.xlsx");
        }
    }
}

测试结果:

 图片 6

 

 

您可以使用范围运算符(:)来指定单元格范围,其中第一个A1样式引用是范围的左上角,后跟一个冒号运算符,之后是右下角的第二个A1样式引用的范围。参考A1:B1指的是行1,列A和列1,列B的两个单元格。参考A1:AA11是指块中的所有297个单元格,其左上角位于第1列,第A列和下侧右角位于第11列,AA栏(第27列)。

【示例3】删除条件格式

using Spire.Xls;

namespace RemoveConditionalFormat_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化Workbook类,加载测试文档
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];
            //删除指定区域的条件格式
            //sheet.Range["A5:H5"].ConditionalFormats.Remove();

            //删除表格中的所有条件格式
            sheet.AllocatedRange.ConditionalFormats.Remove();

            //保存并打开文档
            workbook.SaveToFile("result1.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("result1.xlsx");
        }
    }
}

 删除效果

  1. 删除指定数据范围的条件格式

图片 7

  1. 删除全部条件格式

图片 8

本次关于“C# 应用条件格式到Excel”的示例方法介绍到此。

如需转载,请注明出处。

 

【示例3】应用条件格式用于高亮低于、高于平均值的数值

C#

using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Collections;

namespace Average_Condition
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化workbook类
            Workbook workbook = new Workbook();
            //加载文档
            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

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

            //添加条件格式2并应用到指定数据范围
            XlsConditionalFormats format2 = sheet.ConditionalFormats.Add();
            format2.AddRange(sheet.Range["B17:B24"]);
            //高亮高于平均数值的单元格
            IConditionalFormat cf2 = format1.AddAverageCondition(AverageType.Above);
            cf2.BackColor = Color.Orange;

            //保存并打开文档
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

测试结果:

 图片 9

 

以上内容是本次关于设置Excel表格条件格式的补充介绍,如需转载,请注明出处。

(本文完)

您可以使用联合运算符(,)来指定可能是不连续的多个单元格。例如,参考A1,C4指定了第一个单元格位于第1列,第A列,第二个单元格位于第4列第C列的两个单元格的范围。用户可以通过按住Ctrl键来选择单元格的不连续范围因为他们选择各种细胞。参考A1,C4,C8,C10是指定四个不同单元格的另一个有效的A1样式参考。

交点运算符(一个空格)可以指定单元格的交集。例如,参考A1:A10
A5:A15解析为从行5,列A开始并以行A,列A开始的相交六个单元。参考A1:A10
A5:A15 A5分解为第5行的单个单元格,列A.

您还可以使用您在A1样式参考中在工作表中定义的任何名称。例如,假设您定义了指向单元格A1的名为foo的名称范围。使用您的名称的一些有效的A1样式的引用将包括foo:A2,其引用行1,列A和行2,列A的单元格。引用foo,A5:A6是指第1行,第A列;第5行,A列;和第6列,列A。

如前所述,get_Range方法采用第二个可选参数,您可以传递第二个A1样式的引用字符串。使用范围运算符有效地组合第一个参数和第二个参数。因此,当调用get_Range(“A1”,“A2”)时,get_Range返回的范围等同于调用get_Range(“A1:A2”,Type.Missing)时获得的范围。

获取Range对象的第二种方法是使用Worksheet.Cells属性,该属性返回工作表中所有单元格的范围。然后,您可以在返回的Range对象上使用相同的get_Range方法,并以与使用Worksheet对象中的get_Range相同的方式传递A1样式引用以选择单元格。所以Cells.get_Range(“A1:A2”,Type.Missing)等价于get_Range(“A1:A2”,Type.Missing)。使用Cells属性的更常见的用法是将其与Range的get_Item属性结合使用,该属性将使用行索引和可选的列索引。使用get_Item是一种在不使用A1样式引用的情况下访问特定单元格的方法。所以Cells.get_Item(1,1)相当于get_Range(“A1”,Type.Missing)。

获取Range对象的另一种方法是使用Worksheet.Rows或Worksheet.Columns属性。这些返回一个与其他Range对象不同的范围。例如,如果您采用Column返回的范围并显示范围内的单元格数,则返回256列数。但是如果您在返回的范围内调用Select方法,Excel将在工作表中选择所有16,772,216个单元格。考虑Rows和Columns返回的范围的最简单的方法是它们的行为与列和行标题在Excel中的行为相似。

清单5-27显示了使用get_Range方法和Cells,Rows和Columns属性的几个示例。我们使用范围的Value2属性将范围中的每个单元格设置为指定的字符串值。程序的运行结果如图5-7所示

清单5-27   获取Range对象的VSTO定制

private void Sheet1_Startup(object sender, System.EventArgs e)
{
  Excel.Range r1 = this.get_Range("A1", missing);
  r1.Value2 = "r1";

  Excel.Range r2 = this.get_Range("B7:C9", missing);
  r2.Value2 = "r2";

  Excel.Range r3 = this.get_Range("C1,C3,C5", missing);
  r3.Value2 = "r3";

  Excel.Range r4 = this.get_Range("A1:A10 A5:A15", missing);
  r4.Value2 = "r4";

  Excel.Range r5 = this.get_Range("F4", "G8");
  r5.Value2 = "r5";

  Excel.Range r6 = this.Rows.get_Item(12, missing)
    as Excel.Range;

  r6.Value2 = "r6";

  Excel.Range r7 = this.Columns.get_Item(5, missing)
    as Excel.Range;

  r7.Value2 = "r7";
}

图5-7  运行结果清单5-27

图片 10

使用Address
给定一个Range对象,你经常需要确定它所指的单元格。
get_Address方法返回A1样式或R1C1样式范围的地址。
您已经了解了A1样式的引用。
R1C1样式的引用支持与A1样式引用(范围为冒号,联合逗号和交叉空间)讨论的所有相同的运算符。
R1C1样式的引用分别以R和C开头的行和列号。
所以R1C1风格的单元格A4将是R4C1。
图5-8显示了我们在本节中考虑的三个方面的范围。

图5-8  具有三个不连续区域的范围

 图片 11

图5-8中范围的地址以A1样式和R1C1样式显示:

$A$15:$F$28,$H$3:$J$9,$L$1
R15C1:R28C6,R3C8:R9C10,R1C12

获取地址的另一个选择是获取外部引用还是本地引用。
我们已经在图5-8中显示的地址是本地引用。
外部引用包括范围所在的工作簿和工作表的名称。
在图5-8中,与A1风格和R1C1风格的外部参考相同。

 

[Book1]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1
[Book1]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12

对于我们的示例,我们创建的范围的工作簿未保存。
当我们将它保存为Book1.xls时,地址如下所示:

[Book1.xls]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1
[Book1.xls]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12

获取地址的另一个选择是使用绝对地址还是使用相对地址。
我们已经考虑过的地址是绝对的。
相对格式(相对于单元格A1)的相同地址如下所示:

R[14]C:R[27]C[5],R[2]C[7]:R[8]C[9],RC[11]
A15:F28,H3:J9,L1

对于R1C1样式的地址,您还可以指定希望地址相对的单元格。
如果我们在图5-4中相对于单元格B2获得了R1C1样式,我们得到以下结果:

R[13]C[-1]:R[26]C[4],R[1]C[6]:R[7]C[8],R[-1]C[10]

get_Address方法使用五个可选参数来控制引用的返回方式,如表5-17所示。

表5-17   get_Address的可选参数

Parameter Name

Type

What It Does

RowAbsolute

object

通过TRue将地址的行部分作为绝对引用返回($ A $ 1)。 如果您传递false,行参考将不会是绝对的($ A1)。 默认值为true。

ColumnAbsolute

object

通过TRue将地址的列部分作为绝对引用返回($ A $ 1)。 如果你传递错误,列参考将不是绝对的(A $ 1)。 默认值为true。

ReferenceStyle

XlReferenceStyle

通过xlA1返回A1样式的引用。 通过xlR1C1返回R1C1样式的引用。

External

object

传递真的返回外部引用。 默认值为false。

RelativeTo

object

传递一个表示您希望R1C1样式引用相对于单元格的Range对象。 与A1样式引用一起使用时不起作用。

 

清单5-28显示了使用示例范围的get_Address的几个示例。

清单5-28  使用get_Address的VSTO自定义

private void Sheet1_Startup(object sender, System.EventArgs e)
{
  Excel.Range range1 = this.get_Range(
    "$A$15:$F$28,$H$3:$J$9,$L$1", missing);

  System.Text.StringBuilder sb = new System.Text.StringBuilder();
  sb.AppendLine("A1-Style Addresses:");
  sb.AppendFormat("Default: {0}n", range1.get_Address(
    missing, missing, Excel.XlReferenceStyle.xlA1,
    missing, missing));

  sb.AppendFormat("Relative rows: {0}n",
    range1.get_Address(false, missing,
    Excel.XlReferenceStyle.xlA1, missing, missing));

  sb.AppendFormat("Row & Column Relative: {0}n",
    range1.get_Address(false, false,
    Excel.XlReferenceStyle.xlA1, missing, missing));

  sb.AppendFormat("External: {0}n", range1.get_Address(
    missing, missing, Excel.XlReferenceStyle.xlA1,
    true, missing));

  sb.AppendLine();
  sb.AppendLine("R1C1-Style Addresses:");
  sb.AppendFormat("Default: {0}n", range1.get_Address(
    missing, missing, Excel.XlReferenceStyle.xlR1C1,
    missing, missing));

  sb.AppendFormat("Row & Column Relative to C5: {0}n",
    range1.get_Address(false, false,
    Excel.XlReferenceStyle.xlR1C1, missing,
    this.get_Range("C5", missing)));

  sb.AppendFormat("External: {0}", range1.get_Address(
    missing, missing, Excel.XlReferenceStyle.xlR1C1,
    true, missing));

  MessageBox.Show(sb.ToString());
}

使用运算符方法创建新的范围
我们讨论了可以在地址字符串中使用的几个“运算符”,包括联合运算符(逗号)和交集运算符(空格)。
您还可以通过Application.Union和Application.Intersection方法应用这些操作符。

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图