R: how to add borders to multiple data frames?

Question

I have a problem with adding style to multiple dataframes in one Excel sheet. I want to draw borders separately for every table in one sheet.

I already have a code which automatically adds multiple data frames from a list:

library(openxlsx)
# Data
table1 <- data.frame("Num" = c(5,6,8,10), "Call" = c(1,2,3,4), "Name" = c("a", "b", "c", "d"), stringsAsFactors = FALSE)
table2 <- data.frame("Num" = c(8,1,11,54,3,5), "Call" = c(1,2,3,4,5,6), "Name" = c("f", "g", "h", "i", "j", "k"), "Age" = c(55,21,30,74,16,41), stringsAsFactors = FALSE)

df_list <- list(table1=table1, table2=table2)  


wb <- createWorkbook()
addWorksheet(wb, sheetName ="first")
s1 <- createStyle(border = "TopBottomLeftRight")
curr_row <- 1
curr_col <- 1
for(i in seq_along(df_list)) {
  writeData(wb, "first", names(df_list)[i], startCol = 1, startRow = curr_row)
  writeData(wb, "first", df_list[[i]], startCol = 1, startRow = curr_row+1, rowNames = TRUE)
  
  curr_row <- curr_row + nrow(df_list[[i]]) + 3
}
  
saveWorkbook(wb, paste0(Sys.Date()," Test_file (openxlsx)",".xlsx"))       

So a question is are there any solution by loop add style to every single dataframe? Or I have to write addStyle separately for every table?

Thanks!

Answer 1

So, if I get you right you want to have the borders around each table.

Attached you can find an example where there is a border around each table (table names exluded). I've also added an additional table3 to test the implementation. This procedure works for an arbitrary number of rows and columns.

library(openxlsx)
# Data
table1 <- data.frame("Num" = c(5,6,8,10), "Call" = c(1,2,3,4), "Name" = c("a", "b", "c", "d"), stringsAsFactors = FALSE)
table2 <- data.frame("Num" = c(8,1,11,54,3,5), "Call" = c(1,2,3,4,5,6), "Name" = c("f", "g", "h", "i", "j", "k"), "Age" = c(55,21,30,74,16,41), stringsAsFactors = FALSE)
table3 <- data.frame("Num" = c(8,1,11,54,3,5, 10, 10), "Call" = c(0, 0, 1,2,3,4,5,6), "Name" = c("a", "b", "f", "g", "h", "i", "j", "k"), "Age" = c(0, 0, 55,21,30,74,16,41),
                     "Test" = c(0, 0, 55,21,30,74,16,41), stringsAsFactors = FALSE)
df_list <- list(table1=table1, table2=table2, table3 = table3)  


wb <- createWorkbook()
addWorksheet(wb, sheetName ="first")
s1 <- createStyle(border = "TopBottomLeftRight")
curr_row <- 1
curr_col <- 1
for(i in seq_along(df_list)) {
  writeData(wb, "first", names(df_list)[i], startCol = 1, startRow = curr_row)
  writeData(wb, "first", df_list[[i]], startCol = 1, startRow = curr_row+1, rowNames = TRUE)
  addStyle(wb, sheet = "first", style = s1, rows = (curr_row+1):(nrow(df_list[[i]]) + (curr_row+1)), cols = 1:(1 + ncol(df_list[[i]])), gridExpand = TRUE)
  
  curr_row <- curr_row + nrow(df_list[[i]]) + 3
}

saveWorkbook(wb, paste0(Sys.Date()," Test_file (openxlsx)",".xlsx"))   

^{Created on 2022-05-09 by the reprex package (v2.0.1)}

Attached a screen.