R 使用扳手标题分隔符将数据导出到 excel

发布于04月22日

我有一个数据框，大概是这样的:

data <- data.frame(X = c("A", "B", "C", "D", "E"),
                   Y = c(1,1,2,2,3),
                   Choice1.a = c(2,2,2,3,3),
                   Choice1.b = c(5,6,5,5,6),
                   Choice2.a = c(1,1,1,2,2),
                   Choice3.b = c(6,6,5,5,6))

I would like to save it to an excel file so that it looks something like this:

有人知道这是否可能吗？

如果不是，我会满足于仅仅改变".EXCEL文件中的分隔符为"".

推荐答案

我会用两个部分来解决手头的问题.标题由第1行和第2行组成，主体由从3开始的其余行组成.使用openxlsx2 0.6.1(当前在CRAN传入队列中)重新创建该表，如下所示.

library(openxlsx2)
packageVersion("openxlsx2")
#> [1] '0.6.1'

# table data
data <- data.frame(
  X = c("A", "B", "C", "D", "E"),
  Y = c(1, 1, 2, 2, 3),
  Choice1.a = c(2, 2, 2, 3, 3),
  Choice1.b = c(5, 6, 5, 5, 6),
  Choice2.a = c(1, 1, 1, 2, 2),
  Choice2.b = c(6, 6, 5, 5, 6)
)

# table header
head <- data.frame(
  x = c("", "X"),
  y = c("", "Y"),
  c1_a = c("Choice 1", "a"),
  c1_b = c("", "b"),
  c2_a = c("Choice 2", "a"),
  c2_b = c("", "b")
)

# create a temporary file
tmp <- temp_xlsx()

wb_workbook() %>%
  wb_add_worksheet() %>%
  # write data without column names
  # - header
  wb_add_data(x = head, colNames = FALSE) %>%
  # - body
  wb_add_data(dims = "A3:F8", x = data, colNames = FALSE) %>%
  # merge groups
  # note: with previous openxlsx2 releases cols needs to be c("C", "D") or 3:4
  wb_merge_cells(cols = "C:D", rows = 1) %>%
  wb_merge_cells(cols = "E:F", rows = 1) %>%
  # style groups
  wb_add_cell_style(dims = "C1:F1", horizontal = "center") %>%
  # save the output
  wb_save(tmp)

# # open file
# xl_open(tmp)