Excel DNA and R.NET

[Wu Kenneth]

I am trying to return a value to excel using UDF. When I added in R.NET, it will fail. Couldn't figure out why even after searches on the internet. When I comment out R.NET codes, it will work.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using ExcelDna.Integration;

using RDotNet;

namespace test

{

    public static class MyFunctions

    {

           

            static void SetupPath()

            {

                var oldPath = System.Environment.GetEnvironmentVariable("PATH");

                var rPath = @"C:\Users\gohm\Desktop\R";

                var newPath = string.Format("{0}{1}{2}", rPath, System.IO.Path.PathSeparator, oldPath);

                System.Environment.SetEnvironmentVariable("PATH", newPath);

            }

            [ExcelFunction(Description = "My first .NET function")]

            public static double TestArray(double x)

            {

                SetupPath();

                REngine engine = REngine.CreateInstance("RDotNet");

                engine.Initialize();

                //engine.Evaluate("source('C:/Users/gohm/Desktop/R/Cone.r')");

            

                //NumericVector va = engine.GetSymbol("va").AsNumeric();

                //NumericVector lowerRpt = engine.GetSymbol("lowerRpt").AsNumeric();

                //NumericVector higherRpt = engine.GetSymbol("higherRpt").AsNumeric();

                return x;

            }

    }

}

[Govert van Drimmelen]

Hi,

Could you try this example: http://www.r-bloggers.com/yet-another-way-to-use-r-in-excel-for-net-programmer/

There they use  REngine.SetDllDirectory(...) instead of updating the environment variable.

-Govert

[Wu Kenneth]

This is the warning i recieve.

'RDotNet.NativeLibrary.UnmanagedDll.SetDllDirectory(string)' is obsolete: '"Set environment variable 'PATH' instead.

I am able to integrate RDotNet and C# together using the set environment variable. Wanted to use ExcelDNA to publish in excel. But when I try to use all 3 together, it fails.

[Govert van Drimmelen]

Hi Kenneth,

I can imagine that SetDllDirectory is not the preferred way - it's not a particularly nice function, has side effects and other calls to the same function would overwrite the setting.

But:

* Does SetDllDirectory work when you do use it, despite the warning?

* Does your R work when you change the path in the system settings?

One difference between the Excel situation and running c C# app on its own is that the Excel process bitness would by default be 32-bit (for the 32-bit Excel) whereas a .NET assembly compiled to "Any CPU" will run as 64-bit on a 64-bit Windows.

You might look at the F# code here to see how they set it up: https://github.com/BlueMountainCapital/FSharpRProvider/blob/a63f1911b1761b007a0414e3dd37227612d7ce51/src/RProvider/RInit.fs

It looks like they add bin\i386 or bin\x64 to the R path before adding it to the PATH variable.

-Govert

[Wu Kenneth]

* Does SetDllDirectory work when you do use it, despite the warning?

This doesnt work.

Does your R work when you change the path in the system settings?

Yes, it works.

I modify the code from one of your web suggestion to make it compile with the current version of R.NET

<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>

[Govert van Drimmelen]

Ah - thanks for posting the update.

Glad you got it fixed up!

-Govert

[Wu Kenneth]

Just to update that only one R can be "initialise" and close once at any given time for one process. Hence there is a need to put a check whenever a calculation is called.

if(rengine==null)

{

     //initialize,set your path variable, create r instance etc

}

else { //do your calculation and return values}

This also means that if you create 2 seperate xll (lets call them "A" and "B" )and add them into Excel, after calling a function from "A", the functions from "B" will not work.

You can open another Excel to run "B", then "A" will not work. 

[Govert van Drimmelen]

Hi,

The multiple loading of R into a process is an issue. Since each Excel-DNA add-in always run in its own AppDomain, having the R.NET able to initialize and run in separate AppDomains would be good solution.

That has recently been suggested for the R.NET project:  https://rdotnet.codeplex.com/workitem/92

Please add your vote and comment to that issue.

-Govert

[svMacro]

in order to create a packed DISTRIBUTABLE xll add-in with RDotNet and Excel-DNA what additional parameters do i have to define?

here is an example that  packs perfectly fine and is usable on my local machine, however does not work when used on a colleague's machine. the simple HelloWorld UDF will work but any UDF utilizing RDotNet does not work.

i have added multiple Try/Catch but am unable to pin point the issue. i believe it has something to do with the initial REngine statement but it is not clear.  i have gone as far as replicating the HOME and working directory and R installation on the client machine however i am still getting value errors when attempting to use the UDFs

here is the code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ExcelDna.Integration;

using RDotNet;

using RDotNet.NativeLibrary;

using RDotNet.Utilities;

using System.Diagnostics;

using System.Reflection;

using ExcelDna.Logging;

namespace blah

{

    public class blahR

    {

        [ExcelFunction(Description = "Sortino Ratio")]

        public static object rSortino(double[] returns, double MAR)

        {

            REngine engine;

            try

            {

                engine = REngine.GetInstance();

                engine.Initialize();

                engine.Evaluate("require(PerformanceAnalytics)");

                LogDisplay.WriteLine("we found R correctly");

            }

            catch (Exception ex)

            {

                LogDisplay.WriteLine("oh no R is not initializing properly");

                return ex.Message.ToString();

            }

            NumericVector xtsReturns = engine.CreateNumericVector(returns);

            engine.SetSymbol("xtsReturns", xtsReturns);

            NumericVector rv = engine.Evaluate("SortinoRatio(xtsReturns," + MAR + ")").AsNumeric();

            return rv[0];

        }

        [ExcelFunction(Description = "string thing")]

        public static object rgetwd()

        {

            REngine engine;

            try

            {

                engine = REngine.GetInstance();

            }

            catch (Exception ex)

            {

                return ex.Message.ToString();

            }

            //CharacterVector wd = engine.Evaluate("getwd()").AsCharacter();

            CharacterVector wd = engine.Evaluate("R.home()").AsCharacter();

            return wd[0];

        }

    }

    public class AddIn : IExcelAddIn

    {

        public void AutoOpen()

        {

            MyFunctions.InitializeRDotNet();

        }

        public void AutoClose()

        {

        }

    }

    public static class MyFunctions

    {

        static REngine _engine;

        internal static void InitializeRDotNet()

        {

            LogDisplay.WriteLine("REngine = " + REngine.EngineName);

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

        {

            NumericVector group1;

            LogDisplay.WriteLine("we declared group1 successfully");

            try

            {

                 // .NET Framework array to R vector.

            group1 = _engine.CreateNumericVector(new double[] { 30.02, 29.99, 30.11, 29.97, 30.01, 29.99 });

                _engine.SetSymbol("group1", group1);

            }

            catch (Exception ex)

            {

                return ex.Message.ToString();

            }

            NumericVector group2;

            LogDisplay.WriteLine("we declared group2 successfully");

            try

            {

                // Direct parsing from R script.

            group2 = _engine.Evaluate("group2 <- c(29.89, 29.93, 29.72, 29.98, 30.02, 29.98)").AsNumeric();

            }

            catch (Exception ex)

            {

                return ex.Message.ToString();

            }

            GenericVector testResult;

            LogDisplay.WriteLine("we declared testResult successfully");

            try

            {

                // Test difference of mean and get the P-value.

                testResult = _engine.Evaluate("t.test(group1, group2)").AsList();

            }

            catch (Exception ex)

            {

                return ex.Message.ToString();

            }

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

        }

    

    public static object helloWorld()

    {

        LogDisplay.WriteLine("helloWorld");

        return "hello World";

    }

    

    

    }

}

i also attempted with Govert's example and it worked fine on my machine, but when using the packed add-in on a different machine only the HelloWorld UDF worked.

[Govert van Drimmelen]

Is the R distribution installed on the other machine, or did you just copy the files?

I presume there are some registry entries or something that allow R.NET to find the R files.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of svMacro [seanver...@gmail.com]
Sent: 13 April 2015 08:40 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: Excel DNA and R.NET

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[svMacro]

yes i made sure the exact same R installation was installed on the client machine, and replicated the Rpath, i even attempted to define the Rpath in the event that was the issue but that did not help.

To post to this group, send email to exc...@googlegroups.com.

[svMacro]

i determined that the client computer i was distributing the package to did not have the R location key on the registry due to firm security protocol. when tested on a different computer the add-in worked when the full solution folder was provided, however i still could not get the packed version to work. thanks for your help Govert, and thanks for Excel-DNA

sean

[Govert van Drimmelen]

Hi Sean,

 

I think RDotNet looks under the user key for the SOFTWARE\R-core entries if the key is not found under the machine hive. So you should be able to install or add the required keys even for users that don’t have admin rights. Alternatively you can set an R_HOME environment variable.

 

Regarding the packed version – you’d need to add the R-related references to the .dna file. So your .dna file would be something like:

 

 

<DnaLibrary Name="UsingRDotNet Add-In" RuntimeVersion="v4.0">

 

  <ExternalLibrary Path="UsingRDotNet.dll" LoadFromBytes="true" Pack="true" />

 

  <Reference Path="DynamicInterop.dll" Pack="true" />

  <Reference Path="RDotNet.dll" Pack="true" />

  <Reference Path="RDotNet.NativeLibrary.dll" Pack="true" />

 

</DnaLibrary>

 

 

Regards,

Govert

 

 

To post to this group, send email to exce...@googlegroups.com.