Yet another way to use R in Excel for .NET programmer

July 6, 2011
By

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

I wrote the article whose title is “Another way to use R in Excel for .NET programmer” last night.
In that article, We need to use IDE to write C# program.

On ther other hand, Excel-DNA give us easier way to create XLL.
Let me show you one example.

In last post, I downloaded two libraries.

  1. Excel-DNA
  2. R.NET

If you don’t download these files yet, please do it.
And, put the files which I showed below together into some folder.
I changed the file name of ExcelDna.xll and ExcelDna.dna to SimpleExample.xll and SimpleExample.dna.
It is no problem for you to change these names as you like.

Next, Please copy and paste below codes to SimpleExample.dna by notepad or such a editor and save & close dna file.

<DnaLibrary RuntimeVersion="v4.0" Name="My First XLL" Language="CS">
<ExternalLibrary Path="R.NET.dll" />
<Reference Name="R.NET" />
<![CDATA[

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

]]>
</DnaLibrary>

If you use or install another version R, modify “SetDllDirectory” function call.

Lanch SimpleExample.xll and create new Excel sheet. As you’ll see below, you can use your own function defined in C# language !

Enjoy !

*R.NET seems to be compiled with .NET framework 4.0. you may have to install these to try this example.

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 on topics such as: Data science, Big Data, R jobs, 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.

Sponsors

Mango solutions



RStudio homepage



Zero Inflated Models and Generalized Linear Mixed Models with R

Quantide: statistical consulting and training



http://www.eoda.de









ODSC

CRC R books series











Contact us if you wish to help support R-bloggers, and place your banner here.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)