1. The introduction.

Many developers sooner or later collide with tasks which mean using of Microsoft Excel (further under the text simply Excel). In these cases it is possible to apply the utility tlbimp.exe, to import library of types, to add the reference to it and after that namespace of "Excel" is accessable to the application containing all wrapper classes, around the "interiors" of Excel. Or it is even easier, it is possible to add the reference to COM-objects Excel in Visual Studio, and it will make all itself. All this is good, certainly. And it is simple. But sometimes there are situations when described in brief "early binding" are unacceptable. For example, your application is located on an Internet site, hence, it will be necessary to reduce its size. When the "early binding" is used, the two satellite assemblies will be generated - Microsoft.Office.Interop.Excel.dll and Microsoft.Vbe.Interop.dll which contain wrapper classes. The total size of these assemblies exceeds one megabyte, and, in spite of the fact that .NET assemblies are usually compressed well by archivers, it is obviously inexpedient "to haul" all wrapper classes of Excel for the sake of use of two-three methods.

In these cases the so-called "Late binding" should be used, which allows to call the methods of Excel without using wrappers. So there will be no satellite assemblies of wrappers.

There is no sence to describe "late binding" in this article, because there are a lot of materials in the literature, as well as on the Internet. All examples are written using C#, but I hope the programmers using other .NET-languages can understand a code without special efforts.

2. Starting and closing of Excel.

Starting of Excel and its correct finishing is the very first problem, which needs to be solved by to the programmer if he is going to use Excel in the applications. Probably, Excel it is already started, and operation of start is not necessary, it is enough to receive the reference on Excel and to begin work with it. In reception of the reference to the launched copy of Excel the one unpleasant moment connected to a mistake in the Excel application (which, like, it is corrected in MSOffice 2003) [2] is covered. This situation is in detail described at the end of this chapter.

First of all it is necessary to connect two namespaces to the application:

using System. Runtime. InteropServices;

using System. Reflection;

In these namespaces the types necessary for the organization of late binding are described. One of them is the Marshal class, which gives rich opportunities for the organization of interaction between managed and unmanaged-objects.

For reception of the reference to the Excel process, it is necessary to know the GUID of Excel. However it is possible to reach the goal much easier - to use program identifier of Excel: " Excel. Application ".

For reception of the reference on working Excel it is possible to call the static method GetActiveObject () of the Matshal class:

string sAppProgID = " Excel. Application ";

object oExcel = Marshal. GetActiveObject (sAppProgID);

If the Excel it is already started (the COM-object of Excel is present in memory) the call of the given method will return the reference to object - reflecting of Excel in .NET environment, which can be used for the further work. If Excel it is not started, the exception will be generated.

For start of Excel it is necessary to take advantage of the Activator class, described in the System namespace:

string sAppProgID = " Excel. Application ";

// We receive the reference to interface IDispatch

Type tExcelObj = Type. GetTypeFromProgID (sAppProgID);

// We start Excel

object oExcel = Activator. CreateInstance (tExcelObj);

After reception of the reference/link or start of the Excel, the all objective model of the application becomes accessible. From the point of view of the programmer it looks in this way:

Figure 1. The Object model of Excel.

To use the objects of Excel it is necessary to receive the reference to its collection of books. The collection of books gives the access to any open book. Each book has a collection of pages, the reference on which it is also to be received for access to concrete page. I want to notice at once, that access to books and pages can be received both on their name, and on their order number.

The numeration of books and pages in a collection begins from one, instead of from zero (as it is accepted to number arrays in .NET). I shall note, that though in VBA there is an Option Base directive, in our case it does not influence the order of numeration.

To make a correct disposing of all objects, received by the late binding, it is necessary to call the method ReleaseComObject() of the Marshal class:

// Destruction of object Excel.

Marshal. ReleaseComObject (oExcel);

// We cause the collector of dust for immediate clearing memory

GC.GetTotalMemory (true);

If call of GC.Collect() does not help, try clearing memory by this way. If this operation is ignored, the objects of Excel will be remained in memory, even after the finishing of the application. Thus even if the user has closed the Excel, there would be the process of Excel in the list of the started processes of the Windows Task Manager. In this case, if the .NET-application is started again, and tries to receive the reference on working Excel, it will be possible without problems. But, most likely, in this case, the setting of the "Visible" property to the value of true, main window Excel will be incompletely invalidated. On the screen there will be only panels of tools and a frame of the main window. This situation was quite often observed with Excel 2000 and Excel XP. In Excel 2003 the similar phenomena has not been noticed yet.

But, nevertheless, if your program receives the references to any objects of Excel, you necessarily should call the ReleaseComObject () method of the Marshal class.

And perform the cleaning of the heap before the finishing of work with Excel:

GC.GetTotalMemory (true);

3. Working with books and sheets.

Late binding means, that type of object with which it is planned to work, is unknown, and it means, that it is impossible to address directly to its methods and fields, using the operator ".". Therefore for a call of a method it is necessary to know its name and the list of formal parameters which it accepts. For a call of a method, the method InvokeMember () of the Type class is used. Therefore it is enough to receive the reference to the instance of the Type class, which described the type of instance with which the late binding is established. After that the InvokeMember() method should be used. I will not describe this method in details; it is well enough described in the technical documentations. I will note the most necessary only.

The InvokeMember() method is overloaded, and has three variants.

public object InvokeMember (

 string name, BindingFlags flags, Binder binder,

 object target, object [] args);

public object InvokeMember (

 string name, BindingFlags flags, Binder binder,

 object target, object [] args, CultureInfo info);

public abstract object InvokeMember (

 string name, BindingFlags flags, Binder binder,

 object target, object [] args, ParameterModifier [] modifiers,

 CultureInfo info, string [] namedParameters);

Here the first variant of a method will be used only. As the first parameter the method receives the string name of a method (a field, property) that object with which binding is established. Thus in the name there should not contain spaces or other illegal characters, besides this parameter is sensitive to the register.

The second parameter accepts the flags describing the mode of binding. In this case only the following flags will be necessary:

§ BindingFlags. InvokeMethod - to find a method, to determine its point of an entrance, and to call it having passed an array of actual parameters to it.

§ BindingFlags. GetProperty - to get the value of the property.

§ BindingFlags. SetProperty - to set the value of the property.

The third parameter - binder - is set to null.

Through the fourth parameter - target, is the reference to object, which method it is being called.

The fifth parameter - args is an array of parameters which is passed to a method by the late binding, or an array, which contains the only element - the value of property.

The InvokeMember() method returns the result of a method or the value of property.

For management of books and sheets, first of all it is necessary to receive the reference to their collections. For reception of the reference to a collection of books it is necessary to execute the following code (it is considered, that the reference on oExcel is successfully received):

object oWorkbooks = oExcel. GetType () .InvokeMember (

 "Workbooks", BindingFlags. GetProperty, null, oExcel, null);

The object oWorkbooks also is the managed-reference to a collection of books.

For reception of access to the concrete book the following code using a collection of books is carried out:

// Access to the book on its order number

// Creation of the array of parameters

object [] args = new object [1];

// The first book will be accessed

args [0] = 1;

// We receive the reference to the first book in collection Excel

object oWorkbook = oWorkbooks. GetType () .InvokeMember (

 "Item", BindingFlags. GetProperty, null, oWorkbooks, args);

// Access to the book under its name

// (pay attention, that expansion in the name is not underlined)

object [] args = new object [1];

// We specify the name of the book to which we want to get access

args [0] = "Êíèãà1";

// We receive the reference to the first book in collection Excel

object oWorkbook = oWorkbooks. GetType () .InvokeMember (

 "Item", BindingFlags. GetProperty, null, oWorkbooks, args);

If the book with the specified name does not exist, then an exception is generated.

To open, close or create the book, we will call the corresponding methods of a collection of books oWorkbooks, the reference on which is already successfully received.

For creation of the new book the object oWorkbooks has some overloaded variants of the Add method. If this method is called without parameters, the new book with a name by default, containing the default amount of sheets, will be created.

// We create the new book

object oWorkbook = oWorkbooks. GetType () .InvokeMember (

 "Add", BindingFlags. InvokeMethod, null, oWorkbooks, null);

For creation of the book on the basis of a pattern it is enough to pass a full name of the file containing this pattern:

// We bring in a file of parameters a name of a file

object [] args = new object [1]; args [0] = "D:\MyApp\Templates\invoice.xls";

// We create the new book

object oWorkbook = oWorkbooks. GetType () .InvokeMember (

 "Add", BindingFlags. InvokeMethod, null, oWorkbooks, args);

For opening a file with the book method Open of object oWorkbooks is used:

// Opening a file d:\book1.xls

// We bring in a file of parameters a name of a file

object [] args = new object [1];

args [0] = "D:\book1.xls";

// We taste to open the book

object oWorkbook = oWorkbooks. GetType () .InvokeMember (

 "Open", BindingFlags. InvokeMethod, null, oWorkbooks, args);

Closing the book is performed by using the Close method of object oWorkbook. Thus it accepts some escapable parameters. We will consider two variants (pay attention that we call the Close method of a book, instead of a collection of books, and the oWorkbook will be passed as the target object, instead of oWorkbooks):

// A variant # 1. We close the book with acceptance of all changes

object [] args = new object [1];

// With acceptance of all changes

args [0] = true;

// We try to close the book

oWorkbook. GetType () .InvokeMember (

 "Close", BindingFlags. InvokeMethod, null, oWorkbook, args);

// A variant # 2. We close the book with acceptance of all changes

object [] args = new object [2];

args [0] = true;

// And under the certain name

args [1] = @"D:\book2.xls ";

// We try to close the book

oWorkbook. GetType () .InvokeMember (

 "Close", BindingFlags. InvokeMethod, null, oWorkbook, args);

It is necessary to note, that the book will be saved only if any changes will be made in this book. If the book is created and and tried to be saved immideately without any changes, the Excel will just close the book making no saving actions.

To save changes in the book, it is enough to call its Save or SaveAs method, having passed the name of the file as the parameter.

// Simply we keep the book

oWorkbook. GetType () .InvokeMember (

 "Save", BindingFlags. InvokeMethod, null, oWorkbook, null);

// We set parameters of method SaveAs - a name of a file

object [] args = new object [2];

args [0] = @"d:\d1.xls ";

// We keep the book in a file d:\d1.xls

oWorkbook. GetType () .InvokeMember (

 "SaveAs", BindingFlags. InvokeMethod, null, oWorkbook, args);

// Simply we keep the working book. By default the new book without

// Changes it will be saved in a « my Documents » folder

// under the current Windows account

oWorkbook. GetType () .InvokeMember (

 "Save", BindingFlags. InvokeMethod, null, oWorkbook, null);

For work with sheets it is necessary to get access to their collection. Naturally, by this moment it is necessary to have the reference to the working book. For reception of the reference to a collection of pages it is necessary to call the Worksheets property of the working book:

object oWorksheets = oWorkbook. GetType () .InvokeMember (

 "Worksheets", BindingFlags. GetProperty, null, oWorkbook, null);

The oWorksheets object is a managed-reference to a collection of pages of the current book. Knowing the reference to this collection, it is possible to get access to concrete page by its name or an order number (it is similar to a collection of working books):

// We set a serial number of page - 1

object [] args = new object [1];

args [0] = 1;

// We receive the reference to this page

object oWorksheet = oWorksheets. GetType () .InvokeMember (

 "Item", BindingFlags. GetProperty, null, oWorksheets, args);

// We set a name of page

object [] args = new object [1];

args [0] = "Sheet1";

// We receive the reference to page with name "Sheet1"

oWorksheet = oWorksheets. GetType () .InvokeMember (

 "Item", BindingFlags. GetProperty, null, oWorksheets, args);

4. Work with pages. The Range Object. Using of record of macroses for automation of Excel.

The sheet has the reference to the Range object, which represents a range of cells as a matter of fact. Through the Range object, it is possible to get access to any cell and its properties. But the Range object contains a lot of methods, and for late binding it is necessary to know not only a format of formal parameters passed to it, but also the exact name of a method (or property which is a combination of methods). That is to say it is important to know the method's signature to call it by the late binding. Till now we used the simple methods such as Open, Close, Save, generally, everithing is clear with them. They have some parameters, and the list of these parameters is intuitively clear.

To find out, what methods are supported with object Range, it is possible to take advantage of "OleView" utility, which is intended specially for it. It is included to the Visual Studio package, and located at [Visual Studio's installation folder] \Common7\Tools folder. If you have not installed this utility with VS, it can be found in the Internet. Besides, it is possible to open and research the type library of Excel with the help of disassembler/decompiler, having imported it with the help of the utility tlbimp.exe. (For example, ildasm.exe, Reflector or Anakrino). The disassembler will show object the Range object and all its methods.

But there is more simple way which will allow to save the time essentially. It is Excel itself, being more exact, its system of the macros recording. For example, it is necessary to format cells as how it is shown in the figure # 2:

Figure 2. Result macros execution.

To record the macros it is necessary to start Excel, to turn on the record of macros and to format the specified cells. Recorded macros will look as follows:

Sub Ìàêðîñ1 ()

'

' Ìàêðîñ1 Ìàêðîñ

' Ìàêðîñ it is written down 4/17/2005 (Powerful)

'

 Range ("B3") .Select

 With Selection. Interior

 .ColorIndex = 45

 .Pattern = xlSolid

 End With

 Range ("C3") .Select

 Selection. Font. ColorIndex = 3

 Range ("B3") .Select

 ActiveCell. FormulaR1C1 = "Ïðèâåò"

 Range ("C3") .Select

 ActiveCell. FormulaR1C1 = " èç NET! "

 Range ("B3:C3") .Select

 Selection.Borders(xlDiagonalDown).LineStyle = xlNone

 Selection.Borders(xlDiagonalUp).LineStyle = xlNone

 With Selection. Borders (xlEdgeLeft)

 .LineStyle = xlContinuous

 .Weight = xlThin

 .ColorIndex = xlAutomatic

 End With

 With Selection. Borders (xlEdgeTop)

 .LineStyle = xlContinuous

 .Weight = xlThin

 .ColorIndex = xlAutomatic

 End With

 With Selection. Borders (xlEdgeBottom)

 .LineStyle = xlContinuous

 .Weight = xlThin

 .ColorIndex = xlAutomatic

 End With

 With Selection. Borders (xlEdgeRight)

 .LineStyle = xlContinuous

 .Weight = xlThin

 .ColorIndex = xlAutomatic

 End With

 With Selection. Borders (xlInsideVertical)

 .LineStyle = xlContinuous

 .Weight = xlThin

 .ColorIndex = xlAutomatic

 End With

End Sub

In this macros the call of Select method of the Range object is frequently used. But in fact, the cells can be addressed directly, using their coordinates. The Select method simply redefines the references, which will be returned by the Selection property. The type of the Selection object is range also. Thus, the task essentially becomes simpler. It is necessary to receive simply references to corresponding Range objects, to get access to its included objects and to make a call of corresponding methods or properties, using the InvokeMember() method.

For example, the following part of a code paints the background of B3 cell in orange color with the solid pattern, and sets the color of the text of C3 cell in red:

...

 Range ("B3") .Select

 With Selection. Interior

 .ColorIndex = 45

 .Pattern = xlSolid

 End With

 Range ("C3") .Select

 Selection. Font. ColorIndex = 3

...

Let's try to create a code for this part of the macros. We admit that references to the necessary book and page have been successfully received.

The reference to the sheet is stored in a oWorksheet variable.

// We receive the reference to cell B3 (more precisely on object Range ("B3"))

object oRange = oWorksheet. GetType () .InvokeMember (

 "Range", BindingFlags. GetProperty, null, oWorksheet, new object [] {"B3"});

// We receive the reference to object Interior

object oInterior = oRange. GetType () .InvokeMember (

 "Interior", BindingFlags. GetProperty, null, oRange, null);

// We set painting (Analogue of a call

// Range ("B3") .Interior. ColorIndex)

oInterior. GetType () .InvokeMember (

 "ColorIndex", BindingFlags. SetProperty, null, oInterior, new object [] {45});

// We set the a pattern of painting (Pattern = xlSolid)

/* To find out the value of a xlSolid constant, it is possible to look the documentation, to use the import of library of types described above, and it is possible to step simply the macros in Visual Basic Debugger and to look value in the control of variables that will essentially save your time. */

// We set parameter xlSolid = 1;

object [] args = new object [] {1}

// We set the Pattern property to xlSolid value

oInterior. GetType () .InvokeMember (

 "Pattern", BindingFlags. SetProperty, null, oInterior, args);

To set the text, it is possible to use property Value of object Range.

oRange.GetType().InvokeMember(

 "Value", BindingFlags.SetProperty, null, oRange, new object[]{"Ïðèâåò"});

I will not assort the further code. I just advise the readers to experiment themselves with settings of properties of Excel from .NET-applications, by analogy to the examples above. And now we are going to examine the way of interceptions of the Excel events using the late binding.

5. Interception of Excel events.

Interception of the Excel events allows to monitor its state and to supervise some actions. For example, it is possible to trace closing of the working book and to make correct disconnection procedures, having made clearing of memory and other nececcary actions. To explain, how to intercept events, I will make a little deviation to events of COM-objects. In this deviation I assume, that the reader is a little familiar with architecture of COM.

If the object wants to receive events of other COM-object, it should notify the source of events, having registered itself in the list of event sinks. For this purpose the COM technology defines the IConnectionPointContainer interface containing the FindConnectionPoint method. By the call of the FindConnectionPoint() method of the COM-object, the sink object receives "a point of connection", which is a reference to the IConnectionPoint interface. Then the sink object should register its realization of the IDispatch inteeface, using the Avise() method of the IConnectionPoint interface. Excel exports the following interface which should be realized by the class, which receives events.

interface

["00024413-0000-0000-C000-000000000046"]

{

 DispId (0x61d)]

 void NewWorkbook (object Wb);

 DispId (0x616)]

 void SheetSelectionChange (object Sh, object Target);

 DispId (0x617)]

 DispId (1560)]

 void SheetBeforeRightClick (object Sh, object Target, ref bool Cancel);

 DispId (0x619)]

 void SheetActivate (object Sh);

 DispId (0x61a)]

 void SheetDeactivate (object Sh);

 DispId (0x61b)]

 void SheetCalculate (object Sh);

 DispId (0x61c)]

 void SheetChange (object Sh, object Target);

 DispId (0x61f)]

 void WorkbookOpen (object Wb);

 DispId (0x620)]

 void WorkbookActivate (object Wb);

 DispId (0x621)]

 void WorkbookDeactivate (object Wb);

 DispId (1570)]

 void WorkbookBeforeClose (object Wb, ref bool Cancel);

 DispId (0x623)]

 void WorkbookBeforeSave (object Wb, bool SaveAsUI, ref bool Cancel);

 DispId (0x624)]

 void WorkbookBeforePrint (object Wb, ref bool Cancel);

 DispId (0x625)]

 void WorkbookNewSheet (object Wb, object Sh);

 DispId (0x626)]

 void WorkbookAddinInstall (object Wb);

 DispId (0x627)]

 void WorkbookAddinUninstall (object Wb);

 DispId (0x612)]

 void WindowResize (object Wb, object Wn);

 DispId (0x614)]

 void WindowActivate (object Wb, object Wn);

 DispId (0x615)]

 void WindowDeactivate (object Wb, object Wn);

 DispId (0x73e)]

 void SheetFollowHyperlink (object Sh, object Target);

 DispId (0x86d)]

 void SheetPivotTableUpdate (object Sh, object Target);

 DispId (2160)]

 void WorkbookPivotTableCloseConnection (object Wb, object Target);

 DispId (0x871)]

 void WorkbookPivotTableOpenConnection (object Wb, object Target);

}

Thus, the class - receiver of events should realize this interface and register itself, using IConnectionPointContainer and IConnectionPoint. The library of base classes .NET already defines the managed-versions of interfaces: for IConnectionPointContainer it is   UCOMIConnectionPointContainer, and for IConnectionPoint - UCOMIConnectionPoint which are located in the System. Runtime. InteropServices namespace.

Registration process of events will look so:

// We declare references on IConnectionPointContainer

UCOMIConnectionPointContainer icpc;

// And on IConnectionPoint

UCOMIConnectionPoint icp;

// We receive the reference on Excel

FExcel = Marshal.GetActiveObject("Excel.Application");

// We receive the reference to interface IConnectionPointContainer

icpc = (UCOMIConnectionPointContainer) FExcel;

// We receive « a point of connection »

Guid guid = new Guid("00024413-0000-0000-C000-000000000046");

icpc. FindConnectionPoint (ref guid, out icp);

// We register a class - the receiver of events which realizes

// the interface with ["00024413-0000-0000-C000-000000000046"] GUID.

// Thus our class receives the unique identifier

// cookie, which needs to be kept to have

// An opportunity to be disconnected from a source of events

icp. Advise (ExcelEventSink, out cookie);

For disconnection from events it is enough to cause the method Unadvise(), passing the cookie idenfier to it as a parameter:

icp. Unadvise (cookie);

6. The conclusion.

In this article by the example of work with MS Excel interaction COM and NET with use of late linkage was considered. Using the similar approach, it is possible to organize management of any COM-server.

In the C# - source file enclosed there is a class which provides Excel events for .NET application.

The literature

1. Andrew Troelsen. C# and .NET platform. The Library of the programmer. - SPb. Piter, 2004.

2. N.Elmanova, S.Trepalin, A.Tentser. Delphi 6 and the COM technology - SPb. Piter, 2002.

3. MSDN.

The article was prepared using the RSDN Authoring Pack v. 1.6. http://www.rsdn.ru/

Examples of classes.

Excel Event Provider http://www.pfsoft.com/articles/ExcelEventProvider.cs;