c# - 从 Excel 中的范围中删除单元格

Question

我创建了一个包含两个按钮的自定义 Excel 功能区。 btnFillColor 和 btnUndo。

当用户点击 btnFillColor 按钮时，选定的单元格会填充一些颜色。同时显示一个输入框，要求用户为选定的单元格范围命名。

当用户点击 btnUndo 时，单元格被清除，相应范围的名称以完全相反的顺序被删除。

当用户单击 btnFillColor 并为选定的单元格命名时，如果已存在相同的名称，则该范围将附加到具有相同名称的现有范围。

现在我的问题是:当用户在以相同名称添加两个不同范围后单击撤消按钮时，只有那些单元格应该被清除并从最后添加到范围中的范围中删除。另一个应该与之前给出的名称一起保持完整。如何实现？

这是代码。

    private void btnFillColor_Click(object sender, RibbonControlEventArgs e)
    {
        Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
        string strFilledRangeAddress = Globals.ThisAddIn.Application.Selection.Address.ToString();
        Excel.Range FilledRange = ws.get_Range(strFilledRangeAddress);
        FilledRange.Interior.Color = Color.YellowGreen;
        string tagName = Microsoft.VisualBasic.Interaction.InputBox("Enter tag name:");
        if (lstFilledRangeName.Contains(tagName))
        {
            Excel.Range ExistingRange = ws.get_Range(tagName);
            FilledRange = Globals.ThisAddIn.Application.Union(ExistingRange, FilledRange);
        }
        else
        {
            lstFilledRangeName.Add(tagName);
        }
        FilledRange.Name = tagName;
        lstFilledRangeAddresses.Add(strFilledRangeAddress);
        if (btnUndo.Enabled == false)
            btnUndo.Enabled = true;
    }

    private void btnUndo_Click(object sender, RibbonControlEventArgs e)
    {
        Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
        Excel.Range FilledRange = ws.get_Range(lstFilledRangeAddresses[lstFilledRangeAddresses.Count - 1]);
        FilledRange.Interior.ColorIndex = 0;
        lstFilledRangeAddresses.RemoveAt(lstFilledRangeAddresses.Count - 1);
        if (lstFilledRangeAddresses.Count == 0)
            btnUndo.Enabled = false;
    }

Answer 1

为了只清除最后填充的单元格并将它们从现有范围中删除，我正在跟踪用户在数据表中所做的选择。当用户单击撤消按钮时，在清除单元格和名称之前，我正在检查用户之前是否以相同的名称进行了选择。如果是，那么首先我清除该名称下的所有单元格并删除该名称。之后，我再次用颜色填充之前的选择，并为其命名。

修改后的代码:

    DataTable dtRangeName_Address = new DataTable();
    private void RnD_Ribbon_Load(object sender, RibbonUIEventArgs e)
    {
        btnUndo.Enabled = false;
        dtRangeName_Address.Columns.Add("Name");
        dtRangeName_Address.Columns.Add("Address");
    }

    private void btnFillColor_Click(object sender, RibbonControlEventArgs e)
    {
        Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
        string strFilledRangeAddress = Globals.ThisAddIn.Application.Selection.Address.ToString();
        Excel.Range FilledRange = ws.get_Range(strFilledRangeAddress);
        FilledRange.Interior.Color = Color.YellowGreen;
        DataRow drRangeName_Address = dtRangeName_Address.NewRow();
        bool TagAlreadyExists = false;
        string tagName = Microsoft.VisualBasic.Interaction.InputBox("Enter tag name:");
        for (int i = dtRangeName_Address.Rows.Count - 1; i >= 0; i--)
        {
            if (dtRangeName_Address.Rows[i][0].ToString() == tagName)
            {
                Excel.Range ExistingRange = ws.get_Range(dtRangeName_Address.Rows[i][1].ToString());
                FilledRange = Globals.ThisAddIn.Application.Union(ExistingRange, FilledRange);
                drRangeName_Address[1] = FilledRange.Address.ToString();
                drRangeName_Address[0] = tagName;
                TagAlreadyExists = true;
                break;
            }
        }
        if (TagAlreadyExists == false)
        {
            drRangeName_Address[1] = strFilledRangeAddress;
            drRangeName_Address[0] = tagName;
        }
        FilledRange.Name = tagName;
        dtRangeName_Address.Rows.Add(drRangeName_Address);
        if (btnUndo.Enabled == false)
            btnUndo.Enabled = true;
    }

    private void btnUndo_Click(object sender, RibbonControlEventArgs e)
    {
        Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
        Excel.Range FilledRange = ws.get_Range(dtRangeName_Address.Rows[dtRangeName_Address.Rows.Count - 1][1].ToString());
        string tagName = dtRangeName_Address.Rows[dtRangeName_Address.Rows.Count - 1][0].ToString();
        Excel.Range ExistingRange = null;
        bool TagAlreadyExists = false;
        for (int i = dtRangeName_Address.Rows.Count - 2; i >= 0; i--)
        {
            if (dtRangeName_Address.Rows[i][0].ToString() == tagName)
            {
                ExistingRange = ws.get_Range(dtRangeName_Address.Rows[i][1].ToString());
                TagAlreadyExists = true;
                break;
            }
        }
        FilledRange.Interior.ColorIndex = 0;
        FilledRange.Name.Delete();
        if (TagAlreadyExists == true && ExistingRange != null)
        {
            ExistingRange.Interior.Color = Color.YellowGreen;
            ExistingRange.Name = tagName;
        }
        dtRangeName_Address.Rows.RemoveAt(dtRangeName_Address.Rows.Count - 1);
        if (dtRangeName_Address.Rows.Count == 0)
            btnUndo.Enabled = false;
    }