c# - Excel 工作表更改事件未触发

Question

我使用 .NET 互操作创建了 Excel 工作簿。通过我的 C# 代码成功创建了 excel 工作簿。当用户在 excel 中进行任何更改时，我想做一些事情。我使用了 ExcelWorkSheet.Change 事件。但是这个事件没有触发。这是我的代码-

using Excel = Microsoft.Office.Interop.Excel;    
public class xxx  
{   

    static Excel.Application xlApp;  
    static Excel.Workbook xlWorkBook;  
    static Excel.Worksheet xlWorkSheet;  
    static Excel.Worksheet xlWorkSheet1;
    static Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;   
    public static void ExportToExcel()
    {           
        xlApp = new Excel.ApplicationClass();
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Add(misValue); 
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);  
        ---------------- data is dumped to the excel here----------------  
        ((Microsoft.Office.Interop.Excel._Worksheet)xlWorkSheet).Activate();
        xlApp.EnableEvents = true;
        EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler(Worksheet_Change);  
        xlWorkSheet.Change += EventDel_CellsChange;
        xlWorkBook.SaveAs("D:\\Test.xlsx", Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);  
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();
        releaseObject(xlWorkSheet1);
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);  

        System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
        response.ClearContent();
        response.Clear();
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment; filename=Test.xlsx;");
        response.TransmitFile(("D:\\Test.xlsx");
        response.Flush();
        response.End();  
    }  

    public static void Worksheet_Change(Excel.Range Target)
    {
        try
        {
            xlApp.EnableEvents = false;                
            Excel.Range range = xlWorkSheet.get_Range("Y2");                
            range.Formula = "=A2";                
        }
        catch (Exception ex)
        {               
        }
        finally 
        {
            xlApp.EnableEvents = true;
        }
    }  
}    

当用户进行某些更改时，Excel 文件中不会反射(reflect)任何更改。请帮帮我。提前致谢

Answer 1

Worksheet_Change 事件不是全局的 - 它只适用于特定的工作表。在您的代码中，您将事件处理程序连接到 xlSheet1.Change 事件，然后关闭工作簿并释放所有 Excel 对象。

编辑:我把你的代码放在一个表单后面并稍微调整了一下。我可以触发事件并设置单元格 Y2 中的公式。我不能 100% 确定您的情况，但请尝试使用此代码，然后与您自己的代码进行比较。希望对您有所帮助。

public partial class Form1 : Form
{
    private static Excel.Application xlApp;

    private static Excel.Workbook xlWorkBook;

    private static Excel.Worksheet xlWorkSheet;

    private static Excel.Worksheet xlWorkSheet1;

    private static Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        xlApp = new Excel.Application();
        xlApp.Visible = true;
        object misValue = System.Reflection.Missing.Value;
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        xlWorkSheet1 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
        //---------------- data is dumped to the excel here----------------  
        ((Microsoft.Office.Interop.Excel._Worksheet)xlWorkSheet).Activate();
        xlApp.EnableEvents = true;
        EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler(Worksheet_Change);
        xlWorkSheet.Change += EventDel_CellsChange;   
    }

    public static void Worksheet_Change(Excel.Range Target)
    {
        try
        {
            xlApp.EnableEvents = false;
            Excel.Range range = xlWorkSheet.get_Range("Y2");
            range.Formula = "=A2";
        }
        catch (Exception ex)
        {
        }
        finally
        {
            xlApp.EnableEvents = true;
        }
    }
}