0

Want to save each data.frame of a list separately as a Excel Workbook with write_xlsx function. Able to save each data.frame of a list separately as .RData but not as Excel Workbook. Any thoughts.

library(tidyverse)
library(writexl)

df1 <- data.frame(X = 1:3)
df2 <- data.frame(X = 5:6)

ls1 <-
  list(df1, df2) %>% 
  set_names(c("df1", "df2"))
ls1
#> $df1
#>   X
#> 1 1
#> 2 2
#> 3 3
#> 
#> $df2
#>   X
#> 1 5
#> 2 6


map(.x = names(ls1), .f = function(x1){
  assign(x = x1, ls1[[x1]])
  save(list = x1, file = paste0(x1, ".RData"))
  })
#> [[1]]
#> NULL
#> 
#> [[2]]
#> NULL


map(.x = names(ls1), .f = function(x1){
  assign(x = x1, ls1[[x1]])
  write_xlsx(x = x1, path = paste0(x1, ".xlsx"))
  })
#> Error in write_xlsx(x = x1, path = paste0(x1, ".xlsx")): Argument x must be a data frame or list of data frames

Edited

The following R code using write.xlsx function from openxlsx R package works and produces single Workbook with multiple sheets.

write.xlsx(x = ls1, file = "ls1.xlsx")

However, my requirement to save each data.frame in separate Workbook and tried the write.xlsx function from openxlsx R package which produces wrong output:

map(.x = names(ls1), .f = function(x1){
      assign(x = x1, ls1[[x1]])
      write.xlsx(x = x1, path = paste0(x1, ".xlsx"))
      })
MYaseen208
  • 22,666
  • 37
  • 165
  • 309
  • 2
    I'm not familiar with `writexl`, but I use the openxlsx package to do something like this. You create a workbook, then iterate over your list of data frames, writing each one to a sheet in the workbook. Is that basically what you're trying to do? The argument you have, `x1`, is a string, not a data frame. Why not just call `map` on `ls1`? – camille Feb 26 '22 at 21:25
  • You can skip the assign function and just use" `write.xlsx(ls1[[x1]], path = paste0(x1, ".xlsx"))` – Dave2e Feb 26 '22 at 21:54
  • Use lapply in combination with write.xlsx to „loop“ through your dataframes and store wach one separately. – deschen Feb 26 '22 at 22:11

1 Answers1

2

do:

library(openxlsx)
lapply(1:length(ls1), function(x) write.xlsx(ls1[[x]], file = paste0(names(ls1)[x], '.xlsx')))

The same code works exactly the same with map:

library(purrr)
map(.x = 1:length(ls1),
    .f = function(x) write.xlsx(ls1[[x]], file = paste0(names(ls1)[x], '.xlsx')))
deschen
  • 10,012
  • 3
  • 27
  • 50