.NET/Link Example: Controlling Excel

This example demonstrates calling a COM object from Mathematica using .NET/Link. The COM object used is the Microsoft Excel application, and of course you must have Excel installed for this to work. You do not need to have a very recent version of Excel--this will probably work for at least as far back as Excel 97.

.NET/Link allows you to control COM objects via their IDispatch interface, otherwise known as ActiveX Automation, or just Automation. This means that the same sorts of things that can be done with scripting languages like Visual Basic and VBScript can be done in Mathematica. Microsoft Excel is a good example of an application that exposes a very complete and useful object model to Automation. If you have ever programmed in Excel’s Visual Basic for Applications (VBA), you know that everything that can be done in Excel can be done from VBA. Mathematica can use the same Automation services that VBA uses, and so it is equally powerful. As can be seen by looking at the code, it is also as easy to program these tasks in Mathematica as it is in Visual Basic.

This example presents a function called ExcelPieChart, which produces a pie chart in Excel as its output.

To try this example, evaluate all the initialization cells (you can do this with the menu command Kernel/Evaluation/Evaluate Initialization). Then go to the Example section.

Code and Explanation

The following cell compares two programs that create a simple pie chart in Excel. The code on the left is the Visual Basic for Applications code that you would write if you were working within Excel. The code on the right is a Mathematica program that does the same thing. You can see that these two programs are essentially identical. Note only does .NET/Link give you the same power as Visual Basic, it provides the same convenience to the programmer.

({{Visual Basic

Set wb = Application.Workbooks(1)
Set ws = wb.Worksheets(1)
ws.Range("A1").Value = "North"
ws.Range("A2").Value = "South"
ws.Range("A3").Value = "East"
ws.Range("A4").Value = "West"
ws.Range("B1").Value = 5.2
ws.Range("B2").Value = 10
ws.Range("B3").Value = 8
ws.Range("B4").Value = 20
Set sourceRange = ws.Range("A1:B4")
Set chrt = wb.Charts.Add
chrt.ChartWizard sourceRange, xl3DPie, 7, xlColumns,
		 1, 0, 2, "Sales Percentages", Mathematica

wb=excel@Workbooks@Item[1];
ws=wb@Worksheets@Item[1];
ws@Range["A1"]@Value="North";
ws@Range["A2"]@Value="South";
ws@Range["A3"]@Value="East";
ws@Range["A4"]@Value="West";
ws@Range["B1"]@Value=5.2;
ws@Range["B2"]@Value=10;
ws@Range["B3"]@Value=8;
ws@Range["B4"]@Value=20;
srcRange=ws@Range["A1:B4"];
chrt=wb@Charts@Add;
chrt@ChartWizard[srcRange,-4102,7,2,
		1,0,2,"Sales Percentages"];}})

The ExcelPieChart program developed below is a slightly generalized version of the example code in the above cell.

Needs["NETLink`"]

ExcelPieChart[{labels:{__String}, values:{__?NumberQ}}, title_String] :=
	Module[{excel, wb, ws},
		NETBlock[
			InstallNET[];
			(* We use CreateCOMObject to launch an instance of Excel. It will not be visible until
			   we make it so at the end of the program. Use GetActiveCOMObject instead of CreateCOMObject
			   if you want to attach to a currently-running instance of Excel instead of launching a new one.
			   The argument to CreateCOMObject is a COM ProgID or CLSID (most users will use the ProgID,
			   which is readable text, like "Excel.Application". The excel object returned is a .NET
			   object that "wraps" a COM object and makes it look like a .NET object to callers.
			*)
			excel = CreateCOMObject["Excel.Application"];
			If[!NETObjectQ[excel],
				Return[$Failed]
			];
			
			(* Now we begin working with the excel COM object. Consult Excel's documentation for its object
			   model to see what properties and methods are supported and how to use them.
			*)
			wb = excel@Workbooks@Add[];
			ws = wb@Worksheets@Item[1];
			
			(* Here we fill the range A1 ... An with the pie slice labels. *)
			MapThread[(ws@Range[#1]@Value = #2)&,
				{Table["A" <> ToString[i], {i, Length[labels]}], labels}
			];
			(* Here we fill the range B1 ... Bn with the values the control the slice sizes. *)
			MapThread[(ws@Range[#1]@Value = #2)&,
				{Table["B" <> ToString[i], {i, Length[values]}], values}
			];
			srcRange = ws@Range["A1:" <> "B" <> ToString[Length[values]]];
			
			(* Now create a new Chart object and call the ChartWizard to build the chart. *)
			chrt = wb@Charts@Add[];
			chrt@ChartWizard[srcRange, -4102, 7, 2, 1, 0, 2, title];
			
			(* This makes the Excel application visible. *)
			excel@Visible = True;
		];
		
		(* The Excel instance we launched will not quit until all the COM resources that are being held by
		   .NET objects we created are released. .NET/Link provides a function called ReleaseCOMObject that
		   can be used to force .NET objects that "wrap" COM objects to release those COM objects. We could,
		   for example, call ReleaseCOMObject[excel] here, but that is not enough because we have created
		   many COM objects (Range objects, for example). Rather that try to keep track of them all and call
		   ReleaseCOMObject on every one, it's easier to rely on the fact that COM resources are freed when
		   their .NET wrapper objects are freed by the garbage collector. Therefore we use a NETBlock to
		   ensure that all objects are marked for release when the NETBlock ends, and then explictly call
		   the garbage collector to force the objects to be freed in a timely manner.
		*)
		LoadNETType["System.GC"];
		GC`Collect[];
	]

Interop Assembles

.NET/Link can work with "raw" COM objects or COM objects that have a so-called "interop assembly" created from them. The Microsoft .NET Framework SDK includes a tool called tlbimp.exe that you can use to import a COM type library and spit out a .NET assembly that exposes the COM types in the library as .NET objects. The .NET SDK is freely availablefrom Microsoft, but it is a separate download from the .NET Framework runtime, which is all that you need to run, but not actually compile, .NET programs. Although creating an interop assembly from a COM type library is not necessary to use the COM objects in the library from Mathematica, it is recommended if you are doing serious work. (Note that essentially the same functionality provided by the command-line tool tlbimp.exe is provided by the .NET/Link Mathematica function LoadCOMTypeLibrary. You can use that function to create and load an interop assembly "on the fly" without getting the .NET SDK.)

Some interop assemblies are specially designated as "primary interop assemblies", or PIAs. Typically, the vendor of the COM type library will provide a PIA for that type library. PIAs are strongly-named assemblies and can be installed in the Global Assembly Cache. Microsoft makes PIAs available for the entire Office XP suite, and anyone working with Office XP using .NET/Link should definitely obtain and install them. A good place to start is this link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp.

Once you have created or obtained an interop assembly, you load it and use it in .NET/Link like any other assembly. Two advantages of using an interop assembly are: (1) The types can be inspected just like other native .NET types using the .NET/Link NETTypeInfo function, and (2) You can get access to constants and COM events defined in the type library. You may have noticed the cryptic constants in the chart@ChartWizard[] call in the ExcelPieChart program, vs. the descriptive names xl3DPie and xlColumns in the Visual Basic for Applications version. You can discover the values of these constants in VBA by executing, for example, ?xl3DPie in the Immediate window of the Excel Visual Basic Editor. But if you have installed the Excel PIA, these constants are available by name. This means that you could use the following code:

LoadNETType["Microsoft.Office.Interop.Excel.XlChartType"];
	chrt@ChartWizard[srcRange, XlChartType`xl3DPie, 7, 2, 1, 0, 2, title]

How did I know that the xl3DPie constant was in an enumeration called Microsoft.Office.Interop.Excel.xlChartType? I used NETTypeInfo on the interop assembly object and did some guesswork from there. Unfortunately, there is not yet much documentation on the Office PIAs. I never did find out where the xlColumn constant is defined.

If you have a PIA installed for a given COM object, then .NET/Link will automatically return the correct .NET object from that PIA when you call CreateCOMObject or GetActiveCOMObject.

Example

data = {{"label1", "label2", "label3", "label4"}, {45, 15, 30, 10}};

This will launch Excel and make the chart appear:

ExcelPieChart[data, "Chart Title"]