I have this df:

And I would like to create a xlsx file with something like

I know this is possible by entering manually the cells' reference, but I have a lot of countries, and this would be unsettling. Is there a way I can group the country variable in one merged cell?

here is a reproductible code:

tibble::tibble(
country = c('Canada','Canada','Canada','Argentina','Argentina','Chile'),
city = c('Montreal','Ottawa','Toronto','Cordoba','Mendoza','Santiago')
)

CodePudding user response：

This could be achieved via openxlsx::mergeCells. To this end I first create a list of the row indices to merge. Afterwards you could use e.g. lapply to loop over the list and merge the cells for each country.

library(openxlsx)

# Create list of rows to merge. Note: DF should be ordered by country
to_merge <- split(seq_len(nrow(dat)), dat$country)

fn <- tempfile(".xlsx")

wb <- createWorkbook()
addWorksheet(wb, 1)
writeData(wb, sheet = 1, x = dat)
lapply(to_merge, function(x) mergeCells(wb = wb, sheet = 1, cols = 1, rows = 1   x))
saveWorkbook(wb, fn, overwrite = TRUE)