R語言之xlsx包讀寫Excel數據的操作

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

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

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

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

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

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

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

> # 該函數必須提供數據的起始列索引值、終止列索引值、起始行索引值、終止行索引值；
> dataTmp <- readColumns(sheet, startColumn = 1, endColumn = 10,
            startRow = sheet$getFirstRowNum()+1, endRow = sheet$getLastRowNum()+1,
            header = T, as.data.frame=TRUE)

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

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

> 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

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

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

> # 用上面生成的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")