R: write_xlsx with evaluation problem in argument

Question

I have a loop like this:

library(writexl)
file_output="C:/test/test.xlsx"
for(i in 1:2){
    df<-iris[i,]
    write_xlsx(list(i=df),file_output)
    }

I would like to store each iteration in a separate sheet named the value of i (or in my real program: the value of some variable). Is this possible with write_xlsx, or in general, is it possible to make write_xlsx (or some other function) to interpret:

"list(i=df)" as "list(1=df)" and "list(2=df)"

See here if your problem is how to save in multiple sheets: https://stackoverflow.com/questions/27713310/r-easy-way-to-export-multiple-data-frame-to-multiple-excel-worksheets — RLave, Mar 01 '19 at 09:09
those are with java and perl dependent packages, I'm trying to distance myself from those. — Helen, Mar 01 '19 at 09:10
There is also a solution for `writexl`: `sheets <- list("sheet1Name" = sheet1, "sheet2Name" = sheet2); write_xlsx(sheets, "path/to/location")` — RLave, Mar 01 '19 at 09:12
But there you have to hard code your sheet names, I'm wondering how I can use the value of variable instead. — Helen, Mar 01 '19 at 09:13

score 1 · Answer 1 · answered Mar 01 '19 at 09:21

You don't need a for loop for this probably.

Just use names() on the list object as.character() in order to name the sheets.

l <- lapply(1:2, function(i) iris[i, ]) #creates separated df for each row
names(l) <- as.character(1:2) # sets the names of each element in the list
# these will be the sheets name


file="path_to_file.xlsx" # path to file name
library(writexl)

write_xlsx(l, file)

score 1 · Accepted Answer · answered Mar 01 '19 at 09:22

Instead of i = df you must name the list elements. For your loop this would mean:

file_output = "C:/test/test.xlsx"
for(i in 1:2){
  df <- iris[i,]
  out <- list(df)
  names(out) <- i
  write_xlsx(out, file_output)
}

However, this will result in one file per data.frame, since write_xlsx does not append to existing files (at least to my knowledge). If you want to have only one file with sheets for the various data.frames, you'd have to adapt your code:

file_output = "C:/test/test.xlsx"
vars <- 1:2
out <- vector(mode = "list", length = length(vars))
for(i in vars){ # if you use variable names in vars, use seq_along(vars) instead of vars
  out[[i]] <- iris[i,]
}
names(out) <- vars
write_xlsx(out, file_output)

Since I do not see any merit in using a loop here, I'd even suggest to use map from the purrr package or lapply:

file_output = "C:/test/test.xlsx"
vars <- 1:2
out <- map(vars, ~ iris[.x,])
names(out) <- vars
write_xlsx(out, file_output)

R: write_xlsx with evaluation problem in argument

2 Answers2