Append lines of text above EACH data frame in list when outputting

Question

Let's say I have two datasets (in actuality, I have 6) that are pulled in as a list, and I run a series of functions on them, and then I need exported these as separate excel files, naming them by a subset of their filenames. Before exporting them, I want to add two lines of text, like "Report" and "Quarter." How do I do this? I've provided a screen shot of the template for what I want to do.

I'm getting the error that Error in if (file.exists(file) & !overwrite) { : the condition has length > 1 But there is also a problem even before that, where the output of the second lapply statement lapply(output_data, name_file will just output a list of 0's. So the issues kind of seem two fold and there is clearly something I am doing wrong.

library(openxlsx)

res <- list(mtcars, iris)
names(res) <- c('mtcars', 'iris')

some_fun = function(dat, vars){
  dat%>%
    group_by('vars')%>%
    summarize(count = n())
}

name_file = function(dat, file_name){
  wb <- createWorkbook()
  addWorksheet(wb, 'raw')
  writeData(wb, 'raw', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'raw', dat, startCol = 1, startRow = 3)
  
  addWorksheet(wb, 'processed')
  writeData(wb, 'processed', "CAR SALES REPORT:")
  writeData(wb, 'processed', dat, startCol = 1, startRow = 3)
  saveWorkbook(wb, file = paste0(file_name, '.xlsx'), overwrite = TRUE)
}

output_data = lapply(res, some_fun, 1)
lapply(output_data, name_file, names(res))

This goes beyond my first question, but let's say I actually have two separate outputs from some_fun, so it is actually

some_fun = function(dat, vars){
  dat1 <- dat%>%
    group_by('vars')%>%
    summarize(count = n())
  
  dat2 <- dat%>%
    group_by('vars')%>%
    mutate(count = n())
}

How would I then insert the output from a list of dat1 into the raw excel sheet and the output from the list of dat2 into the processed excel sheet, below:

name_file = function(dat, file_name){
  wb <- createWorkbook()
  addWorksheet(wb, 'raw')
  writeData(wb, 'raw', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'raw', dat1, startCol = 1, startRow = 3)
  
  addWorksheet(wb, 'processed')
  writeData(wb, 'processed', "CAR SALES REPORT:", startCol = 1, startRow = 1)
  writeData(wb, 'processed', dat2, startCol = 1, startRow = 3)
  saveWorkbook(wb, file = paste0(file_name, '.xlsx'), overwrite = TRUE)
}

Answer 1

In your updated code above you have two lists you must join. I've used mapply(SIMPLIFY = FALSE), but there are other ways to do this.

library(openxlsx2)

# don't pollute any folder
tmp <- paste0(tempdir(), "/fun_with_lists")
if (dir.exists(tmp)) unlink(tmp, recursive = TRUE)
dir.create(tmp)
setwd(tmp)

# create lists
res <- list(mtcars, iris)
names(res) <- c("mtcars", "iris")

# aggregate function
some_fun <- function(dat, vars) {
  dat %>%
    dplyr::group_by("vars") %>%
    dplyr::summarize(count = dplyr::n())
}

# xlsx output function
name_file <- function(dat) {
  raw       <- dat[["raw"]]
  processed <- dat[["processed"]]

  wb_workbook()$
    # raw worksheet
    add_worksheet("raw")$
    add_data(dims = "A1", x = "CAR SALES REPORT:")$
    add_data_table(dims = wb_dims(x = raw, from_row = 3), x = raw,
                   with_filter = FALSE)$
    add_named_style(name = "Title")$

    # processed worksheet
    add_worksheet("processed")$
    add_data(dims = "A1", x = "CAR SALES REPORT:")$
    add_data(dims = wb_dims(x = processed, from_row = 3), x = processed)$
    add_named_style(name = "Title")$

    # write file
    save(file = paste0(dat[["name"]], ".xlsx"))
}


output_data <- lapply(res, some_fun)

# assign output list
res2 <- mapply(
  x = res, y = output_data, z = names(res),
  function(x, y, z) {
    list(raw = x, processed = y, name = z)
  },
  SIMPLIFY = FALSE
)

# create output
lapply(res2, name_file) %>%
  invisible()

# check output

if (interactive()) xl_open(dir()[[1]])