It is possible to integrate .NET code with Microsoft Excel. In the following I will show how create an Excel worksheet that uses SuanShu.NET.
To integrate Visual Sudio with .NET, you need Visual Studio 2008 Professional or higher, as well as Microsoft Office 2007 or higher. Please also ensure that during installation of Visual Studio, you have installed the component Visual Studio Tools for Office Runtime. In this tutorial we will be using Visual Studio 2010 Professional with C#.
Examples Project
Instead of following the tutorial below, you can download a project containing the described examples [here http://redmine.numericalmethod.com/projects/public/repository/show/SuanShu.NET]. You may also use this project as a starting point for your own application.
The example project for Excel contains some methods that convert between Excel ranges to Vector and Matrix datatypes in SuanShu. If you are planning of using SuanShu in Excel, it is highly recommended that you look at the examples, to see how this is done.
Setting Up the Project
First we create a new project. If you have everything set up as in SuanShu/DotNet, you should be able to create a Project from an Excel Workbook template. Also don’t forget to enter an appropriate name for the project below the template selection.
On the next screen, simply click ‘OK’.
Having created a new project, you must first add SuanShu.NET to the References again. To do this, please follow the instructions in the previous section. This time however, the license file should be located in your ‘My Documents’ folder. An explanation of how to change the directory in which SuanShu looks for the license file is given in the Tips and Tricks section.
The Worksheet
Let’s now create a simple example program to demonstrate some simple ways of interacting with the Excel worksheet. In this example we will solve a system of linear equations (of the form Ax = b).
First we will set up our worksheet to hold the input and output of our function. This can be done by editing the worksheet like you would from Excel. Please add the following labels and example numbers:
[one_half]
[/one_half]
[one_half_last]
[/one_half_last]
We also add a button to trigger the computation. Open the toolbox (if it isn’t already open, press CTRL + W, X) and drag a Button onto the worksheet.
The button can be renamed by changing its ‘Text’ property in the ‘Properties’ dialog, which by default is in the bottom right corner of the screen.
A useful feature is to assign fields names, which we can then reference in the cods. This is done by right clicking the cell and selecting ‘Define Name…’. Let’s call the cells beneath ‘x:’, ‘b:’ and ‘A:’ ‘xStart’, ‘bStart’ and ‘AStart’ respectively. To manage defined names, you can use the ‘Name Manager’ in the Formulas tab.
Pressing F7 will now take you to the code view. To return to the designer view, press SHIFT + F7. As you can see, a method stub for the button has already been created for you. Let’s now insert the code that solves the linear equation.
Coding
To simplify the integration of SuanShu with Excel we provide a file called SuanShuExcel, which has methods to read and write scalars, vectors and matrices from the worksheet. If you have started your project from scratch, you should add the file to your project.
Having done this, we can solve the linear equation using the following simple code.
private void button1_Click(object sender, EventArgs e) {
// Read b and A starting at the previously named cells bStart and AStart
Vector b = SuanShuExcel.ReadVector(bStart.Row, bStart.Column, this);
Matrix A = SuanShuExcel.ReadMatrix(AStart.Row, AStart.Column, this);
// Precision parameter
double epsilon = 1E-15;
// Solve the linear equation
LinearSystemSolver solver = new LinearSystemSolver(epsilon);
LinearSystemSolver.Solution solution = solver.solve(A);
Vector x = solution.getParticularSolution(b);
// Write the result starting at the previously named cell xStart
SuanShuExcel.WriteVector(x, xStart.Row, xStart.Column, this);
}
If you are unsure about what any of these do, please refer to the XML comments in SuanShuExcel or the documentation below for SuanShu.
Running the Project
You are now ready to run the project by pressing F5, as you would for a normal Visual Studio project. When reopening the project, please make sure you open the (.sln) Visual Studio solution and not the (.xlsx) worksheet.
After building a ‘Release’ version (see image below) of a solution, it can then be run by opening the worksheet in the bin\Release folder.
Another Example
Another example (matrix multiplication) can be found in the examples project. It demonstrates how to automatically recompute the result when the worksheet is changed, how to write matrices and how to use the this.Cells and this.Range classes to clear a range of cells.
Using Used Defined Functions in Excel
Instead of linking a Visual Studio project with an Excel worksheet, you can also define User Defined Functions, which can be used in formulae, much like Excel’s built-in functions. We provide a project that you can use as a starting point to develop your own UDFs. If you want to create a similar project from scratch or are unsure how this project was created, there is a thorough explanation here.
The project is an ‘Automation Add-In’, enabling Excel to call functions on COM objects from cells on the worksheet. There are other ways of defining UDFs via .NET, but this is the officially supported and for our purposes simplest way.
Class Structure
The interop protocol between .NET and Excel is called Component Object Model (COM). COM only looks at interfaces rather than base classes, so for COM to recognise our methods we first have to perform the following steps:
- We define an interface containing definitions of our UDFs
- Both the interface and the class need the ComVisible(true) attribute
- Furthermore our class needs to have Guid via the Guid attribute (you can generate Guids using the Tools->Create Guid tool in Visual Studio) and have its interface specified via ComDefaultInterface(typeof(_our_interface)). COM doesn’t care whether you implement the interface in the C# sense, but doing so helps ensuring you implement all required methods from the interface.
- We add code that adds the location of your dll to the registry. This code can just be copied to any new classes as you create them.
If you add a new file to the project you have to remember to do all these things or the UDF will likely not be found in Excel.
Converting between SuanShu and COM compatible objects
Like for the previous example we provide a class that converts between SuanShu’s Matrix and Vector objects and the objects that are passed by COM. The class can be found in the SuanShuAddin example project as SuanShuAddin.cs.
Using the UDFs
I will explain how to use our define UDFs by using matrix multiplication as an example.
To build the project and register the Add-In, Visual Studio must be run with Administrator privileges (in Windows Vista or later this is done by right clicking the shortcut and clicking ‘Run as Administrator’). After building the project, we need to load the UDF in Excel. To do so we first open the ‘Add-In Manager’. To do this in Excel 2010, open the options dialog from the file tab and in the Add-Ins section under ‘Manage’ select ‘Excel Add-Ins’ and press Go.
In the Add-Ins window, press ‘Automation’ and in the ‘Automation Servers’ window find the class you want to load, select it and press OK twice. It’s name will be the project name followed by the class name, e.g. SuanShuAddin.SuanShu.
If the add-in does not appear, it may be because Visual Studio Tools for Office is not activated. A simple way to check this is to open the SuanShuExcel (not the add-in) project and see if you can successfully build and run it.
After having done that, you can use the UDFs as follows:
- Select the range that you want the result to be written to (or the cell if the output is just a single number).
- In the formulas tab, click ‘Insert Function’.
- Under ‘Category’, select one of the Add-Ins you just loaded (each interface is a separate Add-In).
- Select the function you would like to insert and press OK.
- Select the input arguments.
- If the output is just a single number, click OK. If it is a vector or matrix, press CTRL+Shift+Enter.
[one_half]
[/one_half]
[one_half_last]
[/one_half_last]
Example Spreadsheet
We have provided an example spreadsheet (addin_example.xlsx) that you can use to check whether the add-in is working correctly. However you will still need to follow the instructions for building and loading the add-in for the formula in the spreadsheet to work.
