Another way to use R in Excel for .NET programmer

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());
        }
    }
}

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 !