# Excel

### Revision for “Excel” created on April 15, 2015 @ 18:47:47

TitleContentExcerptRevision Note
 Excel 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:   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:
1. We define an interface containing definitions of our UDFs
2. Both the interface and the class need the ComVisible(true) attribute
3. 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.
4. 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

1. Select the range that you want the result to be written to (or the cell if the output is just a single number).
2. In the formulas tab, click ‘Insert Function’.
3. Under ‘Category’, select one of the Add-Ins you just loaded (each interface is a separate Add-In).
4. Select the function you would like to insert and press OK.
5. Select the input arguments.
6. If the output is just a single number, click OK. If it is a vector or matrix, press CTRL+Shift+Enter.