转载

R语言学习笔记之: 论如何正确把EXCEL文件喂给R处理

前言: 应用背景兼吐槽

继续延续之前每个月至少一次更新博客，归纳总结学习心得好习惯。

这次的主题是论R与excel的结合，又称论如何正确把EXCEL文件喂给R处理

分为：

1、 xlsx包安装及注意事项

2、用vba实现xlsx批量转化csv

以及，这个的对象，针对跟我一样那些从R开始接触编程的，一直以来都是用excel做数据分析的人……编程大牛请轻拍

之所以要研究这个，是因为最近工作上接了个活，要把原来在excel端的报表迁移到R端，自动输出可视化图形，并制作PDF或PPT。

这个活可以分为四个阶段：

1）源数据整理与搭建 & 需求分析

2）依据需求，R语言数据处理及输出处理后数据+图表

3）用markdown或者其他手段自动把图表复制到报告里

4）报告使用人自己编辑整合数据。

全程要求除了数据准备不是自动化，其他都要是自动化，能省就省。。而R本身与xlsx的融合并不好。

而R读取xlsx数据，就是我遇到的第一道槛。

这个活的数据都是人工从公司网页端数据库下载后储存在xlsx里的（用sql直连数据库的权限很难开）。尝试过直接从数据库端下载csv格式，但是一来格式时有错漏，二来直接下csv格式文件大小过大（单个文件从几兆变成几十兆），所以最终还是决定以xlsx格式储存，再另作打算。

相信对于那些从excel迁移到R工作的人，也会遇到同样的问题：

一、xlsx包

首先尝试用R包解决。即xlsx包。

xlsx包在加载时容易遇到问题。基本都是由于java环境未配置好，或者环境变量引用失败。因此要首先配置java环境，加载rJava包。

百度了一下，网上已有很多解决方案。我主要是参考这个帖子，操作步骤为：

1、安装最新版本的java。如果你用的R是64位的，请下载64位java。

下载地址： http://www.java.com/en/download/manual.jsp

要安装在 C:/Program Files/Java 下面，win8的尤其小心不要安装为C:/Program Files(x86)。可能是R在读取路径时，对x86这样的文件夹不大好识别吧，我第一次装在x86里，读取是失败的。

2、在R中加载环境，即一行代码，路径要依据你的java版本做出更改。

Sys.setenv(JAVA_HOME='C://Program Files//Java//jre1.8.0_45//')

之后再加载rjava包或者xlsx包就成功了。

xlsx包加载成功后，用read.xlsx就可以直接读取xlsx文件，还可以指定读取的行和段，以及第几个表，以及可以保存为xlsx文件，这个包还是很强大的。

但是这个方法存在两个问题：

1、不是所有的公司电脑都能自由的配置java环境。很多人的权限是受限的。而且有些公司内部应用是在java环境下配置的。就算你找了IT去安装java，但是一些内部应用可能会因为版本号兼容问题而出错，得小失大。

2、用xlsx包读取数据，在数据量比较小的时候速度还是比较快的。但是如果xlsx本身比较大，包含数据多，read.xlsx效率会很低，不如 data.table 包的 fread 读取快捷以及省内存。但fread函数不支持xlsx的读入。。。

（参见这篇帖子，里面对千万行数据， fread 也只用了10秒左右，比常规的 read.table 或者 read.csv 至少省时一倍）

综上，由于java环境的复杂性与兼容度，还有xlsx包本身读取速度的限制，用xlsx包读取xlsx包的方法，更适合于：

1、个人电脑，自己想怎么玩都无所谓，或者高大上的linux, mac环境

2、数据量不会特别大，而且excel文件很干净，需要细节的操作

不凑巧的是，这个方法不适合我，于是只好另找办法了。

二、用VBA把xlsx批量转化为csv格式

在上面的尝试已经发现，xlsx本身就是这个复杂问题的最根本原因。与之相反，R对csv等文本格式支持的很好，而且有fread这个神器，要处理一定量级的数据，还是得把xlsx转化为csv格式。

以此为思路，在参考了两个资料后，我成功改写了一段VBA，可以选中需要的xlsx，然后在其目录下新建csv文件夹，把xlsx批量转化为csv格式。

代码如下：

 1 Sub getCSV()  2 '这是网上看到的xlsx批量转化，而改写的一个xlsx批量转化csv格式  3 '1)批量转化csv参考：http://club.excelhome.net/thread-1036776-2-1.html  4 '2)创建文件夹参考：http://jingyan.baidu.com/article/f54ae2fcdc79bc1e92b8491f.html  5 '这里设置屏幕不动，警告忽略  6 Application.DisplayAlerts = False  7 Application.ScreenUpdating = False  8 Dim data As Workbook  9 '这里用GetOpenFilename弹出一个多选窗口，选中我们要转化成csv的xlsx文件， 10 file = Application.GetOpenFilename(MultiSelect:=True) 11 '用LBound和UBound 12 For i = LBound(file) To UBound(file) 13     Workbooks.Open Filename:=file(i) 14     Set data = ActiveWorkbook 15     Path = data.Path 16     '这里设置要保存在目录下面的csv文件夹里,之后可以自己调 17     '参考了里面的第一种方法 18     On Error Resume Next 19     VBA.MkDir (Path & "/csv") 20     With data 21         .SaveAs Path & "/csv/" & Replace(data.Name, ".xlsx", ".csv"), xlCSV 22         .Close True 23       End With 24 Next i 25 '弹出对话框表示转化已完成，这时去相应地方的csv里查看即可 26 MsgBox "已转换了" & (i-1) & "个文档" 27 Application.ScreenUpdating = True 28 Application.DisplayAlerts = True 29 End Sub