感謝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，為什么有兩個呢？請看以下區別：

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

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

【基礎】簡單寫入數據到excel文件

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

下面是參考案例：

># 指定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::readRows()使用起來比較麻煩，不如xlsx::readColumns()好用，有興趣的可自行研究一下。另外還有兩個函數，用于定位表內數據第一行和最后一行的索引（這里與Java的性質一致，從0開始算起）

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

下面以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決定了輸出結果為一個數據框。

缺點：在不清楚數據結構的情況下，首行和末行索引值可以求得，但列數一般難以確定，可能導致列缺失或生成多余的列

讀取數據【方法二】

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

注意：這里連同標題行也作為單元格數據一并獲取，并且如果有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數據一致

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

其他函數后續如有機會，再做詳細介紹吧。

【進階】隨心所欲將數據寫入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工作表。

前面講過如何加載已有工作簿，這里以生成新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即可。

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

原文鏈接：https://blog.csdn.net/cjy663636/article/details/91877717