R: how to add borders to multiple data frames?-CodePudding

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!

CodePudding user response：

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}