How to get ExcelDNA work with R.Net

Question

I am a C# and R beginner trying to run the example http://mockquant.blogspot.com/2011/07/yet-another-way-to-use-r-in-excel-for.html

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

I have updated the link in the line SetDLLdirectory and I tried both 32bit and 64 bit versions of R (my cpu system is win7/64 bit)

I tried with earlier stable versions of RDotNet and googled for updates to the example code, eg. here:

https://groups.google.com/d/msg/exceldna/7_wr8pwuCZ0/GLKlVFjr6l8J

    <DnaLibrary RuntimeVersion="v4.0" Name="My First XLL" Language="CS">
<ExternalLibrary Path="RDotNet.dll" />
<ExternalLibrary Path="RDotNet.NativeLibrary.dll" />
<Reference Name="RDotNet" />
<Reference Name="RDotNet.NativeLibrary" />
<![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.
            var oldPath = System.Environment.GetEnvironmentVariable("PATH");
            var rPath = @"C:\Program Files\R\R-3.0.1\bin\x64";
    var newPath = string.Format("{0}{1}{2}", rPath, System.IO.Path.PathSeparator, oldPath);

            System.Environment.SetEnvironmentVariable("PATH", newPath);
            rengine = REngine.CreateInstance("RDotNet");
        }            
        [ExcelFunction(Description = "get random numbers obey to normal distribution")]
        public static double [] MyRnorm(int number)
        {
            return (rengine.Evaluate("rnorm(" + number + ")").AsNumeric().ToArray<double>());
        }
    }
}

]]>
</DnaLibrary>

But I could not make it work...

After trying the older versions of r.net I also tried the newest version with the old code and then I tried to adaptthe example code present on R.Net website to the code above, presuming that initialisation of r engine now uses the path in the registry:

<DnaLibrary RuntimeVersion="v4.0" Name="R.NET" Description="R.NETExcel" Language="CS">
<Reference Path="RDotNet.NativeLibrary.dll"  />
<Reference Path="RDotNet.dll"  />
<Reference Path="DynamicInterop.dll"  />

<![CDATA[
using System;
using System.IO;
using System.Linq;
using RDotNet;
using DynamicInterop;

namespace CSLib
{
    public class CSLib
    {




        public static double[] MyRnorm(int number)
        {
        REngine.SetEnvironmentVariables(); 
        REngine engine = REngine.GetInstance(); 
            engine.Initialize();

        return (engine.Evaluate("rnorm(" + number + ")").AsNumeric().ToArray<double>());

        engine.Dispose();   
    }

    }
}
]]>

</DnaLibrary>

This is also giving no results. Excel function retrns #num error.

I am certain that ExcelDNA works when I comment out the section trying to connect to R and paste some other simple function like sum two values.

I believe that my problems may be related to new developments in RdotNet making the example code above obsolete (eg. it could be new way of initialising REngine instance). I am also wondering about the possibility of or 32 bit /64 bit conflict, that is why I also tried to make it work on 32 bit, win xp, dot.net 4.0 - with no results.

What then should be the right way of connecting ExcelDNA to the current R.NET version?

Thank you very much in advance for help.

Answer 1

These steps worked fine for me:

1. Ensure the R is installed. In my Windows "Add or Remove Programs" list I see "R for Windows 3.02.

2. Create a new "Class Library" project in Visual Studio.

3. In the NuGet package Manager Console, execute the commands:

   PM> Install-Package Excel-DNA
   PM> Install-Package R.NET.Community
   

4. Add the following code to the main .cs file:

   using System;
   using System.Linq;
   using ExcelDna.Integration;
   using ExcelDna.Logging;
   using RDotNet;
   
   namespace UsingRDotNet
   {
       public class AddIn : IExcelAddIn
       {
           public void AutoOpen()
           {
               MyFunctions.InitializeRDotNet();
           }
   
           public void AutoClose()
           {
           }
       }
   
       public static class MyFunctions
       {
           static REngine _engine;
           internal static void InitializeRDotNet()
           {
               try
               {
                   REngine.SetEnvironmentVariables();
                   _engine = REngine.GetInstance();
                   _engine.Initialize();
               }
               catch (Exception ex)
               {
                   LogDisplay.WriteLine("Error initializing RDotNet: " + ex.Message);
               }
           }
   
           public static double[] MyRnorm(int number)
           {
               return (_engine.Evaluate("rnorm(" + number + ")").AsNumeric().ToArray<double>());
           }
   
           public static object TestRDotNet()
           {
               // .NET Framework array to R vector.
               NumericVector group1 = _engine.CreateNumericVector(new double[] { 30.02, 29.99, 30.11, 29.97, 30.01, 29.99 });
               _engine.SetSymbol("group1", group1);
               // Direct parsing from R script.
               NumericVector group2 = _engine.Evaluate("group2 <- c(29.89, 29.93, 29.72, 29.98, 30.02, 29.98)").AsNumeric();
   
               // Test difference of mean and get the P-value.
               GenericVector testResult = _engine.Evaluate("t.test(group1, group2)").AsList();
               double p = testResult["p.value"].AsNumeric().First();
   
               return string.Format("Group1: [{0}], Group2: [{1}], P-value = {2:0.000}",  string.Join(", ", group1), string.Join(", ", group2), p);
           }
       }
   }
   

5. F5 to run the add-in in Excel.

6. Enter the formula =TestRDotNet()and=MyRNorm(5)`. Numbers appear in Excel.

I've added the "UsingRDotNet" project to the Excel-DNA Samples on GitHub.