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.
newproj
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]setup1[/one_half]

[one_half_last]button[/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.
name

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.

defaultcode

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.

release

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

addin

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

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.

automation

After having done that, you can use the UDFs as follows:

  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.

[one_half]udf1[/one_half]

[one_half_last]udf2[/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.

SuanShu in .NET environment

We provide a version of SuanShu that can be used in Microsoft .NET environment. The following explains how to set up Visual Studio to use SuanShu.NET.

Please note that SuanShu is primarily a Java library, of which the .NET version is a conversion. Hence there are some benefits gained by using the Java version, which will be described at the bottom of this page.

Obtaining the Distribution

You can download the most recent version of the distribution from SuanShu’s product website.

Included in the distribution are:

  • The converted suanshu.dll
  • A converted version of the JodaTime jar
  • The parts of IKVM required to use the converted assemblies.

Importing the Assemblies

In order to use SuanShu.NET in your application, you will need to add it and its dependencies as references in Visual Studio. All of the files that you need to add are included in the distribution.

Visual Studio 2010

These directions are for Visual Studio 2010, although the procedure is very similar for other recent versions.

To add the references, right-click your project in the ‘Solution Explorer’ and click ‘Add Reference’.

[one_half]addreference[/one_half]

[one_half_last]dialog[/one_half_last]

Alternatively you can also select ‘Add Reference’ from the ‘Project’ menu. This should open the ‘Add Reference’ dialog. In the dialog, please select the ‘Browse’ tab and navigate to the directory in which you downloaded the SuanShu.NET distribution. Select all files and click ‘OK’. You should then see them being listed as References in the ‘Solution Explorer’.

references

Placing the license file

For SuanShu to run, it has to have access to the license file. In Java it will look for the license file on the classpath of your current project. In .NET it will only check the directory of your executable.

When running your project straight from Visual Studio, this would be the bin\Debug and bin\Release folders inside your project folder. For projects using Office integration features, the license file has to be in your ‘My Documents’ folder. To manually change the license file location, see the tips and tricks section below.

Examples

Using SuanShu with .NET is largely very similar to using it in Java. In the following I will give brief demonstrations on how to do some basic operations in C#.

Simple Example

In the blank project there is a simple example that shows how to call SuanShu code from C# to do matrix multiplication.

After importing the necessary classes (this can be done automatically by Visual Studio):

using com.numericalmethod.suanshu.matrix.doubles;
using com.numericalmethod.suanshu.matrix.doubles.matrixtype.dense;
using com.numericalmethod.suanshu.license;

we can do matrix multiplication with the following code:

Matrix A = new DenseMatrix(new double[][] {
new double[] { 1.0, 2.0, 3.0 },
new double[] { 3.0, 5.0, 6.0 },
new double[] { 7.0, 8.0, 9.0 }
});

Matrix B = new DenseMatrix(new double[][] {
new double[] { 2.0, 0.0, 0.0 },
new double[] { 0.0, 2.0, 0.0 },
new double[] { 0.0, 0.0, 2.0 }
});

Matrix AB = A.multiply(B);
Console.Write(AB.ToString());

Further tips on how to use SuanShu with .NET can be found at the bottom of this page.

Code Examples

http://redmine.numericalmethod.com/projects/public/repository/show/SuanShu.NET

Documentation

Included in the distribution in an XML file containing XML comments, which can be viewed in Visual Studio. However since it is a conversion from Java’s JavaDoc comments, it is possible that documentation for some parts are missing or incomplete. Furthermore documentation for classes from the Java library is not included.

In either of those cases, please check the latest JavaDoc.

Tips and Tricks

Setting the location of the license file

You can manually set the location of the license file before calling any SuanShu code. This can be done by calling:

com.numericalmethod.suanshu.license.License.setLicenseFile()

Datatypes

Since the library uses converted Java datatypes instead of their .NET equivalents. Hence for the SuanShu code to interact with other .NET code you may need to convert the datatypes. For example for lists, this can be accomplished as follows (in C#):

List cSharpList = new List();
java.util.List javaList = java.util.Arrays.asList(cSharpList.ToArray());
List

Unfortunately due to Java’s type erasure at compile time, the converted Java collections are not generic.

Jagged arrays

In .NET we have multi-dimensional arrays ([,] in C#) and jagged arrays ([][], e.g. arrays of arrays). Since Java only supports the latter, functions for which a multi-dimensional array would have been appropriate, will use jagged arrays. To see how a jagged array is defined, see the matrix example above.

Benefits of using the Java version

Even though the .NET version of SuanShu is fully featured, there are a few benefits of using the Java version:

  • Performance is about 2x better
  • The JavaDoc documentation is more complete than the XML comments
  • There is no need to convert between .NET and Java datatypes
  • Types are generic
  • The naming conventions of SuanShu are consistent with Java conventions

 

DSP

This is a collection of the algorithms for digital signal processing.

  • Fast Fourier Transform (FFT)

Statistics

  • descriptive statistics (all support incremental computation):
    • mean
    • variance
    • covariance
    • skewness
    • kurtosis
    • higher moments
  • ranking statistics:
    • min
    • max
    • rank
    • quantile
  • empirical distribution
  • distributions:
    • Beta
    • Chi-square
    • Durbin-Watson
    • Exponential
    • F
    • Gamma
    • Kolmogorov
    • Normal
    • Rayleigh
    • T
    • Weibull

SDE

  • Stochastic Differentiation Equations (SDE)
    • modeling
    • simulation/random walk
  • SDE integration
    • Euler scheme
    • Milstein scheme
  • Brownian motion
  • Bessel process

Optimization

  • univariate optimization:
    • Brent
    • Fibonacci
    • Golden-ratio
  • unconstrained optimization:
    • conjugate gradient
    • Fletcher-Reeves
    • Powell
    • Zangwill
    • Quasi-Newton
    • BFGS
    • DFP
    • steepest-descent
    • Netwon-Raphson
    • Gauss-Newton
  • constrained optimization:
    • penalty method
  • Nelder-Mead optimization
  • least P-th minmax optimization

Misc.

  • complex number
  • scientific notatoin
  • arbitrary precision support
  • interval arithmetics
  • a Math table data structure to store table values
  • Ordinary Least Square solver
  • sequence
    • Fibonacci sequence
  • uniroot finding algorithm:
    • Brent
    • Halley
    • Newton
  • common physical constants

Linear Algebra

  • vector
  • vector space
  • various matrix representations:
    • bi-diagonal
    • diagonal
    • tri-diagonal
    • Givens
    • Hilbert
    • lower/upper triangular
    • permutation
  • sparse vector representations:
  • sparse matrix representations:
    • CSR
    • DOK
    • LIL
  • iterative sparse matrix solver:
    • stationary
      • Jacobi
      • Gauss-Seidel
      • SOR
      • SSOR
    • non-stationary
      • Steepest Descent
      • BiCG
      • BiCGStabl
      • CGNE
      • CGNR
      • CG
      • CGS
      • GCR
      • GMRES
      • MinRes
      • QMR
    • pre-conditioner support
      • Jacobi
      • SSOR
      • customized
  • matrix elementary operations
  • Householder transformation
  • matrix inverse
  • matrix measures:
    • determinant
    • rank
    • trace
    • max
    • min
  • power of matrix
  • matrix pseudoinverse
  • matrix bi-diagonalization
  • matrix tri-diagonalization
  • Cholesky decomposition
  • Doolittle factorization
  • Eigen factorization
  • Gauss-Jordan elimination
  • SVD factorization (for asymmetric matrix)
  • Gram-Schmidt factorization
  • Hessenberg factorization
  • LDL decomposition
  • LU decomposition
  • QR decomposition

Curve Fitting & Interpolation

  • curve fitting
    • least-squares
  • online interpolation
    • the Neville method
  • univariate
    • linear
    • Newton polynomial
    • (natural) cubic spline
    • cubic Hermite spline
  • bivariate grid
    • bilinear
    • bicubic
    • bicubic spline
  • multivariate grid
    • multi-linear
    • multi-cubic spline
    • general recursive grid interpolation