Categories: ExcelR

R 使用 openxlsx 將表格資料、圖形轉為 Excel 檔案教學與範例

介紹如何使用 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")

Excel 檔案

write.xlsx 函數也可以直接將資料轉為 Excel 的表格：

# 將 data.frame 寫入 Excel 檔案，轉為表格
write.xlsx(iris, file = "writeXLSXTable1.xlsx", asTable = TRUE)

Excel 表格

若要將多張 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 多張工作表

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 表格

表格標題列樣式

在建立 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 度。

標題列文字旋轉 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)