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

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

工具準(zhǔn)備

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

安裝R、安裝RStudio；

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

下載安裝xlsx（Rstudio會同步下載并安裝rJava, xlsxjars兩個包）；

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

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

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

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

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

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

切莫用R內(nèi)置函數(shù)read.csv()與read.table()去生成xlsx文件，會有你意想不到的麻煩，還是采用專業(yè)的包來解決問題吧。 xlsx包同樣提供了兩個寫入數(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")

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

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

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

【進階】隨心所欲讀取excel中的各種信息

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

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

聲明一個工作簿對象

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

檢索工作簿中的sheet

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

定位目標(biāo)sheet

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

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

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

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

xlsx::readRows()使用起來比較麻煩，不如xlsx::readColumns()好用，有興趣的可自行研究一下。另外還有兩個函數(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é)果為一個數(shù)據(jù)框。

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

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

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

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

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

values獲取出來就如下面這個樣子，你會發(fā)現(xiàn)value的名稱向量，每個值都包含了所在單元格的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)值拆分出來，作為等會重排數(shù)據(jù)的索引

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

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

> datas.name <- vector(mode = "character")  #聲明一個空的向量，用來存放標(biāo)題
> datas <- data.frame()  # 聲明一個空的數(shù)據(jù)框，用來存放目標(biāo)數(shù)據(jù)
> # 用sapply代替for做循環(huán)，避免占用大量內(nèi)存。同時注意sapply使用時的環(huán)境問題，用.GlobalEnv指向最外層環(huán)境的變量。
> # 這里只對數(shù)據(jù)進行重排，無需進行計算，所以invisible不顯示計算結(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ù)的方式一致，這里不再增加過多篇幅講解，只做簡單介紹。以下函數(shù)按函數(shù)名字面理解。

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

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

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

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

> 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已經(jīng)存在同名的了！

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

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

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

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

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

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

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

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

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

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

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

最新評論