I have data that looks like this:

Here’s some code to create that:

Terrain <- c("Lake", "Desert")
Person <- c("Jim", "Bob", "Steve", "Alice")
Value <- c("A", "B", "C", "D")
Table1 <- data.frame(Terrain, Person, Value)

Let’s call it Table1, and I’m hoping to write it to four Excel files named “Jim.xlsx”, “Bob.xlsx”, “Steve.xlsx”, and “Alice.xlsx” where each has tabs “Lake” and “Desert.”

There’s the further restriction that I can’t use Java (workplace download restrictions), so I believe openxlsx is my only option.

I’ve found this way works to write multiple Excel files:

person_names <- split(Table1, Table1$Person)

lapply(1:length(person_names), 
       function(x) write.xlsx(person_names[[x]], file = paste0(names(person_names[x]), '.xlsx'),
                              sheetName = paste0(names(person_names[x])), 
                              append = TRUE, row.names = FALSE))

But this only gives me one tab with all data. I know you can also write multiple data frames to tabs easily using openxlsx and a list, but this gives me the reverse issue.

Is there a way that I’m missing?

Thanks!