R实践一：用R语言处理Excel数据

2013-07-14 08:00阅读：

http://blog.sina.cn/dpool/blog/u/1855730654

（写于2013.7.12，阿九原创，转载请注明出处 http://blog.sina.com.cn/xaqjtu/）
和LP说起我在琢磨R，这东西有很强的数据处理能力。LP在她公司的财务部门工作。我告诉她如果有什么数据处理分析类的工作可以让我给她写程序做，原来利用Excel手工做的一些事情，现在或许可以较方便地用R来处理。

LP说好啊，她正在做固定资产清理，有一个包含几万条记录的Excel表，现在需要分发到各个下级单位分别补充信息、盖章确认、反馈汇总。手工拆成上百个文件比较费劲，而让各个单位筛选填写自己的数据又担心有操作错误出现遗漏。如果程序可以实现自动拆分的功能就好了。

我想起R的数据输出输入功能有xlsx等包，而数据筛选又是很简单的事情，于是马上说可以做。我盘算着数据文件应该是一个标题行和几万个数据行组成，这样我只要读入xlsx文件，然后根据单位字段筛选出子集，再输出到不同的文件中，这样就大功搞成了。

一、实际需求
打开她的工作xlsx文件，发现情况和所想的有区别。
1.文件上方有一个旋转透视表，用于汇总数据结果。这个表在每个分拆开的文件中都要有。2.子集不是从第一行开始，而是从旋转透视表的下方指定某行开始。数据要含表头。3.拆分时要按照一级单位建立目录（十几个），并在一级单位的目录中按照二级单位生成文件（上百个）。这样就可以按照一级单位的目录

下发，再由一级单位下发二级单位组织填写。
大致文件的头部截图如下：

R实践一：用R语言处理Excel数据

按照第5列（E列）建立目录，按照第6列（F列）在对应目录下建立文件。

这比预想的复杂一点点，不过现实需求应该是复杂的。我说我试试看能够实现多少。

二、寻找合适的函数、测试、实现的过程
1.安装加载xlsx程序包。

2.读入数据
使用read.xlsx()函数读入数据。发现几个问题：读入速度较慢、读到最后java报崩溃，另外，如果只读入少量数据进行测试时，发现读入的汉字为乱码，用write.xlsx()生成xlsx文件时，还是乱码。同事建议可以调高java的heap数值，但是我想这点数据就要调整heap值，那更多的数据岂非不用玩了？
既然只有一个sheet，我于是将xlsx文件转存为csv文件，作为文本形式，用read.table()函数读入。读取时遇到报错，读入失败。经过定位，确定某行数据的字段里有个单引号导致加载失败。于是用文本替换，将所有的单引号剔掉，这样就正确加载了。发生替换的地方不多，一共三百多处。但是我想这无论如何是修改了数据，这或许对数据会产生实际的影响呢。于是我搜了一下帮助，找到了read.csv()函数，用它快速完整加载所有的记录，没有报错。

3.建立目录，生成文件名
根据一级单位字段生成目录，先使用factor()函数将相关字段因子化。然后根据这个因子的水平（level()）进行for()循环，使用dir.create()函数来建立目录
再根据一级单位选择数据的子集（subset()），将子集的二级单位字段因子化进行for()循环，来再次筛选子集，并生成文件。在这个过程中发现相关字段有不能用来建立windows文件名的字符，如“#/\”等等。与LP确认是错乱的单位名，并修正了数据。然后用write.xlsx()函数测试了一下，循环正常，生成的文件记录数总和与原文件相同，这样就确认了整体架构是可靠的。

4.生成文件
现在进入最难度的部分：如何按照LP的要求生成每个xlsx。write.xlsx()能否实现呢？仔细看了帮助文档，没找到办法。有函数能实现对单元格字体、颜色等等的控制，但是没有能整块输出旋转透视表的功能。由于需要的旋转透视表并不复杂，所以也考虑用Excel公式来实现相同的功能，这样我也能通过拼接数据框来变通，但没有找到相关的函数，输出‘=A1 A2’之类的格式，如果这样做，最终的结果都变成了字符串而不是公式。read.xlsx()倒是有读入公式的选项，但是write.xlsx没有输出公式的选项。

忽然我发现有一个addDataFrame()函数，能够在一个Excel对象的某个sheet上的某个格子为左上角来输出数据框。我想能不能先做一个只包含旋转透视表（或者公式）以及表头的xlsx文件模板，然后通过将数据框追加到sheet上来实现呢？经过测试，这是可行的。唯一的缺点是打开后，透视表或公式的数据并不是根据下面的数据自动计算的，而是原来的样子，需要刷新数据才能变化。这点对我的开发目的不是问题，因为表格就是要各个单位去填写，填写完了他们本来就需要手工操作透视表的刷新。那一切就迎刃而解了。

三、最终代码
library('xlsx')
rm(list=ls())
mydata <- read.csv('data.csv',head=FALSE,stringsAsFactors=FALSE,sep=',',na.strings=' ',colClasses=c('character'))
nrow(mydata)

status <- factor(mydata$V5)
dir.create('result')
setwd( 'result' )

for ( i in levels(status) )

{ dir.create(i) }

status <- factor(mydata$V6)

for ( i in levels(status) )
{ subdata <- subset(mydata, V6==i)
print (nrow(subdata) )
dirnm <- subdata$V5[1]
wb <- loadWorkbook('model.xls')
sheets <- getSheets(wb)
sheet <- sheets[[1]]
addDataFrame(subdata,sheet,col.names=FALSE, row.names=FALSE,startRow=16,startCol=1)
levels(dirnm)
setwd(dirnm)
saveWorkbook(wb, paste(i,'.xls',sep=''))
setwd('..')
}

总结：
（个人通过解决问题所产生的总结，一孔之见。有些我认为“不能”的事情，未进行特别的深究；有些问题的解决方案，可能有更好更优雅的解决办法。请大家指正）
1.读入较多数据时，xlsx文件可能有内存问题，另外还碰到汉字问题。这时转存为csv文件是较好的解决办法。如果使用read.table()读入，在碰到一些特殊字符时可能会有问题，用read.csv()读入就没有这些问题了。但是如果有很多文件需要处理、自动处理，那还是需要解决xlsx与csv的转化问题。
2.未找到如何输出Excel公式或者透视表（）。
3.生成的新Excel里面的旋转透视表和公式，还是模板里面的值，需要人工刷新一次才会变化。后来我加了一个按钮控件，把刷新的宏存了进去。本意是让操作人员点一下来刷新数值，但是结果发现首次打开就会自动刷新了。这个原理我没有深究。
4.R毕竟不是专门用来操控Excel的，不能完全实现Excel的功能，这可以理解，如果要实现这样复杂的操控，那还不如干脆用VBA了。

举报/Report

我的更多文章

下载客户端阅读体验更佳