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