How to apply thick border around a cell range using the `openxlsx` package in R

Question

I loaded an Excel workbook in R and want to do some formatting of the borders in a rectangular range of cells.

1. I would like to put a thin border between all cells
2. put a thick border around the outside of the range of the cells.

At the moment, I can only see the following approach (as started in the code below):

1. Add thin borders in the range
2. overwrite the left cells with left thick border and other thin borders
3. overwrite the right cells with right thick border and other thin borders
4. overwrite each corner cell separately with the correct borders

Is there an easier way of achieving this?

---

Edit 1:

If I use stack = TRUE in the second call I can skip the corners:

library(openxlsx)

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(
  wb = wb,
  sheetName = "Borders"
)

rangeRows = 2:5
rangeCols = 4:8

insideBorders <- openxlsx::createStyle(
  border = c("top", "bottom", "left", "right"),
  borderStyle = "thin"
)
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = insideBorders,
  rows = rangeRows,
  cols = rangeCols,
  gridExpand = TRUE
)

openxlsx::openXL(wb)

## left borders
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = openxlsx::createStyle(
    border = c("left"),
    borderStyle = c("thick")
  ),
  rows = rangeRows,
  cols = rangeCols[1],
  stack = TRUE,
  gridExpand = TRUE
)

##right borders
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = openxlsx::createStyle(
    border = c("right"),
    borderStyle = c("thick")
  ),
  rows = rangeRows,
  cols = tail(rangeCols, 1),
  stack = TRUE,
  gridExpand = TRUE
)

## top borders
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = openxlsx::createStyle(
    border = c("top"),
    borderStyle = c("thick")
  ),
  rows = rangeRows[1],
  cols = rangeCols,
  stack = TRUE,
  gridExpand = TRUE
)

##bottom borders
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = openxlsx::createStyle(
    border = c("bottom"),
    borderStyle = c("thick")
  ),
  rows = tail(rangeRows, 1),
  cols = rangeCols,
  stack = TRUE,
  gridExpand = TRUE
)

openxlsx::openXL(wb)

---

Original Code:

library(openxlsx)

wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(
  wb = wb,
  sheetName = "Borders"
)

rangeRows = 2:5
rangeCols = 4:8

insideBorders <- openxlsx::createStyle(
  border = c("top", "bottom", "left", "right"),
  borderStyle = "thin"
)
openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = insideBorders,
  rows = rangeRows,
  cols = rangeCols,
  gridExpand = TRUE
)

openxlsx::openXL(wb)

leftBorders <- openxlsx::createStyle(
  border = c("top", "bottom", "left", "right"),
  borderStyle = c("thin", "thin", "thick", "thin")
)

openxlsx::addStyle(
  wb = wb,
  sheet = "Borders",
  style = leftBorders,
  rows = rangeRows,
  cols = rangeCols[1],
  gridExpand = TRUE
)

openxlsx::openXL(wb)

Answer 1

I know this is an older question but in case anyone comes across this issue, here is a function that applies borders only to the outside of the rows and columns argument you pass:

OutsideBorders <-
  function(wb_,
           sheet_,
           rows_,
           cols_,
           border_col = "black",
           border_thickness = "medium") {
    left_col = min(cols_)
    right_col = max(cols_)
    top_row = min(rows_)
    bottom_row = max(rows_)
    
    sub_rows <- list(c(bottom_row:top_row),
                     c(bottom_row:top_row),
                     top_row,
                     bottom_row)
    
    sub_cols <- list(left_col,
                     right_col,
                     c(left_col:right_col),
                     c(left_col:right_col))
    
    directions <- list("Left", "Right", "Top", "Bottom")
    
    mapply(function(r_, c_, d) {
      temp_style <- createStyle(border = d,
                                borderColour = border_col,
                                borderStyle = border_thickness)
      addStyle(
        wb_,
        sheet_,
        style = temp_style,
        rows = r_,
        cols = c_,
        gridExpand = TRUE,
        stack = TRUE
      )
      
    }, sub_rows, sub_cols, directions)
  }

# Function call example
OutsideBorders(
  wb_,
  sheet_ = 1,
  rows_ = 1:nrow(test_sheet),
  cols_ = 1:ncol(test_sheet)
)