Search code examples
rlapplyr-xlsx

Using write.xlsx inside lapply to write each data frame in Global Environment to each tab of a single Excel file

My Global Environment in R Studio consists of multiple data frames. I would like to write the data within each data frame to each tab within a single excel file.

To make this question reproducible, let us consider the following data frames in the Global Environment:

df1 <- data.frame(ID = c("001", "002", "003"), scores = c(5, 7, 6))
df2 <- data.frame(ID = c("001", "002", "003"), scores = c(3, 6, 4))
df3 <- data.frame(ID = c("001", "002", "003"), scores = c(7, 6, 7))
df4 <- data.frame(ID = c("001", "002", "003"), scores = c(4, 3, 7))

The desired output is one excel file, with 4 tabs, with the first tab named "df1" and containing the data of df1, and so and so forth.

I have tried the following, but they all do not work:

lapply(ls(), function(x) write.xlsx(ls(), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

lapply(names(ls()), function(x) write.xlsx(ls(), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

lapply(names(ls()), function(x) write.xlsx(ls[[x]], "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))

I know on Stackoverflow there are solutions to my query using a for loop. However, there are no solutions where the apply family of functions (such as lapply) are used to arrive at the answer.

Appreciate any help using lapply to solve this problem. Thanks!

Solution

  • First we create an empty .xlsx to append our sheets:

    library(xlsx)
wb = xlsx::createWorkbook(type='xlsx')
saveWorkbook(wb,"Overall_Output.xlsx")
rm(wb)#remove wb to avoid it in ls()

    Then we call each dataframe by their name, using get0() function:

    lapply(ls(), function(x) write.xlsx(get0(x), "Overall_Output.xlsx", sheetName = x, append = TRUE, col.names = TRUE, row.names = FALSE, showNA = FALSE))