Using C# and ExcelDNA to call .NET Libraries

In my last post, I demonstrated calling NMath from Excel using ExcelDNA and Visual Basic (VB) code. In this blog post, we will duplicate that functionality using C# instead of Visual Basic. In addition we will use the functionality of NMath to enabled the marshaling of data between Excel and NMath, and provide some additional code examples.

The outcome of these blog articles should illustrate that Excel can become a powerful tool to quickly access a comprehensive .NET library such as CenterSpace’s NMath without needing a large supporting programming environment. This is all made possible by Govert van Drimmelen’s freeware tool ExcelDNA. ExcelDNA can interpret VB, C#, and F# instructions stored in a text file as Excel is loaded eliminating the need for a separate compiler. ExcelDNA does require the text file to have the extension .DNA. I recommend using an editor like NotePad++ to edit this file. In our last blog post, we named our file NMathExcel.dna to hold our VB code. Below, I have provided C# code that performs the same functions as the VB code in our previous post. This code below provides examples on how to use several basic routines in NMath.










In reviewing the differences between the VB and C# versions, the first order of business was to add the language specification to “CS” for C# so that ExcelDNA knows to switch from the default language of VB. Aside from the obvious differences between the two languages, we have added a reference to our NMathShared assembly and an explicit reference to using ExcelDNA.Integration. In general, the C# code is slightly simpler than VB and enables very simple code to call the library. In our C# sample code function for creating a DoubleMatrix with a random generation, we used the library’s built in conversion function ToArray() to marshal the data into an array format for Excel. Using NMath’s built-in array handling capabilities we can simply copy data in and out in the proper formats. This illustrates the ease by which we can utilize the NMath math libraries and Excel.

To further demonstrate this capability, we can add the following code to our NMathExcel.dna text file that calls the matrix transpose function in NMath. We will show how to create array data in Excel, pass it to the library in the proper format, and then return a result for display.

   public static double[,] NDoubleMatrixTranspose(double[,] InputAr)
   {
      DoubleMatrix TempAr = new DoubleMatrix(InputAr);
     TempAr = TempAr.Transpose();
      return TempAr.ToArray();
   }

Make sure you close Excel and reopen it after saving your code changes to the .DNA file so that ExcelDNA has the opportunity to incorporate the new code.

We can now send our library a range of data cells to be acted on and display the result. In the following screen shot I show setting up a 5 by 5 range with values to be transposed.

We can now select an available empty cell to insert our transpose computation. The following screenshot shows selecting the input range for calling our NMath transpose function.

As in our previous blog, the returned result is stored in a single cell and only one value is displayed. As a quick review to display the full result, first paint (select) the area for the data to be displayed with the function call in the upper left hand corner, then press the F2 key, next press Ctrl-Shift (hold), followed by the Enter key – this will build the array formula to populate the selected area with the result. Your result should look like the following screen.

As a further example of what is possible, we can tackle an least squares example from the NMath documentation. The problem is to calculate the residual norm square using the Cholesky method, something that would be awkward to do natively in Excel alone. In this example the inputs are the number of rows and columns of a DoubleMatrix uniformly filled with random numbers. To accomplish this we need to add the following code to our DNA text file.

using CenterSpace.NMath.Matrix;
  .
  .
public static object NDoubleCholeskyLeastSqRNS(int rsize, int csize, int RandLBx, int RandUBx)
{
	RandGenUniform randomGenUniform = new RandGenUniform(RandLBx,RandUBx);
	randomGenUniform.Reset(0x124);
	DoubleMatrix TempAr = new DoubleMatrix(rsize,csize,randomGenUniform);
	DoubleCholeskyLeastSq cholLsq = new DoubleCholeskyLeastSq(TempAr);
	if (cholLsq.IsGood)
	{
 		DoubleVector b = new DoubleVector(TempAr.Rows,randomGenUniform);
		DoubleVector x = cholLsq.Solve(b);
		return cholLsq.ResidualNormSqr(b);
	}
	else
		return "The random matrix doesn't have full rank";
}

Summary

Approaching the complex computations with Excel by performing the necessary computation by calling a C# library with data passed in from Excel, creates a robust approach to solving problems. The following screenshot shows how providing our function with the input parameters from the example produces the desired result.

If we tried to implement the solution line by line in Excel, we would be loosing the power of an object language like C# and powerful third-party .NET libraries, and increasing the generation of tedious Excel code without a strong development environment.

To wrap up, combining Excel, ExcelDNA, and the CenterSpace’s NMath libraries can be used to quickly generate solutions to complex problems without an extensive programming environment. In my next blog, I plan to solve a curve fitting example with this approach and using more of Excel features to display the results.

Mike Magee

4 thoughts on “Using C# and ExcelDNA to call .NET Libraries

  1. Hi Mike,

    Thanks for the post, it is very interesting. Do you have any info on how to package this for client PCs? In particular, ExcelDNA is unable to pack nmath_native_x86.dll, and when I distribute the DLL seperately, an error occurs along the lines of “Unable to load nmath_native_x86.dll. The module was expected to contain an assembly manifest”

    Best
    Dafydd

  2. Hi Dafydd,

    Thank you for your interest in my example. The answer to your problem might be best answered by ExcelDNA, but if you allow me to guess as to what is happening I might be able to help. Your error indicates that pack is complaining that nmath_native-x86.dll is not an assembly, this is true. NMath does need this file to run in the x86 environment. I would assume you are trying to avoid installing CenterSpace on the client computer and just create a deliverable package. You might have to include the nmath_native dll files along with your generated XLL file and put them all in the same directory. One more step would be to include the path to this directory in your environment variables in system properties.

    At runtime NMath examines the system and determines whether it is running on a 32 or 64 bit processor. It then loads the appropriate native math module. I doubt if you will be able to pack it into one xll file, but if NMath.dll can find it then all should be well.

    Again thanks for your interest, and let me know if this helps.

    Mike

  3. Hi Mike

    Thanks for your quick reply. I did indeed solve it by including both nmath_native dll and the NMathKernel dll in the same directory as the XLL (this was for 32-bit), with the other nmath libs packed inside the XLL.

    Best regards
    Dafydd

Leave a Reply

Your email address will not be published. Required fields are marked *

Top