How to align two named lists of tibbles and insert them into excel worksheets?

I want to align lists of data frames to insert them into their own excel workbook. Below is example code.

library(tidyverse)
library(XLConnect)


a <- tibble(x = 1:10, y = 11:20, z = 21:30)
b <- tibble(x = 1:10, y = 11:20, z = 21:30)
c <- tibble(x = 1:10, y = 11:20, z = 21:30)

ldf_abc <- list(a,b,c)
names(ldf_abc) <- c("a", "b", "c")

c <- tibble(n = 1:10, o = 11:20, p = 21:30)
a <- tibble(n = 1:10, o = 11:20, p = 21:30)
b <- tibble(n = 1:10, o = 11:20, p = 21:30)

ldf_cab <- list(c,a,b)
names(ldf_cab) <- c("c", "a", "b")

In this example, I would like to align ldf_cab with ldf_abc.

After, I would like to put the values in ldf_cab and ldf_abc in their own excel file. (eg. a.xls contains variables n o p in sheet 1 and variables x y z in sheet 2. Below is my attempt (I do not get very far)

my_order <- c("a.xlsx", "b.xlsx", "c.xlsx")
my_wb_l <- lapply(my_order, function(x) loadWorkbook(filename = paste0("~/Desktop/", x), create = TRUE))

From this block I get this error.

 Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.FillPatternType.getCode()S

How would I go about solving this problem.

Solution

try

library(purrr)
walk(c("a", "b", "c"), function(x) {
  wb <- loadWorkbook(filename = paste0("~/Desktop/", x,".xlsx"), create = TRUE)
  createSheet(wb, "ldf_cab")
  createSheet(wb, "ldf_abc")
  writeWorksheet(wb, ldf_cab[[x]], sheet = 1)
  writeWorksheet(wb, ldf_abc[[x]], sheet = 2)
  saveWorkbook(wb)
})

BTW, if XLConnect gives you problems, you might experiment with openxlsx. I've had much better luck getting that to work. A solution with that would be

library(openxlsx)
walk(c("a", "b", "c"), function(x) {
  temporary_list <- list(
    ldf_cab[[x]],
    ldf_abc[[x]]
  )
  write.xlsx(temporary_list, file = paste0("~/Desktop/",x,".xlsx"))
})