Event Handling Overview
Visual C #. NET uses delegation to handle events from Component Object Model (COM) servers. Delegation is a new concept in Microsoft Visual Studio. NET. For COM events, delegation is a special object that listens for events from a COM server and then forwards them to Visual C # functions. To use delegation, you must create an instance of the object, and then add the object instance to the event that you want to listen to. Each event has a delegate that is specifically designed to convert COM events (using native data types) to standard Microsoft. NET calls (using managed data types).
Create a Visual C #. NET Automation client
To use delegation to process Excel events from an Automation client that is developed by using Visual C #. NET, follow these steps: 1. Start Visual Studio. NET 2002 or Visual Studio. NET 2003. On the File menu, click New, and then click Project. Under Visual C # projects, select Windows application. Name the project Xleventtest, and then click OK. The Form1 is created by default.
2. Add a reference to the Microsoft Excel object library. To do this, follow these steps: A. On the Project menu, click Add Reference.
B. On the COM tab, locate the Microsoft Excel 11.0 Object Library, and then click Select.
C. Click OK in the Add Reference dialog box to accept your selection. If you are prompted to generate a wrapper for the selected library, click Yes.
3. In Solution Explorer, double-click Form1.cs to display the form in Design view.
4. On the View menu, click Toolbox to display the Toolbox, and then add a button to the Form1. Change the button's "Text" property to start Excel.
5. Double-click Start Excel to display the Code window for the form. Add the following code to the button's Click event handler: private void Button1_Click (object sender, System.EventArgs e)
{
Startexcelandsinkevents ();
}
6. Add the following code below the top of the file and under another using statement: using System.Reflection;
Using System.Diagnostics;
Using Excel = Microsoft.Office.Interop.Excel;
7. Add the following code to the Form1 class so that it is located below the Click event handler in step 5://excel Automation variables:
Excel.Application xlapp;
Excel.Workbook Xlbook;
Excel.Worksheet XlSheet1, XlSheet2, Xlsheet3;//excel event delegate variables:
Excel.appevents_workbookbeforecloseeventhandler Eventdel_beforebookclose;
Excel.docevents_changeeventhandler eventdel_cellschange;private void Startexcelandsinkevents ()
{
Start Excel, and then create a new workbook.
xlapp = new Excel.Application ();
Xlbook = XlApp.Workbooks.Add (Missing.Value);
XlBook.Windows.get_Item (1). Caption = "XL Event Test";
XlSheet1 = (Excel.Worksheet) xlBook.Worksheets.get_Item (1);
XlSheet2 = (Excel.Worksheet) XlBook.Worksheets.get_Item (2);
XlSheet3 = (Excel.Worksheet) XlBook.Worksheets.get_Item (3);
Xlsheet1.activate (); Add an event handler for the WorkbookBeforeClose event of the
Application object.
Eventdel_beforebookclose =
New Excel.appevents_workbookbeforecloseeventhandler (Beforebookclose);
Xlapp.workbookbeforeclose + = Eventdel_beforebookclose; The Add an event handler for the Change event of both worksheet objects.
EventDel_CellsChange = new Excel.docevents_changeeventhandler (cellschange); xlSheet1.Change + = EventDel_CellsChange;
Xlsheet2.change + = EventDel_CellsChange;
Xlsheet3.change + = EventDel_CellsChange; Make Excel visible and give the user control.
Xlapp.visible = true;
Xlapp.usercontrol = true;
}private void CellsChange (Excel.Range Target)
{
This was called when the any cell to a worksheet is changed.
Debug.WriteLine ("Delegate:you Changed Cells" +
Target.get_address (Missing.Value, Missing.Value,
Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value) +
"On" + Target.Worksheet.Name);
}private void Beforebookclose (Excel.Workbook Wb, ref bool Cancel)
{
This was called when you choose to close the workbook in Excel.
The event handlers is removed, and then the workbook is closed
Without saving the changes.
Wb.saved = true;
Debug.WriteLine ("delegate:closing the workbook and removing event handlers.");
xlSheet1.Change-= EventDel_CellsChange;
Xlsheet2.change-= EventDel_CellsChange;
Xlsheet3.change-= EventDel_CellsChange;
Xlapp.workbookbeforeclose-= Eventdel_beforebookclose;
}
Test code
1. Press Ctrl+alt+o to display the Output window.
2. Press F5 to build and run the program.
3. On the form, click the Start Excel button. The program starts Excel, and then creates a workbook that has three sheets.
4. Add any data to a cell in any worksheet. Review the Output window in Visual Studio to confirm that the event handler was called.
5. Exit Excel, and then close the form to end the debugging session. Back to top troubleshooting
When you compile your code, you may receive the following compiler error message:
The namespace already contains the definition of "Excel"
You receive this error message if the primary interop assembly (PIA) for Excel is not installed. To resolve this issue, follow these steps: 1. Run the Microsoft Office Setup program, and then install the Excel PIA. In Office setup, the PIA appears as a component under Excel, ". NET Programmability Support."
2. Open your project, delete the references to the Excel interop assemblies, and then repeat step 2 in the "Creating a Visual C #. NET Automation Client" section of this article to correctly reference the PIA.
When you test the code, you may receive the following error message:
An unhandled "System.InvalidCastException" type of exception appears in Interop.Excel.dll.
Additional information: This interface is not supported
For additional information about this error message, click the following article number to view the article in the Microsoft Knowledge Base:
316653 (http://support.microsoft.com/kb/316653/PRB: An error occurred while using WithEvents or delegating to process Excel events from visual Basic. NET or Visual C #. Net
Back to top reference
For additional information, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/library/en-us/dnoxpta/html/vsofficedev.asp(http://msdn.microsoft.com/library/en-us/dnoxpta/html/vsofficedev.aspFor additional information about implementing Excel automation in Visual C #. NET, click the following article number to view the article in the Microsoft Knowledge Base:
302084 (http://support.microsoft.com/kb/302084/How to implement Microsoft Excel Automation in Microsoft Visual C #. NET
302096 (http://support.microsoft.com/kb/302096/How to make Excel run automatically in Visual C #. NET to use an array to populate or get data from a region
302902 (http://support.microsoft.com/kb/302902/) binding the Office Automation server with Visual C #
How to use Visual C #. NET to handle Excel events
