教大家一个Excel与R通信的策略(不用Rexcel)

相似文件 换一批

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

Rexcel是通过COM，而我介绍的方案是基于http协议，原理很简单，就是在R里设置一个http服务器，用VBA构建R代码或格式化数据，从Excel向R发送数据、代码，计算完毕返回结果，以VBA处理返回结果。Rexcel难安装，且也需要学习一些使用技术，对于实际应用而言，很多情况下未必有这个基于http协议的方法易用。强调一点，这里介绍的，是一个策略或方案，不是一个单一的办法，实现这个方案的技术不仅下述一种。

这个方案的难点在于R语言一端的server，见以下脚本，别看这个脚本没几行，为了攒起这个玩意，还真耗了不少搜索。

library(Rook)
library(R.utils)
setRefClass(
'FooBar',
methods = list(
    call = function(env) {
    result <- captureOutput(source(textConnection(rawToChar(env[['rook.input']]$read())))$value)
         list(
             status = 200L,
             body = paste(result,collapse="\n")
              )
                      }
                 )
             )
s <- Rhttpd$new()
s$start(listen="127.0.0.1", port="11269")
s$add(name="response",app=getRefClass('FooBar')$new())

几点说明：
1. 以上脚本可以实现的功能是：运行客户端发的R代码，捕捉输出，以文本格式返回客户端。这样就实现了以Excel为前端，以R为后端的效果。不过，不能返回图像。
2. 为什么选基于http的通信方式？Excel一端用WinHttpRequest发请求很容易，且可以与一个打开的R session反复通信；Clipboard我也试过，也很容易实现，但不知为什么容易出莫名其妙的故障；管道也试过，也不难，但在Windows下，很难实现一直守着一个打开的R session。
3. 本文介绍的以R为后端的通信方案仅可用作本地工具，不适合做网络应用开发，要想做开发，就必须用Shiny之类的了。
4. 上文中的脚本要求Rook包。R里能用来做server的工具不多，Rook是比较简易高效的一个，Linux和Windows下都没问题，还有个更小更快的httpuv，Linux下很好用，Windows下似乎总会导致R崩溃。
5. 向Rook服务器发送请求的长度上限为64kb，这个长度能做的事情还是不少的。
6. 上述代码中最为来之不易的是最长的那句，尤其是“env[['rook.input']]$read()”这部分，其功能是获得http请求中的原始input，Rook的官方说明里并没有给出说明，我是从其他包的源码中找到的这种方法。
7. 客户端以以下 VBA代码为例：

Private Sub CommandButton1_Click()
Dim REQ As New WinHttpRequest '引用Microsoft WinHTTP Services

REQ.Open "POST", "http://127.0.0.1:11269/custom/response/" '注意url后半段中的路径

REQ.Send ("citation()")
Debug.Print REQ.ResponseText
End Sub
8. 基于这个方案，可以利用R编程的优势，把一些复杂的计算过程或者Excel中没有的功能做成宏，提高效率。

上面强调过了，以http通信连接Excel与R只是一个策略而已，具体实现方法很多，再如用Python或Node.js等做一个数据中转用的服务器，在Excel做格式化数据的宏，实现在Excel中选取数据，将数据以文本方式发至中转服务器，R端从服务器读入数据，省去建立数据文件的麻烦。

抛砖引玉而已，有什么其他问题欢迎跟帖。。。

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏16 回帖

关键词：RExcel EXCEL xcel exce cel Excel 通信

前排学习

谢谢分享

如果能有截图操作，就更好了

一个recipe (Friedman检验)，因为是带宏的，就不使用附件了。


1. 在R中运行如下服务器代码：

1. library(Rook)
   
2. library(R.utils)
   
3. setRefClass(
   
4. 'FooBar',
   
5. methods = list(
   
6.     call = function(env) {
   
7.     result <- captureOutput(source(textConnection(rawToChar(env[['rook.input']]$read())))$value)
   
8.          list(
   
9.              status = 200L,
   
10.              body = paste(result,collapse="\n")
    
11.               )
    
12.                       }
    
13.                  )
    
14.              )
    
15. s <- Rhttpd$new()
    
16. s$start(listen="127.0.0.1", port="11269")
    
17. s$add(name="response",app=getRefClass('FooBar')$new())

复制代码

2. 在Excel工程中插入一个模块，置入如下代码：

1. Sub MacroFriedman()
   
2. Dim DRANGE, REQ, RCODE, RET, VEC, NC, NR
   
3. Set DRANGE = Application.InputBox(prompt:="Pick Data", Title:="Friedman Test", Type:=8)
   
4. NC = DRANGE.Columns.Count: NR = DRANGE.Rows.Count
   
5. For Each clm In DRANGE.Columns
   
6. For Each cel In clm.Cells
   
7. VEC = VEC & cel.Value & " "
   
8. Next
   
9. Next
   
10. VEC = Join(Split(Trim(VEC), " "), ",")
    
11. RCODE = "m<-c(" & VEC & "); " & "dim(m)<-c(" & NR & "," & NC & ")"
    
12. RCODE = RCODE & "; rt<-friedman.test(m); rt"
    
13. 
    
14. Set REQ = CreateObject("WinHttp.WinHttpRequest.5.1")
    
15. REQ.Open "POST", "http://127.0.0.1:11269/custom/response/", True
    
16. REQ.Send RCODE
    
17. REQ.WaitForResponse
    
18. MsgBox REQ.ResponseText
    
19. End Sub

复制代码

3. 在工作表里放几列数据（3列以上），运行宏"MacroFriedman"，会出现选框，选中数据确定，计算后结果返回Excel，出现于弹出的Msgbox。

此帖应该精华

zipf 发表于 2015-10-29 01:06
Rexcel是通过COM，而我介绍的方案是基于http协议，原理很简单，就是在R里设置一个http服务器，用VBA构建R代 ...

谢谢你

您好，我按照阁下的方法在R里输入代码后出现如下错误，不知为何，尚请赐教。多谢。
> s$start(listen='127.0.0.1',port='11269',quiet = F)
Error in FUN(X[[i]], ...) :
  cannot coerce type 'closure' to vector of type 'character'

lonestone 发表于 2017-2-26 10:09
谢谢你

您好，我按照楼主的方法在R里输入代码后出现如下错误，不知为何，尚请赐教。多谢。
> s$start(listen='127.0.0.1',port='11269',quiet = F)
Error in FUN(X[], ...) :
  cannot coerce type 'closure' to vector of type 'character'

知道了，我的代码是放在Rstudio里运行的，不知为何会报错，放在gui里正常运行。多谢了。