R export data to excel with spanner header delimiter

Question

I have a data.frame that looks something like this:

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:

Does anyone know if this is possible?

If it's not, I would settle for just changing the "." delimiter to " " in the excel file.

Answer 1

I would solve the problem at hand with two sections. A header, consisting of rows 1 and 2, and the body consisting of the remaining rows starting at 3. Using openxlsx2 0.6.1 (currently in CRANs incoming queue) the table is recreated as follows.

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)

Answer 2

It isn't pretty, but I can get you close with the xlsx package. The xlsx::addMergedRegion is what merges the cells:

library(xlsx)
wb <- createWorkbook(type = "xlsx")
sheet <- createSheet(wb)

#' restructuring original data - you can skip this if you 
#' are up for manually tweaking in Excel
data2 <- rbind(colnames(data), data)
data2[1,] <- gsub("Choice|\\d+|\\.", "", data2[1,])
colnames(data2) <- c(""," ","Choice 1", "  ","Choice 2", "   ")

addDataFrame(data2, sheet, row.names = FALSE)

addMergedRegion(sheet, 1, 1, 3, 4)
addMergedRegion(sheet, 1, 1, 5, 6)

saveWorkbook(wb, file = "test.xlsx")