0

I need to write an excel file with multiple sheets named "Cases", "Coordinators", "Contacts"

I have checked the class of each--e.g., class(Cases)--and the result in each case is:

class(Cases)
[1] "tbl_df"     "tbl"        "data.frame"

I combine these dfs into a list called "compiledData" as follows:

compiledData <- c(Cases, Coordinators, Contacts)

and checked class of compiledData as follows:

class(compiledData)
[1] "list"

So, I am confused as to why the following code results in the following error:

write_xlsx(compiledData, "FileName.xlsx")
Error in write_xlsx(compiledData, "FileName.xlsx") : 
  Argument x must be a data frame or list of data frames

Any assistance would be greatly appreciated. I have been searching for two days for a solution, but have not found an answer. Apologies in advance if a solution has already been posted and I've not seen it.

One update: I changed compiledData <- c(Cases, Coordinators, Contacts) to: compiledData <- list(Cases, Coordinators, Contacts) and the Excel file is now being created without error. My new problem is that the worksheets in the excel file are not labeled "Cases", "Coordinators", "Contacts"--they are Sheet1, Sheet2, Sheet3

Progman
  • 16,827
  • 6
  • 33
  • 48
techieshrink
  • 3
  • 3

2 Answers2

0

When you store the data frames in a list (ie, compiledData <- list(Cases, Coordinators, Contacts)) the elements are unnamed. For instance, in this example:

df1 <- df2 <- df3 <- tibble::tibble(A = 1:5, B = 1:5, C = 1:5)
comb <- list(df1, df2, df3)

names(comb)
# NULL

To export the to excel with the df names, you just have to name the elements in the list (ie, using names() or setNames()):

df1 <- df2 <- df3 <- tibble::tibble(A = 1:5, B = 1:5, C = 1:5)
comb <- list(df1, df2, df3)

names(comb) <- c("df1", "df2", "df3")
writexl::write_xlsx(comb, "test.xlsx")

# or one-liner

writexl::write_xlsx(setNames(comb, c("df1", "df2", "df3")), "test.xlsx")

Note: since you resolved your initial question (RE: properly exporting) but edited to pose a new one (RE: naming sheets), please edit your question to remove the original question and only focus on the new one. This will help future readers. Good luck!

jpsmith
  • 11,023
  • 5
  • 15
  • 36
0

I did away with the creation of "compiledData"

(compiledData <- c(Cases, Coordinators, Contacts)

And instead accomplished the writing of the excel file with named worksheets like this: write_xlsx(list(Cases = Cases, Coordinators = Coordinators, Contacts = Contacts), "test.xlsx")

It is a bit verbose and maybe a better way to do it, but it works :)

Julian
  • 6,586
  • 2
  • 9
  • 33
techieshrink
  • 3
  • 3
  • While this is a fine answer, it is essentially the same answer as that provided by @jpsmith. – Wil Feb 22 '23 at 15:02