Exporting multiple tables to xlsx in R

Question

I am trying to export a set of tables to an excel sheet. However I am using a for loop to create different sheets which means that in some cases not all of the tables exist. Does anyone know how to export it such that when the tables exist they print on the excel sheet one after the other?

At the moment this is what I have for my code, however the issue is if for example pnt_h, mid_h, lc3_h don't exist then rc3_h is printed on row 27 which leaves a big space between row 1 where res_h is printed. Ideally I would have res_h printed from row 1 and then rc3_h is printed 1 row below res_h depending on how many rows res_h has (note that res_h will have differing number of rows for each iteration of the loop).

addWorksheet(OUT, "Summary")

writeData(OUT, sheet = "Summary", x = home_matchups, startCol = 1, startRow = 1)

if(exists("res_h")){ writeData(OUT, sheet = "Summary", x = res_h, startCol = 1, startRow = 1) }
if(exists("pnt_h")){ writeData(OUT, sheet = "Summary", x = pnt_h, startCol = 1, startRow = 5) }
if(exists("mid_h")){ writeData(OUT, sheet = "Summary", x = mid_h, startCol = 1, startRow = 9) }
if(exists("lc3_h")){ writeData(OUT, sheet = "Summary", x = lc3_h, startCol = 1, startRow = 13) }
if(exists("rc3_h")){ writeData(OUT, sheet = "Summary", x = rc3_h, startCol = 1, startRow = 27) }

Note I have simplified this code so it is easier to read so the above will not appear as though I am using a loop but I am. Further, as there is no guarantee that "res_h" exists I cant do the following as the second line

if(exists("pnt_h")){ writeData(OUT, sheet = "Summary", x = pnt_h, startCol = 1, startRow = nrow(res_h) + 3 ) }

Apologies if any of this is not clear

Answer 1

The following solution avoids having to test for existence.

• ls/mget will create list with all data sets ending in "_h";
• then, cumsum computes a vector of starting rows. (It starts at row 1 and the last df doesn't count);
• the data list and the start rows vector are passed to a Map loop that writes to a sheet of an existing workbook.

Finally the workbook is saved to file.

Note: data example from Edward's answer, slightly edited.

set.seed(1)
res_h <- as.data.frame(matrix(rnorm(20), 4, 5))
mid_h <- as.data.frame(matrix(rnorm(20), 4, 5))

library(openxlsx)

# this is probably where the data exist, change if not
env <- .GlobalEnv
# find all data sets whose names end in '_h'
df_h <- ls(pattern = "_h$", envir = env)
# create a list of all these df's
df_list <- mget(df_h, envir = env)
# check that all df's are in 'df_list'
names(df_list)
#> [1] "mid_h" "res_h"

start_row <- c(1, sapply(df_list[-length(df_list)], nrow)) |> cumsum()

OUT <- createWorkbook()
addWorksheet(OUT, "Summary")

Map(\(x, sr) {
  writeData(OUT, sheet = "Summary", x = x, startCol = 1L, startRow = sr)
}, df_list, start_row)
#> $mid_h
#> [1] 0
#> 
#> $res_h
#> [1] 0

saveWorkbook(OUT, "writeDataExample.xlsx", overwrite = TRUE)

^{Created on 2024-10-23 with reprex v2.1.1}

Answer 2

Next time, please provide some sample data.

You can include, in each block of code, a parameter called start_row that gets updated if the table exists. Then use that for the following startRow argument. For example,

library(openxlsx)

OUT <- createWorkbook()
addWorksheet(OUT, "Summary")

writeData(OUT, sheet = "Summary", x=NULL, startCol = 1, startRow = 1)

set.seed(1)
res_h <- data.frame(matrix(rnorm(20), 4, 5))
mid_h <- data.frame(matrix(rnorm(20), 4, 5))

if(exists("res_h")){ 
  writeData(OUT, sheet = "Summary", x = res_h, startCol = 1, startRow = 1) 
  start_row <- nrow(res_h) + 2
  }

if(exists("pnt_h")){ 
  writeData(OUT, sheet = "Summary", x = pnt_h, startCol = 1, startRow = start_row) 
  start_row <- start_row + nrow(pnt_h) + 2
}

if(exists("mid_h")){ 
  writeData(OUT, sheet = "Summary", x = mid_h, startCol = 1, startRow = start_row) 
  start_row <- start_row + nrow(mid_h) + 2
}

saveWorkbook(OUT, "writeDataExample.xlsx", overwrite = TRUE)

Would create the following Excel file:

And all your code blocks could be simplified using iteration and a vector of table names.

tables <- c("res_h", "pnt_h", "mid_h")

start_row <- 1

for(x in tables) {
  if(!is.null(dat <- get0(x))) { 
    writeData(OUT, sheet = "Summary", x = dat, startCol = 1, startRow = start_row) 
    start_row <- nrow(dat) + 2
  }
}

saveWorkbook(OUT, "writeDataExample.xlsx", overwrite = TRUE)