formatting tables in xlsx files

Question

I am trying to create function for formatting every tables in xlsx file.

I want to save N numbers of Tables in xlsx and formatting all the tables in xlsx file. but its formatting the first table only.

library(openxlsx)
format_tbl <- function(workbook,tbll){
  setColWidths(workbook, 1,cols = 1:ncol(tbll), widths = "auto")
  writeData(workbook, 1, tbll)
  
  header_style <- createStyle(fgFill = "#009DE0", halign = "center", textDecoration = "bold", fontColour = "white")
  addStyle(workbook, 1, style = header_style, rows = 1, cols = 1:ncol(tbll))
  
  name_style <- createStyle(fgFill = "gray70", halign = "center", fontColour = "white")
  addStyle(workbook, 1, style = name_style, rows = 2 : nrow(tbll), cols = 1)
  
  percent_style <- createStyle(halign = "center", numFmt = "00%")
  addStyle(workbook, 1, style = percent_style, rows = 2 : nrow(tbll), which(colnames(tbll) == "disp"))
  
  center_style <- createStyle(halign = "center")
  addStyle(workbook, 1, style = center_style, rows = 2 : nrow(tbll), cols = which(!colnames(tbll) %in% c("model", "disp")), gridExpand = TRUE)
  
  total_style <- createStyle(fgFill = "#009DE0", halign = "center", fontColour = "black", fontSize = 12)
  addStyle(workbook, 1, style = total_style, rows = nrow(tbll), cols = 1:ncol(tbll))
  
  Na_style <- createStyle(fgFill = "#00968F", halign = "center", fontColour = "black", fontSize = 12)
  addStyle(workbook, 1, style = Na_style, rows = nrow(tbll)+1, cols = 1:ncol(tbll))
  
}

t1 = mtcars
t2 = mtcars[,1:5]
t3= iris
t4 = iris[1:8,] 

format_tbl(workbook=wb,tbll=t1)
format_tbl(workbook=wb,tbll=t2)
format_tbl(workbook=wb,tbll=t3)
format_tbl(workbook=wb,tbll=t4)

tbls <- list(t1,t2,t3,t4)



startRows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1

fn <- tempfile(fileext = "xlsx")

wb <- createWorkbook()
addWorksheet(wb, "tbls")
mapply(function(tbl, startRow) writeData(wb, "tbls", x = tbl, startRow = startRow), tbls, startRows)

saveWorkbook(wb, fn, overwrite = TRUE) %>% file.show(.)

Answer 1

Here is a function that formats each element of the list of data frames. With R scoping rules what they are, notice format_tbls returns a workbook object, which allows the updates that occur inside the function to be passed to the object that is eventually saved to a file.

Note: I attempted to follow the formatting shown in the original question, but I have excluded the 'Na_string' addStyle call. This probably was intended to highlight NA's in the data.frames. The current code does not do that.

library(openxlsx)
library(expss)

# make list of tables
tbls <- list( mtcars,mtcars[,1:5],iris,iris[1:8,])

# function that formats each table in a list
format_tbls <- function(tbls, wb){
  
  # add worksheet
  addWorksheet(wb, "tbls")
  
  # calculate start rows
  rows <- c(0, cumsum(2 + sapply(tbls, nrow)[-length(tbls)])) + 1
  
  # styles
  header_style <- createStyle(fgFill = "#009DE0", halign = "center", textDecoration = "bold", fontColour = "white")
  name_style <- createStyle(fgFill = "gray70", halign = "center", fontColour = "white")
  percent_style <- createStyle(halign = "center", numFmt = "00%")
  center_style <- createStyle(halign = "center")
  total_style <- createStyle(fgFill = "#009DE0", halign = "center", fontColour = "black", fontSize = 12)
  Na_style <- createStyle(fgFill = "#00968F", halign = "center", fontColour = "black", fontSize = 12)
  
  setColWidths(wb, 1,cols = 1:100, widths = "auto")
  
  for(i in seq_along(tbls)){
    
    #writeData(wb, 1, tbls[[i]], startRow = rows[i]) # this works
    expss::xl_write(tbls[[i]], wb, "tbls", row = rows[i]) # this also works
     
    addStyle(wb, 1, style = total_style, rows = rows[i]:(rows[i]+nrow(tbls[[i]])), 
             cols = 1:ncol(tbls[[i]]), gridExpand = TRUE) # set 
    
    addStyle(wb, 1, style = header_style, 
             rows = rows[i], cols = 1:ncol(tbls[[i]]))
    
    addStyle(wb, 1, style = name_style, 
             rows = rows[i]+1, cols = 1)
    
    addStyle(wb, 1, style = percent_style, rows = rows[i]+1, 
               which(colnames(tbls[[i]]) == "disp"))
    
    addStyle(wb, 1, style = center_style, rows = rows[i]+1, 
             cols = which(!colnames(tbls[i]) %in% c("model", "disp")), gridExpand = TRUE)
    
    # not included. probably needs conditional formatting to detect NA values?
    #addStyle(wb, 1, style = Na_style, rows = rows[i]+1:(rows[i]+nrow(tbls[[i]])), 
             #cols = 1:ncol(tbls[[i]]), gridExpand = TRUE) # set stack = TRUE to allow styles to be merged
  }
  
  return(wb)
  
}

# create wb
wb <- createWorkbook()

# use function
wb <- format_tbls(tbls = tbls, wb = wb)

# save to file
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)