感謝Adrian A. Drǎgulescu發(fā)布的xlsx包

xlsx包提供了必要的工具來(lái)與Excel 2007進(jìn)行交互。用戶可以閱讀和編寫xlsx，并可以通過(guò)設(shè)置數(shù)據(jù)格式、字體、顏色和邊框來(lái)控制電子表格的外觀。設(shè)置打印區(qū)域，縮放控制，創(chuàng)建分割和凍結(jié)面板，添加頁(yè)眉和頁(yè)腳。包使用Apache POI項(xiàng)目中的java庫(kù)。本篇主要分享利用xlsx工具包在讀寫xlsx過(guò)程中所碰到的問(wèn)題及解決辦法。

工具準(zhǔn)備

強(qiáng)烈建議大家使用RStudio這個(gè)IDE，它是以今為止對(duì)R語(yǔ)言最友好的一個(gè)IDE之一，而且使用很方便。特別是在新包下載安裝的時(shí)候，只需請(qǐng)求要安裝的包名，RStudio會(huì)自動(dòng)將關(guān)聯(lián)的其他包也一并下載并安裝。

安裝R、安裝RStudio；

一個(gè)簡(jiǎn)單的示例數(shù)據(jù)（本次以iris鳶尾花數(shù)據(jù)為例）；

下載安裝xlsx（Rstudio會(huì)同步下載并安裝rJava, xlsxjars兩個(gè)包）；

> # 下載并安裝xlsx包
> install.packages("xlsx")
> library(xlsx)

【基礎(chǔ)】簡(jiǎn)單讀取excel文件數(shù)據(jù)

假如是csv或txt等文本類的數(shù)據(jù)文件，利用R內(nèi)置函數(shù)read.csv()與read.table()就可讀取（注意編碼格式的參數(shù)設(shè)置）。Excel由于使用范圍最廣，很多問(wèn)題不可避免，因此，xlsx包提供了專門讀取xlsx的函數(shù)read.xlsx和read.xlsx2，為什么有兩個(gè)呢？請(qǐng)看以下區(qū)別：

其實(shí)只是細(xì)微的差別，大家自己體會(huì)即可。下面給個(gè)參考案例：

> # 指定file和sheetIndex（或sheetName），即可定位到相應(yīng)的工作表
> data1 <- read.xlsx("iris.xlsx",sheetIndex = 1)
> head(data1)

【基礎(chǔ)】簡(jiǎn)單寫入數(shù)據(jù)到excel文件

切莫用R內(nèi)置函數(shù)read.csv()與read.table()去生成xlsx文件，會(huì)有你意想不到的麻煩，還是采用專業(yè)的包來(lái)解決問(wèn)題吧。 xlsx包同樣提供了兩個(gè)寫入數(shù)據(jù)的函數(shù)write.xlsx()和write.xlsx2()，其中細(xì)微區(qū)別自行參透（注意參數(shù) ...）。

下面是參考案例：

># 指定x待寫入數(shù)據(jù)，file生成的文件名，row.names為false則不生成行名，指定sheet工作表名為Sheet1
>write.xlsx(iris, file = "iris.xlsx", row.names = FALSE, sheetName = "Sheet1")

想必會(huì)有人在這里踩坑，大家應(yīng)該注意到有一個(gè)append的參數(shù)，是否認(rèn)為將其值設(shè)置為TRUE的話，就可以多次向表中寫入數(shù)據(jù)？那就真踩坑了。查看xlsx包中的注釋也很模糊：

> # a logical value indicating if x should be appended to an existing file. 
> # 翻譯：一個(gè)邏輯值，指示是否應(yīng)該將x附加到現(xiàn)有文件中

附加到現(xiàn)有文件中，實(shí)際上是增加新的sheet，而非在原有sheet工作表中繼續(xù)增加數(shù)據(jù)。如需在同一個(gè)sheet工作表中多次增加數(shù)據(jù)，請(qǐng)繼續(xù)往下看。

【進(jìn)階】隨心所欲讀取excel中的各種信息

說(shuō)隨心所欲 一點(diǎn)不夸張，不僅可以取出excel中的數(shù)據(jù)，還能識(shí)別excel單元格的樣式（包括顏色、字體、大小、標(biāo)注、數(shù)據(jù)類型等等）。其原理與數(shù)據(jù)庫(kù)有點(diǎn)相似，先是定義一個(gè)工作簿的對(duì)象，再基于工作簿定義里面的工作表，進(jìn)而逐級(jí)查詢。下面進(jìn)行詳細(xì)介紹：

【樣例數(shù)據(jù)】文件名：iris10.xlsx。

聲明一個(gè)工作簿對(duì)象

> # loadWorkbook(file, password=NULL)  #用于聲明一個(gè)工作簿對(duì)象
> # 提醒：如果excel文件不在工作空間內(nèi)，file最好指定為絕對(duì)路徑
> wb <- createWorkbook("iris10.xlsx")

檢索工作簿中的sheet

> # sheets <- getSheets(wb)  #用于生成一個(gè)list對(duì)象，其中包含所有工作表的信息,數(shù)據(jù)類型為rJava::jobjRef，在此不深入講解
> sheets <- getSheets(wb)

定位目標(biāo)sheet

> # 本例只有一個(gè)sheet，名稱為“Sheet1”
> sheet <- sheets[["Sheet1"]]  # sheet的數(shù)據(jù)類型為rJava::jobjRef

讀取數(shù)據(jù)【方法一】

上面read.xlsx()方法能夠?qū)⒄麄€(gè)sheet工作表的數(shù)據(jù)讀取出來(lái)，在這里提供另一種方法，不過(guò)相對(duì)麻煩一點(diǎn)，使用的是xlsx::readColumns()函數(shù)

xlsx::readRows()使用起來(lái)比較麻煩，不如xlsx::readColumns()好用，有興趣的可自行研究一下。另外還有兩個(gè)函數(shù)，用于定位表內(nèi)數(shù)據(jù)第一行和最后一行的索引（這里與Java的性質(zhì)一致，從0開始算起）

xlsx::readRows()使用起來(lái)比較麻煩，不如xlsx::readColumns()好用，有興趣的可自行研究一下。另外還有兩個(gè)函數(shù)，用于定位表內(nèi)數(shù)據(jù)第一行和最后一行的索引（這里與Java的性質(zhì)一致，從0開始算起）

下面以xlsx::readColumns()為例獲取數(shù)據(jù)：

> # 該函數(shù)必須提供數(shù)據(jù)的起始列索引值、終止列索引值、起始行索引值、終止行索引值；
> 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決定了輸出結(jié)果為一個(gè)數(shù)據(jù)框。

缺點(diǎn)：在不清楚數(shù)據(jù)結(jié)構(gòu)的情況下，首行和末行索引值可以求得，但列數(shù)一般難以確定，可能導(dǎo)致列缺失或生成多余的列

讀取數(shù)據(jù)【方法二】

另一種方法相對(duì)【方法一】要好一點(diǎn)，先是將所有單元格的值獲取出來(lái)，再生成數(shù)據(jù)框。（稍微復(fù)雜一點(diǎn)）

注意：這里連同標(biāo)題行也作為單元格數(shù)據(jù)一并獲取，并且如果有null值的單元格，會(huì)跳過(guò)該單元格

> # 獲取cells進(jìn)而獲取values
> cells <- sheet %>% getRows() %>% getCells()
> values <- lapply(cells,getCellValue)

values獲取出來(lái)就如下面這個(gè)樣子，你會(huì)發(fā)現(xiàn)value的名稱向量，每個(gè)值都包含了所在單元格的x、y坐標(biāo)值。

> 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"

將這些坐標(biāo)值拆分出來(lái)，作為等會(huì)重排數(shù)據(jù)的索引

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

接下來(lái)就只需要將其進(jìn)行重排，形成數(shù)據(jù)框即可。

> datas.name <- vector(mode = "character")  #聲明一個(gè)空的向量，用來(lái)存放標(biāo)題
> datas <- data.frame()  # 聲明一個(gè)空的數(shù)據(jù)框，用來(lái)存放目標(biāo)數(shù)據(jù)
> # 用sapply代替for做循環(huán)，避免占用大量?jī)?nèi)存。同時(shí)注意sapply使用時(shí)的環(huán)境問(wèn)題，用.GlobalEnv指向最外層環(huán)境的變量。
> # 這里只對(duì)數(shù)據(jù)進(jìn)行重排，無(wú)需進(jìn)行計(jì)算，所以invisible不顯示計(jì)算結(jié)果
> 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  #最后在添加標(biāo)題
> View(datas)

得到結(jié)果與原excel數(shù)據(jù)一致

獲取單元格樣式與獲取數(shù)據(jù)的方式一致，這里不再增加過(guò)多篇幅講解，只做簡(jiǎn)單介紹。以下函數(shù)按函數(shù)名字面理解。

其他函數(shù)后續(xù)如有機(jī)會(huì)，再做詳細(xì)介紹吧。

【進(jìn)階】隨心所欲將數(shù)據(jù)寫入excel文件

我想大家更想看到的就是這部分內(nèi)容了。確實(shí)在日常處理數(shù)據(jù)時(shí)，將數(shù)據(jù)存儲(chǔ)到excel中進(jìn)行傳遞是常有的事，誰(shuí)叫excel是微軟親生的呢。閑話少說(shuō)，直入正題。

前面基礎(chǔ)篇通過(guò)write.xlsx()函數(shù)將數(shù)據(jù)寫入excel文件中，同時(shí)指定sheet名稱。但這種寫入是一次性的，即一次寫入多少就多少。在工作簿里面新增sheet工作表用append控制，但在同個(gè)sheet上繼續(xù)寫入數(shù)據(jù)，會(huì)報(bào)錯(cuò)：

> 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

說(shuō)是這個(gè)名稱的sheet已經(jīng)存在同名的了！

這次我們采用高級(jí)一點(diǎn)的方法，跟前面進(jìn)階讀取數(shù)據(jù)一樣，先是定義一個(gè)工作簿的對(duì)象，再創(chuàng)建或加載sheet工作表。

前面講過(guò)如何加載已有工作簿，這里以生成新excel工作簿為例，將數(shù)據(jù)寫入文件中

> wb <- xlsx::createWorkbook()
> sheets <- getSheet()
# 新生成的工作簿沒(méi)有sheet，系統(tǒng)提示：Workbook has no sheets!
> sheet <- createSheet(wb,sheetName = "newSheet1")

此時(shí)R內(nèi)存中已經(jīng)生成了一個(gè)工作簿，包含一個(gè)空的sheet工作表，通過(guò)addDataFrame()函數(shù)將數(shù)據(jù)寫入sheet中.

> # 用上面生成的datas數(shù)據(jù)框?qū)ο?，取?行數(shù)據(jù)寫入當(dāng)前sheet對(duì)象中
> addDataFrame(data[1:4,],sheet,row.names = F)
> saveWorkbook(wb,file = "iris_new.xlsx")

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

如果是在已有excel工作簿上操作，這里最好做一個(gè)判斷，避免覆蓋現(xiàn)有數(shù)據(jù)，造成不必要的麻煩。如果當(dāng)前sheet的最后一行索引不等于零（說(shuō)明有數(shù)據(jù)），則將新數(shù)據(jù)寫到最后一行數(shù)據(jù)的下一行，同時(shí)不加入列名行(col.names = FALSE)；如果為零則將數(shù)據(jù)直接添加到sheet中。

> # 用上面生成的datas數(shù)據(jù)框?qū)ο?，取?行數(shù)據(jù)寫入當(dāng)前sheet對(duì)象中
> 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")

至此，你應(yīng)該知道如何在原有工作表基礎(chǔ)上新增數(shù)據(jù)行了吧？多么方便?。?/p>

如果要增加新的sheet工作表，只需將sheet重新定義一個(gè)新的sheetName即可。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。