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