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即可。

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。