删除条件格式 [英] Deleting a ConditionalFormat

问题描述

我正在尝试使用带有以下代码的C＃添加条件格式。

I am trying to add a conditional formatting using C# with the below code.

Microsoft.Office.Interop.Excel.FormatCondition formatConditionObj = null;

formatConditionObj = (Microsoft.Office.Interop.Excel.FormatCondition)myRange
.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, 
Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, 
Type.Missing, Type.Missing);

formatConditionObj.Interior.ColorIndex = 5;

动态地，我使用

formatConditionObj.ModifyAppliesToRange(NewRange);

现在我想删除这种格式，该格式如何应用。

Now i want to delete this format which is applied how can this be achieved.

formatConditionObj.Delete();

这对我不起作用。这不会删除应用该格式的所有单元格的格式。仅删除最后一个单元格格式。

This doesn't work for me. This does not delete the format for all the cells where it is applied. Only the last cells formats is removed.

我也尝试使用

formatConditionObj.AppliesTo.Delete();

但它会删除其他也应用于该单元格的ConditionalFormat。

But it delete other ConditionalFormats also which are applied on that cell.

注意：某些格式已经应用到此条件式格式用于某些填充颜色的单元格上。甚至在某些单元格上还应用了其他一些条件格式。我只想删除此特定的ConditionalFormat（formatConditionObj）。

Note: Some formats are already applied on the cells where this conditinal formatting is applied for e.g some fill color. Even there are some other conditional formats applied on some of the cells. I just want to delete this particular ConditionalFormat(formatConditionObj).

任何人都可以帮我吗。

推荐答案

当一个单元格中有多个条件时，您将无法删除这样的格式条件。您必须通过条件编号来解决条件格式才能删除它。

You cannot delete the format conditions like this when you have multiple conditions in a cell. You have to address the conditional format by it's number to delete it.

请考虑以下示例。 （测试并试用）

Consider this example. (TESTED AND TRIED)

以下代码创建了一个新工作簿，并且在工作表1中的单元格A1中创建了两种格式条件。创建两个条件后，该应用程序将通过显示一个消息框来暂停。转到Excel并手动检查创建的条件格式。 （快照1）。完成后，在消息框中单击确定。然后，代码将删除条件1，然后通过向您显示一个消息框来再次暂停。转到Excel并手动检查条件格式。您会注意到只剩下一种（准确地说是第二种）条件格式。 （快照2）

The below code creates a new workbook and in sheet 1 creates 2 format conditions in Cell A1. After it creates the 2 conditions, the application will pause by showing you a message box. Go to Excel and manually inspect the conditional formats created. (Snapshot 1). Once done, click on OK in the message box. The code will then delete the condition 1 and then again pause by showing you a message box. Go to Excel and manually inspect the conditional formats. You will notice that there is only one (the second one to be precise) conditional format left. (Snapshot 2)

    private void btnSearch_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

        xlexcel = new Excel.Application();
        xlexcel.Visible = true;

        //~~> Add a File
        xlWorkBook = xlexcel.Workbooks.Add();
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //~~> Create 2 Conditions
        xlWorkSheet.Cells[1, 1].FormatConditions.Add( 1,5,"=5");
        xlWorkSheet.Cells[1, 1].FormatConditions.Add(1, 5, "=10");

        MessageBox.Show("Wait");
        //~~> Now if you check the Excel file, Cell A1 has two conditional formats.
        //~~> See Snapshot 1

        //~~> Delete the first condition
        xlWorkSheet.Cells[1, 1].formatconditions(1).delete();

        MessageBox.Show("Wait");
        //~~> Now if you check the Excel file, Cell A1 has only 1 conditional format.
        //~~> See Snapshot 2
    }

快照1

快照2

这篇关于删除条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！