Exporting multiple tibbles to XLSX -- sheetName problem when using deparse(substitute())

Question

I have 15 tibbles that I want to export to a single XLSX workbook, with the sheetName for each set to be the same as the name of the tibble object. To export a single tibble, this works just fine:

library(xlsx)

    my_tibble1 %>% 
      write.xlsx("output_filename.xlsx", 
                 sheetName = "my_tibble1", 
                 append = TRUE)

However, there are enough of these tibbles that writing all that out for each one is time-consuming. So, I wrote a function:

output_expediter <- function(df, output_filename) {
      write.xlsx(df, 
                 output_filename, 
                 sheetName = deparse(substitute(df)), 
                 append = TRUE)

This function successfully writes the tibble to a new sheet in the output workbook, BUT the sheetName is always a single period (".").

All the variable names used for the tibbles are limited to lowercase characters and underscores, and all of them are 31 or fewer characters long, so I don't think any of them violate XLSX format conventions. In the R console, running:

deparse(substitute(my_tibble1))

yields "my_tibble1" as expected.

Any ideas for why this is happening? Any possible workarounds, other than just typing out the names of each sheet?

Answer 1

An option is openxlsx

library(openxlsx)
library(tibble)
output_expediter <- function(df, output_filename) {
     nm1 <- deparse(substitute(df))
     wb <- createWorkbook()
     addWorksheet(wb, sheetName = nm1)
     writeData(wb, sheet= nm1, x = df)
     saveWorkbook(wb =wb, file = output_filename, overwrite = TRUE)
     }


file1 <- "hello.xlsx"
df1 <- tibble(col1 = 1:5, col2 = 6:10)
output_expediter(df1, file1)

-output

---

If we need to write muliple files,

library(purrr)    
wb1 <- createWorkbook()
output_expediter <- function(df, wb, nm1, output_filename) {


     addWorksheet(wb, sheetName = nm1)
     writeData(wb, sheet= nm1, x = df)
     saveWorkbook(wb =wb, file = output_filename, overwrite = TRUE)
     }

lst1 <- lst(df1= df1, df2= df1)
iwalk(lst1, ~ output_expediter(.x, wb = wb1, .y, file))