Thursday, 1 March 2012

Adding functionality to Excel using the NAG Library for .NET

Much of our work at NAG is devoted to creating new implementations of our numerical libraries and attempting to make their algorithms available from as many languages and packages as possible, so that our users have access to them from whichever environment they're working in. Thus, users of packages such as MATLAB® (and similar packages such Octave), LabVIEW and Maple, and programmers working in languages like Java, Python and Visual Basic (along with, of course, more traditional languages such as C and Fortran) have all been making use of NAG algorithms to enhance their applications and solve numerical problems for a long time.

Microsoft Excel® users can easily access NAG routines from both the NAG Fortran Library and the NAG C Library, because they are distributed as Dynamic Link Libraries (DLLs). For example, my colleague Marcin Krzysztofik has recently described how to solve a nonlinear least-squares problem in Excel using the nag_opt_nlin_lsq (e04unc) routine from the NAG C Library.


Recently, we had a query from an Excel user who wanted to invoke a method from the NAG Library for .NET. One way to do this is to use Visual Studio Tools for Office (VSTO) to create a customized Excel workbook that loads a .NET assembly when it is opened. Events in the workbook (e.g. typing values in cells or clicking buttons) then call assembly methods which can access workbook data and - for example - call NAG methods to process it.

My colleague Sorin Serban and I have illustrated this by producing a demo Excel workbook which fits a surface to a set of points in 3D space. More specifically, it uses the NAG method e01da to compute a bicubic spline through a set of data values on a rectangular grid, and then invokes e02df to calculate the values of the spline on the grid. The user can edit the data values in the workbook, and can also select a subset of the points to be used in the fit.


The demo is freely available for download as a deployed VSTO solution from this location, whilst more information about its working and installation - including how to obtain a trial licence for the NAG Library for .NET, which is required to run the demo - can be found in this README (a copy of which is also contained in the demo distribution). To obtain a copy of the full solution (including the assembly C# source code which invokes the NAG methods), please contact NAG support.

No comments:

Post a Comment

NAG moderates all replies and reserves the right to not publish posts that are deemed inappropriate.