示例要点概述:

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

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

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

  4. 删除条件格式

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

  4.2 删除全部条件格式

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

测试结果:

 图片 1

 

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

(本文完)

清单5-31显示了使用get_Item的示例。

【示例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. 删除指定数据范围的条件格式

图片 2

  1. 删除全部条件格式

图片 3

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

如需转载,请注明出处。

 

要点概述:

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

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

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

 

使用regions
CurrentRegion属性返回一个范围,该范围将扩展为包含所有单元格,直到空白行和空白列。这个扩展的范围被称为一个区域。所以,例如,你可能有一个范围,它包含一个表格中的几个单元格,以获得包含整个表格的范围(假设该表由空白的行和列组成),您将使用较小范围的CurrentRegion属性返回整个桌子

使用工具

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

 

【示例 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

 图片 5

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

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

图片 6

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

测试结果:

 图片 7

 

 

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

  int rowCount = range1.Rows.Count;
  int columnCount = range1.Columns.Count;

  for (int i = 1; i <= rowCount; i++)
  {
    for (int j = 1; j <= columnCount; j++)
    {
      Excel.Range cell = range1.get_Item(i, j) as Excel.Range;
      string address = cell.get_Address(missing,
        missing, Excel.XlReferenceStyle.xlA1,
        missing, missing);

      cell.Value2 = String.Format("get_Item({0},{1})", i, j);
    }
  }
}

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

测试结果:

图片 8

C#代码示例(供参考)

格式化一系列单元格
Excel提供了几种方法和属性来格式化一系列单元格。最有用的是NumberFormat属性,您可以将其设置为格式化与“格式化单元格”对话框的“自定义”类别中的字符串相对应的字符串。例如,您可以将NumberFormat设置为“常规”,不设置特定的数字格式。将NumberFormat设置为m
/ d /
yyyy设置日期格式,0%将格式设置为百分比格式。当使用NumberFormat时,如果您正在构建控制台应用程序或加载项,请务必考虑本章后面的“特殊Excel问题”一节中讨论的区域设置问题,因为读取和设置此字符串可能会在不同的运行时导致问题语言环境。如果您在工作簿或模板项目后面使用VSTO代码,则无需担心语言环境问题。

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

使用工具:

  • Spire.XLS for
    .NET

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

图片 9

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

示例代码(供参考)

测试文档如下:

图片 10

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

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

Excel还允许您创建与工作簿相关联的样式,并将这些样式应用于范围。您可以使用Workbook.Styles创建样式。清单5-35显示了创建样式并将其应用于Range的示例。

Font属性返回一个Font对象,可用于将Font设置为各种大小和样式。清单5-34显示了用于加粗单元格字体的Font对象的示例。

使用Area
当一个范围内存在多个不连续的单元格范围时,每个不连续的范围称为一个区域。
如果Range中有多个不连续的区域,请使用Areas属性通过Areas集合访问每个区域(作为Range)。
Areas集合具有一个Areas.Count属性和一个Areas.get_Item方法,它将一个表示基于1的索引的int参数作为数组。
清单5-30显示了一个迭代示例范围(有三个区域)并打印每个区域的地址的示例。

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

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

特殊Excel问题
在.NET中使用Excel对象模型时,需要注意几个特殊的注意事项。
本节将检查两个最重要的内容:使用多个区域设置并使用Excel日期。

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

请考虑以下示例。您的加载项正在法国机器上运行。您的加载项将区域设置切换到1033并设置公式值。另一个加载项是处理Change事件并显示一个对话框。该对话框以英文而不是法语显示。因此,通过更改线程区域设置,您已经改变了另一个加载项的行为,并且一般是不良的Office公民。

自动化可执行文件和COM加载项的Excel区域问题
当使用自动化可执行文件或COM加载项中的托管代码对Excel对象模型进行编程时,根据当前线程的区域设置,Excel方法和属性的行为可能会有所不同。
请注意,使用VSTO构建的文档解决方案后面的代码中不会出现此问题。
例如,如果要为范围设置公式并且位于法语区域设置中,则Excel要求您使用本地化的法语公式名称和格式:

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

 

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

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

"$F$20:$K$33,$M$8:$O$14,$Q$6"

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

图片 11

清单5-33显示了使用Value2的几个示例,包括将值数组传递给Value2的示例。通过数组一次设置范围内的单元格的值比通过多个调用单独设置每个单元格更有效。

最后,Excel不能代表1900年1月1日之前1900年格式的日子,1904年1月1日之前的日期,以1904年格式。
因此,当您将DateTime转换为Excel日期时,必须传递一个字符串而不是表示date的数字,因为这些日期不能在Excel中表示为日期(仅作为字符串)。

object excelDate = myRange.get_value(Type.Missing);
DateTime goodDate = ConvertExcelDateToDate(excelDate);

对于日期,您有一个明确的解决方法。 不要将日期作为文字字符串传递给Excel。
相反,使用System.DateTime对象构建日期,并使用DateTime的ToOADate方法将其传递给Excel,如代码清单5-36所示。
ToOADate方法将DateTime转换为OLE自动化日期,这是Excel对象模型期望的日期格式。

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

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

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

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

VSTO对Excel语言环境问题的解决方案有一些注意事项。
VSTO透明代理可以稍微减慢代码的速度。它也会导致Excel对象在调试器中检查时显示略有不同。最后,如果您使用Equals运算符将代理的Excel对象(如Application)与未使用的Application对象进行比较,则它们将不会被评估为相等。

表5-18  查找方法的参数

旧格式或无效的类型库错误
Excel语言环境问题进一步复杂化的第二个问题是,在将语言环境设置为非英语区域设置的计算机上的英文Excel安装中使用Excel对象模型时,可能会收到“旧格式或无效类型库”错误。
Excel正在程序文件 Microsoft Office OFFICE11
1033中找到一个名为xllex.dll的文件,它找不到。解决此问题的方法是安装xllex.dll文件或安装Office的MUI语言包。您还可以复制excel.exe,将其重命名为xllex.dll,并将其复制到1033目录。

将Excel日期转换为DateTime
Excel可以以两种格式表示日期:1900格式或1904格式。
1900格式基于一个系统,当转换为一个数字时,它代表从1900年1月1日起的已过去的天数。1904格式是基于一个系统,当转换为一个数字时,它表示已过的天数自1904年1月1日起,1904年格式由早期的Macintosh计算机引入,因为我们稍后描述的1900格式的问题。您可以通过检查Workbook.Date1904属性来确定工作簿正在使用的格式,如果工作簿使用1904格式,则返回true。

也可以通过使用get_Offset方法取一个范围并获得一个与之相距一些行和列的新范围。
该方法采用行和列值来偏移给定范围并返回新的偏移范围。
所以在图5-8的示例范围中调用get_Offset(5,5)返回一个这样的A1样式地址的范围:

 

 

您还可以使用“行”和“列”属性来确定第一个区域中的行和列的总数。这些属性返回特殊范围,您可以将其视为对应于与范围相关联的行或列标题。当我们从图5-8中的示例范围获取Rows.Count时,它返回14,Columns.Count返回6.这是有道理的,因为我们选择的第一个区域(A15:F28)跨越6列和14行。

发表评论

电子邮件地址不会被公开。 必填项已用*标注