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