如何获得ExcelDNA工作，R.Net [英] How to get ExcelDNA work with R.Net
问题描述
我是一个C＃和R初学者尝试运行的例子的http://mockquant.blogspot.com/2011/07/yet-another-way-to-use-r-in-excel-for.html
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>());
}
}
}
我已经更新了行SetDllDirectory会的链接，我尝试32位和64位版本的R（我的CPU系统是WIN7 / 64位）
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)
我试着用RDotNet早期稳定的版本，并用Google搜索更新到例如code，例如。这里：
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...
在试图将旧版本r.net我也尝试过的最新版本与旧code，然后我试图adaptthe例如code present上R.Net网站上的code以上，presuming R发动机的是初始化现在使用的路径在注册表中：
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>
这还没有给出结果。 Excel函数retrns＃NUM错误。
This is also giving no results. Excel function retrns #num error.
我敢肯定，ExcelDNA的作品时，我注释掉部分试图连接到R和粘贴其他一些简单的功能如求和两个值。
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.
我相信我的问题，可能与新发展RdotNet使得例如code以上过时（初始化REngine实例，例如，它可能是新的方式）。我也想知道的可能或32位/ 64位的冲突，这就是为什么我还试图使它在32位工作，WIN XP，dot.net 4.0 - 没有结果。
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.
那么，什么应该是连接ExcelDNA到当前R.NET版本的正确方法？
What then should be the right way of connecting ExcelDNA to the current R.NET version?
非常感谢您提前帮助。
推荐答案
这些步骤对我来说工作得很好：
These steps worked fine for me:
-
确保安装了R上。在我的Windows的添加或删除程序列表中我看到R为Windows 3.02。
Ensure the R is installed. In my Windows "Add or Remove Programs" list I see "R for Windows 3.02.
在Visual Studio中的一个新的类库项目。
Create a new "Class Library" project in Visual Studio.
在的NuGet包管理器控制台，执行命令：
In the NuGet package Manager Console, execute the commands:
PM> Install-Package Excel-DNA
PM> Install-Package R.NET.Community
添加以下code主.cs文件：
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);
}
}
}
F5运行加载项在Excel中。
F5 to run the add-in in Excel.
输入公式= TestRDotNet（）和 = MyRNorm（5）`。数字显示在Excel中。
Enter the formula =TestRDotNet()and=MyRNorm(5)`. Numbers appear in Excel.
我添加了UsingRDotNet项目在GitHub上的 Excel的DNA样本。
I've added the "UsingRDotNet" project to the Excel-DNA Samples on GitHub.
这篇关于如何获得ExcelDNA工作，R.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
