rexportopenxlsx

Exporting multiple tables to xlsx in R

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

Solution

  • 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:

    enter image description here

    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)