介紹如何使用 R 的 openxlsx 套件將 R 的分析結果與圖形匯出至 Excel 檔案中。
R 語言的
openxlsx 套件是一個可以用來建立並編輯 Excel 檔案的套件，透過這個套件可以將 R 的各種資料與分析結果轉成 Excel 檔案，以下是使用教學以及範例。
安裝 openxlsx 套件
openxlsx 套件可從官方的 CRAN 套件庫下載安裝：
# 安裝 openxlsx 套件 install.packages("openxlsx")
安裝完成之後，將其載入即可使用：
# 載入 openxlsx 套件 library(openxlsx)
建立 Excel 檔案
openxlsx 所提供的 write.xlsx 函數可以將 data.frame 的資料直接寫入 Excel 檔案中，其效果類似 write.csv 函數，但是 write.xlsx 可以直接輸出為 xlsx 檔案：
# 將 data.frame 寫入 Excel 檔案 write.xlsx(iris, file = "writeXLSX1.xlsx")
write.xlsx 函數也可以直接將資料轉為 Excel 的表格：
# 將 data.frame 寫入 Excel 檔案，轉為表格 write.xlsx(iris, file = "writeXLSXTable1.xlsx", asTable = TRUE)
若要將多張 data.frame 同時寫入一個 Excel 檔案，可以先用 R 的列表（list）打包之後，再以 write.xlsx 寫入至 Excel 檔案中：
# 將多張 data.frame 寫入 Excel 檔案 l <- list("IRIS" = iris, "MTCARS" = mtcars) write.xlsx(l, file = "writeXLSX2.xlsx") write.xlsx(l, file = "writeXLSXTable2.xlsx", asTable = TRUE)
不同的 data.frame 會儲存於不同的 Excel 工作表中。
Excel 儲存格格式
透過設定不同的資料類型，可以讓 write.xlsx 函數在匯出資料至 Excel 時自動設定儲存格格式。
# 設定框線樣式 options("openxlsx.borderColour" = "#4F80BD") options("openxlsx.borderStyle" = "thin") # 日期與時間格式 options("openxlsx.datetimeFormat" = "yyyy-mm-dd hh:mm:ss") # 建立資料 df <- data.frame( "Time" = Sys.time(), "Cash" = paste("$",1:20), "Cash2" = 31:50, "hLink" = "https://CRAN.R-project.org/", "Percentage" = seq(0, 1, length.out=20), "TinyNumbers" = runif(20) / 1E9, stringsAsFactors = FALSE) # 設定資料類型，用於 Excel 儲存格格式 class(df$Cash) <- "currency" # 貨幣 class(df$Cash2) <- "accounting" # 會計 class(df$hLink) <- "hyperlink" # 超連結 class(df$Percentage) <- "percentage" # 百分比 class(df$TinyNumbers) <- "scientific" # 科學記號 # 將 data.frame 寫入 Excel 檔案 write.xlsx(df, "writeXLSX3.xlsx") write.xlsx(df, file = "writeXLSXTable3.xlsx", asTable = TRUE)
表格標題列樣式
在建立 Excel 表格時，可以自訂標題列樣式，例如讓標題列的文字旋轉 45 度。
# 建立樣式 hs <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD", halign = "center", valign = "center", textDecoration = "Bold", border = "TopBottomLeftRight", textRotation = 45) # 將 data.frame 寫入 Excel 檔案，並指定標題列樣式 write.xlsx(iris, file = "writeXLSX4.xlsx", borders = "rows", headerStyle = hs) write.xlsx(iris, file = "writeXLSX5.xlsx", borders = "columns", headerStyle = hs) # 將 data.frame 寫入 Excel 檔案，轉為表格，並指定標題列樣式 write.xlsx(iris, "writeXLSXTable4.xlsx", asTable = TRUE, headerStyle = createStyle(textRotation = 45))
產生的 Excel 表格會類似這樣，標題列文字旋轉 45 度。
自行建立 Excel 活頁簿
如果想要將各種非 data.frame 的資料寫入 Excel 檔案中，可以使用自行建立 Excel 活頁簿的方式，手動寫入各種資料，以下是建立一個 Excel 活頁簿，並將各種資料寫入的範例。
# 建立 Excel 活頁簿 wb <- createWorkbook() # 設定框線樣式 options("openxlsx.borderColour" = "#4F80BD") options("openxlsx.borderStyle" = "thin") # 設定 Excel 活頁簿預設字型 modifyBaseFont(wb, fontSize = 10, fontName = "Arial Narrow") # 新增工作表 addWorksheet(wb, sheetName = "Motor Trend Car Road Tests", gridLines = FALSE) addWorksheet(wb, sheetName = "Iris", gridLines = FALSE) # 鎖定第一張工作表的第一行與第一列 freezePane(wb, sheet = 1, firstRow = TRUE, firstCol = TRUE) # 將 mtcars data.frame 資料寫入第一張工作表 writeDataTable(wb, sheet = 1, x = mtcars, colNames = TRUE, rowNames = TRUE, tableStyle = "TableStyleLight9") # 設定欄寬 setColWidths(wb, sheet = 1, cols = "A", widths = 18) # 將 iris data.frame 資料寫入第二張工作表 writeDataTable(wb, sheet = 2, iris, startCol = "K", startRow = 2) # 將 ggplot2 繪製的圖形插入第二張工作表的 B16 位置 require(ggplot2) qplot(data=iris, x = Sepal.Length, y= Sepal.Width, colour = Species) insertPlot(wb, 2, xy=c("B", 16)) # 建立組平均與變異數資料表格 means <- aggregate(x = iris[,-5], by = list(iris$Species), FUN = mean) vars <- aggregate(x = iris[,-5], by = list(iris$Species), FUN = var) # 建立樣式 headSty <- createStyle(fgFill="#DCE6F1", halign="center", border = "TopBottomLeftRight") # 將組平均資料表格寫入第二張工作表的 B3 位置 writeData(wb, 2, x = "Iris dataset group means", startCol = 2, startRow = 2) writeData(wb, 2, x = means, startCol = "B", startRow=3, borders="rows", headerStyle = headSty) # 將組變異數資料表格寫入第二張工作表的 B10 位置 writeData(wb, 2, x = "Iris dataset group variances", startCol = 2, startRow = 9) writeData(wb, 2, x= vars, startCol = "B", startRow=10, borders="columns", headerStyle = headSty) # 設定欄寬 setColWidths(wb, 2, cols=2:6, widths = 12) setColWidths(wb, 2, cols=11:15, widths = 15) # 設定標題樣式 titleStype <- createStyle(fontSize=14, textDecoration=c("bold", "italic")) addStyle(wb, 2, style = titleStyle, rows=c(2,9), cols=c(2,2)) # 儲存 Excel 活頁簿 saveWorkbook(wb, "basics.xlsx", overwrite = TRUE)
參考資料：openxlsx vignette