R语言 xlsx 读写Excel数据

【基础】简单读取excel文件数据
【基础】简单写入数据到excel文件
【进阶】随心所欲读取excel中的各种信息
【进阶】随心所欲将数据写入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)
1
2
3
【基础】简单读取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

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

a logical value indicating if x should be appended to an existing file.

翻译：一个逻辑值，指示是否应该将x附加到现有文件中。

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

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

声明一个工作簿对象

loadWorkbook(file, password=NULL) #用于声明一个工作簿对象

提醒：如果excel文件不在工作空间内，file最好指定为绝对路径

wb

sheets

函数 参数
getFirstRowNum() 无参。该函数必须基于sheet对象
getLastRowNum() 无参。该函数必须基于sheet对象
下面以xlsx::readColumns()为例获取数据：

该函数必须提供数据的起始列索引值、终止列索引值、起始行索引值、终止行索引值；

dataTmp

读取数据【方法二】
另一种方法相对【方法一】要好一点，先是将所有单元格的值获取出来，再生成数据框。（稍微复杂一点）
函数 参数 注释
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

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”
1
2
3
4
5
将这些坐标值拆分出来，作为等会重排数据的索引

addresses

datas.name

• if (x[1] == “1”) {
  *

.GlobalEnv$datas.name = c(.GlobalEnv$datas.name,.GlobalEnv$values[[1]])

*

.GlobalEnv$values[[1]] <- null < code></->

• } else {
  *

.GlobalEnv$datas[x[1],x[2]] <- .globalenv$values[[1]] < code></->

*

.GlobalEnv$values[[1]] <- null < code></->

• }
• }))

names(datas)

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

函数 参数
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
1
2
3
说是这个名称的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

sheet

用上面生成的datas数据框对象，取前4行数据写入当前sheet对象中

addDataFrame(data[1:4,],sheet,row.names = F)
saveWorkbook(wb,file = “iris_new.xlsx”)
1
2
3

DataFrame(aa,sheet1)
sheet2

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

1
如果是在已有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”)
1
2
3
4
5
6
7
8
至此，你应该知道如何在原有工作表基础上新增数据行了吧？多么方便！！
如果要增加新的sheet工作表，只需将sheet重新定义一个新的sheetName即可。

Original: https://blog.csdn.net/selectopti/article/details/115912372
Author: selectopti
Title: R语言 xlsx 读写Excel数据