how to assign list element name to sheetName

user_8936239 0
Viewed 130

I'm trying to assign the name of each list element to the sheetName in write.xlsx, while creating new sheets for each element. I'm not sure how to work out the sheetNumber part in order to get the elements in the same sheet. Here is an example:

list_df = list('a'=data.frame(test1=NA,test2=NA),'b'=data.frame(test3=NA,test4=NA))
purrr::imap(list_df, ~write.xlsx(.x, "test.xlsx", sheetName = .y))

Note that this new edit was a partial solve by another member.

xlsx r
2 Answers

If you have a named list of dataframes you can directly pass it to write.xlsx command. 

openxlsx::write.xlsx(list_df, 'test.xlsx')
user_39629140

You could use purrr::pwalk

purrr::pwalk(
    list(list_df, names(list_df), names(list_df) != names(list_df)[1]),
    function(x, y, z) write.xlsx(x, "test.xlsx", sheetName = y, append = z))

The third z argument is the entry of a logical vector, that is FALSE for the first element of your list and TRUE for all other elements, and that determines whether we append sheets to the Excel file (true for all list elements except the first).

Note that this is based on the write.xlsx function from the xlsx package. It seems that openxlsx::write.xlsx can directly write a named list to multiple sheets, see @RonakShah's answer.

user_65309700
Related