Excel工作表更改事件未触发 [英] Excel worksheet change event is not firing

问题描述

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

I have created excel workbook using .NET interop. The excel workbook is created successfully through my C# code. When the user makes any changes in the excel, I want to do some stuff. I have used the ExcelWorkSheet.Change event. But this event is not firing. Here is my code-

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文件中不会反映任何更改. 请帮帮我. 预先感谢

No change is reflected in the Excel file when the user makes some changes. Please help me out. Thanks in advance

推荐答案

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

The Worksheet_Change event is not global - it only applies to that particular worksheet. In your code you wire up the event handler to the xlSheet1.Change event, then close the workbook and release all the Excel objects.

我将您的代码弹出到表单后面，并对其进行了一些修改.我可以触发该事件，并设置单元格Y2中的公式.我不确定您的情况100％，但是请尝试使用此代码，然后与您自己的代码进行比较.希望对您有所帮助.

I popped your code behind a Form and adapted it slightly. I could get the event to fire and the formula in cell Y2 is set. I'm not 100% sure of your circumstances, but try this code and then compare with your own. Hope it helps.

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;
        }
    }
}

这篇关于Excel工作表更改事件未触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！