Writing data with multiple header rows

[JanMarvin]

This SO asks how to write data with multiple headers. There are two solutions to this issue, but I prefer writing such table in two steps. The second solution uses an openxlsx2 option to convert strings to numerics on the fly. Note that although this option is supported, it can have unintended side effects.

writing the table as two data frames

In R data frames, each column consists of a single data type. So if we have several rows of characters and append them over some numbers, the numbers will be converted to characters. Once we write this data to our output, we will write "1" instead of 1. If we write the header and the body in two steps, we can avoid the implicit conversion to characters.

The problem is that we need to make sure that our header and body columns match and if we do not check that, we might generate nonsense. However, I strongly believe that one should check the data as soon as it is written.

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

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

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

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

everything in a single data frame

Still, there may be cases where we have everything in a single data frame. Multiple headers and numeric data mixed together. In this case openxlsx2.string_nums can be of help. If this option is set, openxlsx2 will check each cell to see if it can be expressed as numeric. If this is the case, it will convert on the fly.

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

data <- data.frame(
  x = c("", "X", "A", "B", "C", "D", "E"),
  y = c("", "Y", "1", "1", "2", "2", "3"),
  c1_a = c("Choice 1", "a", "2", "2", "2", "3", "3"),
  c1_b = c("", "b", "5", "6", "5", "5", "6"),
  c2_a = c("Choice 2", "a", "1", "1", "1", "2", "2"),
  c2_b = c("", "b", "6", "6", "5", "5", "6")
)

# convert string numerics to numerics: write "1" as 1
options(openxlsx2.string_nums = 2)

tmp <- temp_xlsx()

wb_workbook() %>%
  wb_add_worksheet() %>%
  wb_add_data(x = data, colNames = FALSE) %>%
  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)

# xl_open(tmp)