Though I try to not send data to individuals within an Excel file, sometimes my colleagues use Excel extensively in their own work, are adept at using Excel, and benefit from some of the formatting that is available for highlighting specific cells, columns, and rows within worksheets. For these reasons, I have put together a small function to encapsulate many of the nice formatting features provided by the XLConnect xlconnect@mirai-solutions.com package, which is available on CRAN.

The function add_formatted_worksheet is used on one worksheet at a time, but can add any number of formats to different cells within that worksheet

The following function is one of several that I have placed in a small package of utility functions that I frequently use when preparing reports and statistical analyses for colleagues. There is not anything fancy about it. It allows the user to provide the file name to be used, a list of data frames, a vector of sheet names, and whether or not a preexisting version of the file should be replaced.

All the real work is performed by the XLConnect xlconnect@mirai-solutions.com functions. Except for extremely wide columns, it successfully uses a built-in function of Excel’s to automatically set the column width to fit the contents.

The comments are in rmarkdown form. Enjoy.

#' \code{create_wkbk()} creates an Excel workbook with worksheets.
#' 
#' @param file filename of workbook to be created
#' @param df_list list of data frames to be added as worksheets to 
#'   workbook
#' @param sheetnames character vector of worksheet names
#' @param create Specifies if the file should be created if it does not 
#' already exist (default is FALSE). Note that create = TRUE has 
#' no effect if the specified file exists, i.e. an existing file is 
#' loaded and not being recreated if create = TRUE.
#' @import XLConnect
#' @export
create_wkbk <- function(file, df_list, sheetnames, create = TRUE) {
  if (length(df_list) != length(sheetnames))
    stop("Number of dataframes does not match number of worksheet names")
  
  if (file.exists(file) & create)
    file.remove(file)
  
  wkbk <- loadWorkbook(filename = file, create = create)
  for (i in seq_along(df_list)) {
    sheetname <- sheetnames[i]
    df <- df_list[[i]]
    createSheet(wkbk, sheetname)
    writeWorksheet(wkbk, df, sheetname, startRow = 1, startCol = 1, 
                   header = TRUE)
    setColumnWidth(wkbk, sheetname, column = 1:ncol(df), width = -1)
  }
  saveWorkbook(wkbk)
}