Style entire columns and rows

[JanMarvin]

This SO asked and I was intrigued. We might provide a helper for this in the future.

The task

Create a workbook and colorize columns 4-6 in orange and rows 4-6 in blue.

# Create the colors. It looks like every colored row must start with a cell in the requested
# color otherwise the A column is empty for this row.
# Colored rows do not share this restriction. All we need is a single row in a style
wb <- wb_workbook() %>% 
  wb_add_worksheet() %>% 
  wb_add_fill(dims = "D1", color = wb_colour("orange")) %>% 
  wb_add_fill(dims = "A4:A6", color = wb_colour("blue"))

# get the styles 1 and 2 in our case
wb %>% wb_get_cell_style(dims = "D1")
wb %>% wb_get_cell_style(dims = "A4")

# apply style 1 (simply assign the xml string)
wb$worksheets[[1]]$cols_attr <- "<col min=\"4\" max=\"6\" width=\"8.83203125\" style=\"1\"/>"

# apply style 2 (extract, modify and insert) 
rows <- wb$worksheets[[1]]$sheet_data$row_attr
rows$customFormat[rows$r %in% c(4:6)] <- "1"
rows$s[rows$r %in% c(4:6)] <- "2"
wb$worksheets[[1]]$sheet_data$row_attr <- rows

# check the final result
wb$open()