c# - C# Excel worksheet_change 不总是触发

Question

我一直在研究一个excel插件。

这是插件的构造函数：

        public AddinModule()
        {
            Application.EnableVisualStyles();
            InitializeComponent();
            AddinFinalize += new ADXEvents_EventHandler(FinalizeAddIn);
            AddinInitialize += new ADXEvents_EventHandler(InitializeAddIn);
            OnError += new ADXError_EventHandler(OnErrorAddIn);
            EventDel_CellsChange += new Excel.DocEvents_ChangeEventHandler(CellsChange);
        }

有一个按钮，除其他外，创建一个新的工作表，创建后，将 CellsChange 方法添加到 worksheet.Change 事件中，如下所示：

//some code
                CreateWorkSheet();
                sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
                sheet.Change += EventDel_CellsChange;
//some code

创建工作表时会加载 A 列值。这按预期工作。B 到 E 列的值取决于在前一列中选择的值。我遇到的问题是这些列中的任何一个的更改并不总是触发事件。我显然已经调试了插件，在 cellschange 方法中添加了断点，但结果是一样的：有时它会在断点处中断，有时它不会。当它停止时，一切正常。一旦事件没有被触发，它就不会再次起作用，除非工作表被删除，并通过按前面提到的按钮添加。我错过了什么吗？

最后，cellsChange 方法：

private void CellsChange(Excel.Range Target)
        {

            Excel.Worksheet sheet = null;
            Excel.Range rng = null;

            Excel.Range columns = null;

            Excel.Range column = null;
            try
            {
                foreach (Excel.Range c in Target.Cells)
                {
                    if (c.Row != 1 && c.Column < (int)Columnas.Column6 && c.Value2 != null)
                    {

                        sheet = CurrentInstance.ExcelApp.ActiveSheet as Excel.Worksheet;
                        string[] values = null;
                        string AValue;
                        string BValue;
                        switch (c.Column)
                        {
                            case (int)Columnas.A:
                                values = GetBValues(c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range;
                                break;
                            case (int)Columnas.B:
                                AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                values = GetCValues(AValue, c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.C] as Excel.Range;
                                break;
                            case (int)Columnas.C:
                                AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                BValue = (string)(sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range).Value;
                                values = GetDValues(AValue, BValue, c.Value2.ToString());
                                rng = sheet.Cells[c.Row, (int)Columnas.D] as Excel.Range;
                                break;
                            case (int)Columnas.D:
                                if (c.Value2.ToString() == FUTURE)
                                {
                                    AValue = (string)(sheet.Cells[c.Row, (int)Columnas.A] as Excel.Range).Value;
                                    BValue = (string)(sheet.Cells[c.Row, (int)Columnas.B] as Excel.Range).Value;
                                    string moneda = (string)(sheet.Cells[c.Row, (int)Columnas.C] as Excel.Range).Value;
                                    values = GetEValues(AValue, BValue, moneda, c.Value2.ToString());
                                    rng = sheet.Cells[c.Row, (int)Columnas.E] as Excel.Range;
                                }
                                break;
                            default:
                                break;
                        }


                        if (values != null)
                        {

                            sheet.Unprotect(Type.Missing);
                            columns = rng.Columns;
                            column = columns[1] as Excel.Range;
                            column.Validation.Delete();
                            SetRangeFormat(column, FormatType.Text);
                            column.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertInformation,
                                    Type.Missing, string.Join(Separator, values), Type.Missing);

                            column.Validation.InCellDropdown = true;
                            column.Locked = false;

                            sheet.Protect(Type.Missing, false, true, false, false, true, true, true, false, false, true, false, false, true, true, true);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
            }
            finally
            {
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }

                if (columns != null) Marshal.ReleaseComObject(columns);
                if (rng != null) Marshal.ReleaseComObject(rng);
                if (column != null)
                {

                    Marshal.ReleaseComObject(column);
                }
            }
        }

标签： c#excelworksheet-function