Another way to use R in Excel for .NET programmer

July 5, 2011
By

(This article was first published on My Life as a Mock Quant in English, and kindly contributed to R-bloggers)

As you know, RExcel give us a way to combine R with Excel.
But, It just bothering to install some COMs and maybe not be programming but excel manipulation!

If you are a .NET programmer, there is another way to call R from Excel.
I would like to show you simple example.
We need to two libraries to do that.
  1. Excel-DNA
  2. R.NET
First, you download ExcelDNA from here.
And, go to "Distribution" folder.



you just need only three files(ExcelDna.dna, ExcelDna.xll, ExcelDna.Integration.dll) in this folder.
(I assume that your OS is 32bit windows.)
Second, you download R.NET from here.

you can set(or copy) these files any folder as you like.

Next, you start up your IDE. I used VC# this time.
Of-course, you can use other .NET languages like a VB.NET.

Create new project, choice "Class library" as template and wrote program as below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;
using RDotNet;

namespace CSLib
{
public class CSLib
{
static REngine rengine = null;
static CSLib()
{
// Set the folder in which R.dll locates.
REngine.SetDllDirectory(@"C:\Program Files\R\R-2.13.0\bin\i386");
rengine = REngine.CreateInstance("RDotNet", new[] { "-q" });
}
[ExcelFunction(Description = "get random numbers obey to normal distribution")]
public static double [] MyRnorm(int number)
{
return (rengine.EagerEvaluate("rnorm(" + number + ")").AsNumeric().ToArray());
}
}
}

In this case, I defined the function which generates random numbers obey standard normal  distribution. If you use or install another version R, modify "SetDllDirectory" function call.
All source code and solution files are here(github).

Next, Add R.NET.dll and ExcelDna.Integration.dll to your project as reference.
Now, Everything is ready. Let's compile !

After compile, you have to modify your ExcelDna.dna file.
Edit this file with notepad like below.
















(v4.0 means your version of .NET framework. modify this number if you need)
(If your DLL's relative-path is not "CSLib.dll" from ExcelDNA.xll, you have to correct this name)
(I deployed CSLib.dll, ExcelDna.xll, ExcelDna.dna in the same folder)

After that, double-click your ExcelDna.xll and create new Excel sheet.
As you'll see below, you can use your own function defined in C# language !



Enjoy !

To leave a comment for the author, please follow the link and comment on his blog: My Life as a Mock Quant in English.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.