Site icon R-bloggers

Another way to use R in Excel for .NET programmer

[This article was first published on My Life as a Mock Quant in English, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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 their blog: My Life as a Mock Quant in English.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.