How to style tables in a workbook

[JanMarvin]

Another question from SO:

The OP wanted to style a workbook in a special corporate template. A solution using openxlsx2 can be found below.

library(openxlsx2)

in_xlsx <- temp_xlsx()
out_xlsx <- temp_xlsx()

# create example data
wb <- wb_workbook() %>%
  wb_add_worksheet() %>%
  wb_add_data(x = mtcars) %>% # A1:K33
  wb_add_worksheet() %>%
  wb_add_data(x = iris) %>% 
  wb_save(in_xlsx)

# colloring the tables in an unknown workbook
wb <- wb_load(in_xlsx)
for (i in seq_along(wb$worksheets)) {
  
  # get columns for our data
  dims <- wb %>% wb_data(sheet = i) %>% attr("dims")
  TO <- int2col(ncol(dims))
  ROW <- nrow(dims)
  
  # assume that they all begin at A1 and have a single header row
  header_dim <- sprintf("A1:%s1", TO)
  body_dim   <- sprintf("A2:%s%s", TO, ROW)
  
  wb <- wb %>% 
    # style the header
    wb_add_fill(sheet = i, dims = header_dim, color = wb_color("#85BC22")) %>% 
    wb_add_font(sheet = i, dims = header_dim, color = wb_color("white"),
                size = "10", name = "Segoe Ui") %>% 
    wb_add_border(sheet = i, dims = header_dim,
                  top_color = wb_color("white"), bottom_color = wb_color("white"),
                  left_color = wb_color("white"), right_color = wb_color("white"),
                  inner_vcolor = wb_color("white"),
                  top_border = "thin", bottom_border = "thin",
                  left_border = "thin", right_border = "thin",
                  inner_vgrid = "thin") %>% 
    # fill the background of every second row gray
    wb_add_fill(sheet = i, dims = body_dim, color = wb_color("#D9D9D9"),
                every_nth_row = 2) %>% 
    # set fonts and border for body
    wb_add_font(sheet = i, dims = body_dim,
                size = "10", name = "Segoe Ui") %>% 
    wb_add_border(sheet = i, dims = body_dim,
                  top_color = wb_color("white"), bottom_color = wb_color("white"),
                  left_color = wb_color("white"), right_color = wb_color("white"),
                  inner_vcolor = wb_color("white"),
                  top_border = "thin", bottom_border = "thin",
                  left_border = "thin", right_border = "thin",
                  inner_vgrid = "thin")
}

wb %>% wb_save(out_xlsx)