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)

Mercedes

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)
)

Pellet

Answer 2

The mapply() in another answer slowed things down for my team (see note at end). We wrote this code and I wanted to share it with anyone else who is struggling. I'm also including some small helpers that made this easier for us to digest.

library(openxlsx)
`%T>%` <- magrittr::`%T>%` # pipe and update in place

df <- head(mtcars)

outside_borders <- function(wb, sheet, rows, cols) {
  border_style <- function(direction) {
    createStyle(
      border = direction,
      borderColour = "black",
      borderStyle = "medium"
    )
  }
  
  add_border <- function(direction, r = rows, c = cols) {
    addStyle(
      wb = wb, 
      sheet = sheet, 
      style = border_style(direction), 
      rows = r, 
      cols = c, 
      stack = TRUE
    )
  }
  
  add_border("Top",    r = min(rows))
  add_border("Bottom", r = max(rows))
  add_border("Left",   c = min(cols))
  add_border("Right",  c = max(cols))
}


# we used these helpers for final code
row_start <- 2
col_start <- 2

df_dim <-
  list(
    rows = row_start:(nrow(df) + row_start),
    cols = col_start:(ncol(df) + col_start - 1)
  )

# all together
wb <-
  createWorkbook() %T>%
  addWorksheet(sheetName = "Borders") %T>%
  writeDataTable(
    sheet = "Borders",
    x = df,
    tableStyle = "none",
    startCol = row_start,
    startRow = col_start
  ) %T>%
  outside_borders(
    sheet = "Borders",
    rows = df_dim$rows,
    cols = df_dim$cols
  )

openXL(wb)

Note about the tee-pipe:
Magrittr forward pipe fails to forward values into openXL::addWorksheet - "Error ...: First argument must be a Workbook"

Slowdown:
If there were 10 rows and 3 columns, the mapply() would run 26 iterations instead of just 4 we want (one call to "Top", one to "Bottom", etc). And if there were 1,000 rows, it would run 2,006 times (1:1000 twice and 1:3 twice)

Supercool