c# - Microsoft.Office.Interop.Excel.FormatConditions 可以包含 3 种以上的条件格式吗？

Question

https://docs.microsoft.com/en-gb/dotnet/api/microsoft.office.interop.excel.formatconditions中的文档说：

FormatConditions 集合最多可以包含三种条件格式。

在https://docs.microsoft.com/en-gb/dotnet/api/microsoft.office.interop.excel.formatconditions.add?view=excel-pia#Microsoft_Office_Interop_Excel_FormatConditions_Add_Microsoft_Office_Interop_Excel_XlFormatConditionType_System_Object_System_Object_System_Object_System_Object_System_Object_System_Object_System_Object_中进一步加强了这一点，其中说：

您不能为一个范围定义三个以上的条件格式。

但是，我可以为一系列单元格定义超过 3 种条件格式。我在 Microsoft Visual Studio 2008 中开发的 C# 应用程序中执行此操作，使用 .NET Framework 3.5 的目标框架，并引用 Microsoft.Office.Interop.Excel.dll 版本 12.0.0.0。

using System;
using System.IO;

using Microsoft.Office.Interop.Excel;

namespace test
{
  class Program
  {
    static int Main( string[] args )
    {
      //------------------------------
      // Parse command line for output path.
      string path = null;
      if ( args.Length > 0 )
      {
        path = args[0];
      }
      Console.WriteLine( "Output path: '" + path + "'" );

      //------------------------------
      // The Excel application.
      Application app = new Application();

      // Disable appearance of Excel window.
      app.Visible = false;

      // Disable screen updates, so that changing cells is faster.
      app.ScreenUpdating = false;

      // Disable prompts, like whether to overwrite an existing file.
      app.DisplayAlerts = false;

      //------------------------------
      // The workbook.
      Workbook book = app.Workbooks.Add( Missing.Value );

      //------------------------------
      // The worksheet.
      Worksheet sheet = (Worksheet)book.ActiveSheet;

      //------------------------------
      // The first row.
      int r = 1;
      int c = 1;
      sheet.Cells[r, c++] = "R";
      sheet.Cells[r, c++] = "Y";
      sheet.Cells[r, c++] = "G";
      sheet.Cells[r, c++] = "B";
      sheet.Cells[r, c++] = "X";
      sheet.Cells[r, c++] = "R";
      sheet.Cells[r, c++] = "Y";
      sheet.Cells[r, c++] = "G";

      //------------------------------
      // The cells to format.
      Range range = sheet.get_Range( sheet.Cells[1, 1], sheet.Cells[1, 8] );
      range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
      range.VerticalAlignment = XlVAlign.xlVAlignCenter;
      range.Borders.LineStyle = XlLineStyle.xlContinuous;


      //------------------------------
      // *** Add more than 3 conditional format rules as a test. ***
      //------------------------------


      //------------------------------
      // Red status.
      FormatCondition cond = (FormatCondition)range.FormatConditions.Add(
        XlFormatConditionType.xlCellValue,    // type 
        XlFormatConditionOperator.xlEqual,    // operator
        "R",                                  // formula1: The value or expression associated with the conditional format. 
        Type.Missing,                         // formula2
        Type.Missing,                         // object string
        Type.Missing,                         // textOperator
        Type.Missing,                         // dateOperator
        Type.Missing                          // scopeType
      );

      cond.Font.Bold = true;
      cond.Font.Color = XlColor.WHITE;
      cond.Interior.Color = XlColor.STATUS_RED;

      //------------------------------
      // Yellow status.
      cond = (FormatCondition)range.FormatConditions.Add(
        XlFormatConditionType.xlCellValue,
        XlFormatConditionOperator.xlEqual,
        "Y",
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );

      cond.Font.Bold = true;
      cond.Interior.Color = XlColor.STATUS_YELLOW;

      //------------------------------
      // Green status.
      cond = (FormatCondition)range.FormatConditions.Add(
        XlFormatConditionType.xlCellValue,
        XlFormatConditionOperator.xlEqual,
        "G",
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );

      cond.Font.Bold = true;
      cond.Font.Color = XlColor.WHITE;
      cond.Interior.Color = XlColor.STATUS_GREEN;

      //------------------------------
      // Blue status.
      // *** The 4th condition I supposedly should not have. ***
      cond = (FormatCondition)range.FormatConditions.Add(
        XlFormatConditionType.xlCellValue,
        XlFormatConditionOperator.xlEqual,
        "B",
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );

      cond.Font.Bold = true;
      cond.Font.Color = XlColor.WHITE;
      cond.Interior.Color = XlColor.STATUS_BLUE;

      //------------------------------
      // Another status for good measure.
      // *** The 5th condition I supposedly should not have. ***
      cond = (FormatCondition)range.FormatConditions.Add(
        XlFormatConditionType.xlCellValue,
        XlFormatConditionOperator.xlEqual,
        "X",
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing );

      cond.Font.Bold = true;
      cond.Font.Color = XlColor.WHITE;
      cond.Interior.Color = XlColor.BLACK;


      //------------------------------
      // Save workbook to output path.
      book.SaveAs( path,                  // fileName
        XlFileFormat.xlWorkbookDefault,   // fileFormat
        Type.Missing,                     // password
        Type.Missing,                     // writeResPassword
        false,                            // readOnlyRecommended
        false,                            // createBackup
        XlSaveAsAccessMode.xlNoChange,    // accessMode
        XlSaveConflictResolution.xlLocalSessionChanges,  // conflictResolution: Should not matter since the workbook I create is not shared, but probably benign to specify it.
        Type.Missing,                     // addToMru
        Type.Missing,                     // textCodepage
        Type.Missing,                     // textVisualLayout
        Type.Missing                      // local
      );

      Console.WriteLine( "Saved workbook: '" + book.FullName + "'" );

      book.Close( true,   // saveChanges: If true, save changes to the workbook.  If there is not yet a file name associated with the workbook, then given fileName is used.
        path,             // fileName:  If saveChanges is true and fileName is omitted, the user is asked to supply a file name.
        false             // routeWorkbook
      );

      return 0;
    }
  }

  /// <summary>
  /// B8G8R8 color values that can be used with Microsoft.Office.Interop.Excel API.
  /// </summary>
  public enum XlColor
  {
    BLACK = 0x000000,
    WHITE = 0xffffff,
    RED = 0x0000ff,
    GREEN = 0x00ff00,
    BLUE = 0xff0000,
    YELLOW = 0x00ffff,
    MAGENTA = 0xff00ff,
    CYAN = 0xffff00,

    STATUS_RED = 0x0707be,
    STATUS_YELLOW = 0x00ffff,
    STATUS_GREEN = 0x50b000,
    STATUS_BLUE = 0x784e1f,
  };
}

我使用 Microsoft Office Professional Plus 2016 打开生成的 Excel 文件。我定义的所有 5 条条件格式规则都有效。

我只是幸运吗？还是文档过时了？

标签： c#excel