R语言之xlsx包读写Excel数据的操作

感谢adrian a. drǎgulescu发布的xlsx包

xlsx包提供了必要的工具来与excel 2007进行交互。用户可以阅读和编写xlsx，并可以通过设置数据格式、字体、颜色和边框来控制电子表格的外观。设置打印区域，缩放控制，创建分割和冻结面板，添加页眉和页脚。包使用apache poi项目中的java库。本篇主要分享利用xlsx工具包在读写xlsx过程中所碰到的问题及解决办法。

工具准备

强烈建议大家使用rstudio这个ide，它是以今为止对r语言最友好的一个ide之一，而且使用很方便。特别是在新包下载安装的时候，只需请求要安装的包名，rstudio会自动将关联的其他包也一并下载并安装。

安装r、安装rstudio；

一个简单的示例数据（本次以iris鸢尾花数据为例）；

下载安装xlsx（rstudio会同步下载并安装rjava, xlsxjars两个包）；

> # 下载并安装xlsx包
> install.packages("xlsx")
> library(xlsx)

【基础】简单读取excel文件数据

假如是csv或txt等文本类的数据文件，利用r内置函数read.csv()与read.table()就可读取（注意编码格式的参数设置）。excel由于使用范围最广，很多问题不可避免，因此，xlsx包提供了专门读取xlsx的函数read.xlsx和read.xlsx2，为什么有两个呢？请看以下区别：

函数	参数
xlsx::read.xlsx()	file, sheetindex, sheetname=null, rowindex=null,startrow=null,endrow=null, colindex=null,as.data.frame=true, header=true, colclasses=na,keepformulas=false, encoding=“unknown”, password=null, …
xlsx::read.xlsx2()	file, sheetindex, sheetname=null, startrow=1,colindex=null, endrow=null, as.data.frame=true, header=true,colclasses=“character”, password=null, …

其实只是细微的差别，大家自己体会即可。下面给个参考案例：

> # 指定file和sheetindex（或sheetname），即可定位到相应的工作表
> data1 <- read.xlsx("iris.xlsx",sheetindex = 1)
> head(data1)

sepal.length	sepal.width	petal.length	petal.width	species
5.1	3.5	1.4	0.2	setosa
4.9	3.0	1.4	0.2	setosa
4.7	3.2	1.3	0.2	setosa
4.6	3.1	1.5	0.2	setosa
5.0	3.6	1.4	0.2	setosa
5.4	3.9	1.7	0.4	setosa

【基础】简单写入数据到excel文件

切莫用r内置函数read.csv()与read.table()去生成xlsx文件，会有你意想不到的麻烦，还是采用专业的包来解决问题吧。 xlsx包同样提供了两个写入数据的函数write.xlsx()和write.xlsx2()，其中细微区别自行参透（注意参数 ...）。

函数	参数
xlsx::write.xlsx()	x, file, sheetname=“sheet1”, col.names=true, row.names=true, append=false, showna=true, password=null
xlsx::write.xlsx2()	x, file, sheetname=“sheet1”,col.names=true, row.names=true, append=false, password=null, ...

下面是参考案例：

># 指定x待写入数据，file生成的文件名，row.names为false则不生成行名，指定sheet工作表名为sheet1
>write.xlsx(iris, file = "iris.xlsx", row.names = false, sheetname = "sheet1")

想必会有人在这里踩坑，大家应该注意到有一个append的参数，是否认为将其值设置为true的话，就可以多次向表中写入数据？那就真踩坑了。查看xlsx包中的注释也很模糊：

> # a logical value indicating if x should be appended to an existing file. 
> # 翻译：一个逻辑值，指示是否应该将x附加到现有文件中

附加到现有文件中，实际上是增加新的sheet，而非在原有sheet工作表中继续增加数据。如需在同一个sheet工作表中多次增加数据，请继续往下看。

【进阶】随心所欲读取excel中的各种信息

说随心所欲 一点不夸张，不仅可以取出excel中的数据，还能识别excel单元格的样式（包括颜色、字体、大小、标注、数据类型等等）。其原理与数据库有点相似，先是定义一个工作簿的对象，再基于工作簿定义里面的工作表，进而逐级查询。下面进行详细介绍：

【样例数据】文件名：iris10.xlsx。

声明一个工作簿对象

> # loadworkbook(file, password=null)  #用于声明一个工作簿对象
> # 提醒：如果excel文件不在工作空间内，file最好指定为绝对路径
> wb <- createworkbook("iris10.xlsx")

检索工作簿中的sheet

> # sheets <- getsheets(wb)  #用于生成一个list对象，其中包含所有工作表的信息,数据类型为rjava::jobjref，在此不深入讲解
> sheets <- getsheets(wb)

定位目标sheet

> # 本例只有一个sheet，名称为“sheet1”
> sheet <- sheets[["sheet1"]]  # sheet的数据类型为rjava::jobjref

读取数据【方法一】

上面read.xlsx()方法能够将整个sheet工作表的数据读取出来，在这里提供另一种方法，不过相对麻烦一点，使用的是xlsx::readcolumns()函数

函数	参数
xlsx::readcolumns()	sheet,startcolumn,endcolumn,startrow,endrow=null,as.data.frame=true,header=true, colclasses=na, …
xlsx::readrows()	sheet, startrow, endrow, startcolumn, endcolumn=null

xlsx::readrows()使用起来比较麻烦，不如xlsx::readcolumns()好用，有兴趣的可自行研究一下。另外还有两个函数，用于定位表内数据第一行和最后一行的索引（这里与java的性质一致，从0开始算起）

函数	参数
getfirstrownum()	无参。该函数必须基于sheet对象
getlastrownum()	无参。该函数必须基于sheet对象

xlsx::readrows()使用起来比较麻烦，不如xlsx::readcolumns()好用，有兴趣的可自行研究一下。另外还有两个函数，用于定位表内数据第一行和最后一行的索引（这里与java的性质一致，从0开始算起）

函数	参数
getfirstrownum()	无参。该函数必须基于sheet对象
getlastrownum()	无参。该函数必须基于sheet对象

下面以xlsx::readcolumns()为例获取数据：

> # 该函数必须提供数据的起始列索引值、终止列索引值、起始行索引值、终止行索引值；
> datatmp <- readcolumns(sheet, startcolumn = 1, endcolumn = 10,
            startrow = sheet$getfirstrownum()+1, endrow = sheet$getlastrownum()+1,
            header = t, as.data.frame=true)

as.data.frame=true决定了输出结果为一个数据框。

缺点：在不清楚数据结构的情况下，首行和末行索引值可以求得，但列数一般难以确定，可能导致列缺失或生成多余的列

读取数据【方法二】

另一种方法相对【方法一】要好一点，先是将所有单元格的值获取出来，再生成数据框。（稍微复杂一点）

函数	参数	注释
xlsx::getrows()	sheet, rowindex=null	用于获取sheet的每一行数据，返回值list，数据类型为rjava::jobjref
xlsx::getcells()	row, colindex=null, simplify=true	用于获取行内每个单元格的数据，返回值list，数据类型为rjava::jobjref
xlsx::getcellvalue()	cell, keepformulas=false, encoding=“unknown”	用于获取所有单元格的值，返回值list，数据类型为character，长度为数据表m*n

注意：这里连同标题行也作为单元格数据一并获取，并且如果有null值的单元格，会跳过该单元格

> # 获取cells进而获取values
> cells <- sheet %>% getrows() %>% getcells()
> values <- lapply(cells,getcellvalue)

values获取出来就如下面这个样子，你会发现value的名称向量，每个值都包含了所在单元格的x、y坐标值。

> names(values)  #查看values的名称向量
 [1] "1.1" "1.2" "1.3" "1.4" "1.5" "2.1" "2.2" "2.3" "2.4" "2.5" "3.1" "3.2" "3.3" "3.4" "3.5" "4.1" 
[17] "4.2" "4.3" "4.4" "4.5" "5.1" "5.2" "5.3" "5.4" "5.5" "6.1" "6.2" "6.3" "6.4" "6.5" "7.1" "7.2" 
[33] "7.3" "7.4" "7.5" "8.1" "8.2" "8.3" "8.4" "8.5" "9.1" "9.2" "9.3" "9.4" "9.5" "10.1" "10.2" "10.3"
[49] "10.4" "10.5" "11.1" "11.2" "11.3" "11.4" "11.5"

将这些坐标值拆分出来，作为等会重排数据的索引

> addresses <- sapply(names(values),fun = function(x) str_split(string = x,pattern = "[.]"))

接下来就只需要将其进行重排，形成数据框即可。

> datas.name <- vector(mode = "character")  #声明一个空的向量，用来存放标题
> datas <- data.frame()  # 声明一个空的数据框，用来存放目标数据
> # 用sapply代替for做循环，避免占用大量内存。同时注意sapply使用时的环境问题，用.globalenv指向最外层环境的变量。
> # 这里只对数据进行重排，无需进行计算，所以invisible不显示计算结果
> invisible(sapply(addresses,fun = function(x) {
+  if (x[1] == "1") {
+   .globalenv$datas.name = c(.globalenv$datas.name,.globalenv$values[[1]])
+   .globalenv$values[[1]] <- null
+  } else {
+   .globalenv$datas[x[1],x[2]] <- .globalenv$values[[1]]
+   .globalenv$values[[1]] <- null
+  }
+ }))
> names(datas) <- datas.name  #最后在添加标题
> view(datas)

得到结果与原excel数据一致

获取单元格样式与获取数据的方式一致，这里不再增加过多篇幅讲解，只做简单介绍。以下函数按函数名字面理解。

函数	参数
xlsx::cellstyle()	wb, dataformat=null, alignment=null,border=null, fill=null, font=null, cellprotection=null
xlsx::setcellstyle()	cell, cellstyle
xlsx::getcellstyle()	cell
xlsx::createcellcomment()	cell, string="", author=null, visible=true
getcellcomment()	cell
removecellcomment()	cell

其他函数后续如有机会，再做详细介绍吧。

【进阶】随心所欲将数据写入excel文件

我想大家更想看到的就是这部分内容了。确实在日常处理数据时，将数据存储到excel中进行传递是常有的事，谁叫excel是微软亲生的呢。闲话少说，直入正题。

前面基础篇通过write.xlsx()函数将数据写入excel文件中，同时指定sheet名称。但这种写入是一次性的，即一次写入多少就多少。在工作簿里面新增sheet工作表用append控制，但在同个sheet上继续写入数据，会报错：

> write.xlsx(datas,file = "iris10.xlsx",sheetname = "sheet1",row.names = f,append = t)
error in .jcall(wb, "lorg/apache/poi/ss/usermodel/sheet;", "createsheet", : 
 java.lang.illegalargumentexception: the workbook already contains a sheet of this name

说是这个名称的sheet已经存在同名的了！

这次我们采用高级一点的方法，跟前面进阶读取数据一样，先是定义一个工作簿的对象，再创建或加载sheet工作表。

函数	参数	注释
xlsx::createworkbook()	type=“xlsx”	用于生成一个新的excel工作簿
xlsx::loadworkbook()	file, password=null	用于加载当前已存在的excel工作簿
xlsx::saveworkbook()	wb, file, password=null	使用完必须保存工作簿
xlsx::createsheet()	wb, sheetname=“sheet1”	用于生成一个新的sheet工作表
xlsx::removesheet()	wb, sheetname=“sheet1”	用于删除工作表
xlsx::getsheets()	wb	用于获取当前工作簿里的工作表清单，返回值是list
xlsx::adddataframe()	x, sheet, col.names=true, row.names=true,startrow=1,	用于获取当前工作簿里的工作表清单，返回值是list
（续上）	startcolumn=1,colstyle=null, colnamesstyle=null,rownamesstyle=null, showna=false, characterna="", byrow=false

前面讲过如何加载已有工作簿，这里以生成新excel工作簿为例，将数据写入文件中

> wb <- xlsx::createworkbook()
> sheets <- getsheet()
# 新生成的工作簿没有sheet，系统提示：workbook has no sheets!
> sheet <- createsheet(wb,sheetname = "newsheet1")

此时r内存中已经生成了一个工作簿，包含一个空的sheet工作表，通过adddataframe()函数将数据写入sheet中.

> # 用上面生成的datas数据框对象，取前4行数据写入当前sheet对象中
> adddataframe(data[1:4,],sheet,row.names = f)
> saveworkbook(wb,file = "iris_new.xlsx")

==记得保存工作簿、记得保存工作簿、记得保存工作簿==

如果是在已有excel工作簿上操作，这里最好做一个判断，避免覆盖现有数据，造成不必要的麻烦。如果当前sheet的最后一行索引不等于零（说明有数据），则将新数据写到最后一行数据的下一行，同时不加入列名行(col.names = false)；如果为零则将数据直接添加到sheet中。

> # 用上面生成的datas数据框对象，取前4行数据写入当前sheet对象中
> if (sheet$getlastrownum() != 0) {
+     adddataframe(data[1:4,],sheet,row.names = f,col.names = f,startrow = sheet$getlastrownum() + 2)
+    } else {
+     adddataframe(data[1:4,],sheet,row.names = f)
+    }
+ }
> saveworkbook(wb,file = "iris_new.xlsx")

至此，你应该知道如何在原有工作表基础上新增数据行了吧？多么方便！！

如果要增加新的sheet工作表，只需将sheet重新定义一个新的sheetname即可。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。