writexlsx.R

#' @name write.xlsx
#' @title write data to an xlsx file
#' @description write a data.frame or list of data.frames to an xlsx file
#' @author Alexander Walker, Jordan Mark Barbone
#' @inheritParams buildWorkbook
#' @param file A file path to save the xlsx file
#' @param overwrite If `TRUE` will save over `file` if present (default: `FALSE`)
#'
#' \itemize{
#'   \item{createWorkbook}
#'   \item{addWorksheet}
#'   \item{writeData}
#'   \item{freezePane}
#'   \item{saveWorkbook}
#' }
#'
#' see details.
#' @details Optional parameters are:
#'
#' \bold{createWorkbook Parameters}
#' \itemize{
#'   \item{\bold{creator}}{ A string specifying the workbook author}
#' }
#'
#' \bold{addWorksheet Parameters}
#' \itemize{
#'   \item{\bold{sheetName}}{ Name of the worksheet}
#'   \item{\bold{gridLines}}{ A logical. If \code{FALSE}, the worksheet grid lines will be hidden.}
#'   \item{\bold{tabColour}}{ Colour of the worksheet tab. A valid colour (belonging to colours())
#'   or a valid hex colour beginning with "#".}
#'   \item{\bold{zoom}}{ A numeric between 10 and 400. Worksheet zoom level as a percentage.}
#' }
#'
#' \bold{writeData/writeDataTable Parameters}
#' \itemize{
#'   \item{\bold{startCol}}{ A vector specifying the starting column(s) to write df}
#'   \item{\bold{startRow}}{ A vector specifying the starting row(s) to write df}
#'   \item{\bold{xy}}{ An alternative to specifying startCol and startRow individually.
#'  A vector of the form c(startCol, startRow)}
#'   \item{\bold{colNames or col.names}}{ If \code{TRUE}, column names of x are written.}
#'   \item{\bold{rowNames or row.names}}{ If \code{TRUE}, row names of x are written.}
#'   \item{\bold{headerStyle}}{ Custom style to apply to column names.}
#'   \item{\bold{borders}}{ Either "surrounding", "columns" or "rows" or NULL.  If "surrounding", a border is drawn around the
#' data.  If "rows", a surrounding border is drawn a border around each row. If "columns", a surrounding border is drawn with a border
#' between each column.  If "\code{all}" all cell borders are drawn.}
#'   \item{\bold{borderColour}}{ Colour of cell border}
#'   \item{\bold{borderStyle}}{ Border line style.}
#'   \item{\bold{keepNA}} {If \code{TRUE}, NA values are converted to #N/A (or \code{na.string}, if not NULL) in Excel, else NA cells will be empty. Defaults to FALSE.}
#'   \item{\bold{na.string}} {If not NULL, and if \code{keepNA} is \code{TRUE}, NA values are converted to this string in Excel. Defaults to NULL.}
#' }
#'
#' \bold{freezePane Parameters}
#' \itemize{
#'   \item{\bold{firstActiveRow}} {Top row of active region to freeze pane.}
#'   \item{\bold{firstActiveCol}} {Furthest left column of active region to freeze pane.}
#'   \item{\bold{firstRow}} {If \code{TRUE}, freezes the first row (equivalent to firstActiveRow = 2)}
#'   \item{\bold{firstCol}} {If \code{TRUE}, freezes the first column (equivalent to firstActiveCol = 2)}
#' }
#'
#' \bold{colWidths Parameters}
#' \itemize{
#'   \item{\bold{colWidths}} {May be a single value for all columns (or "auto"), or a list of vectors that will be recycled for each sheet (see examples)}
#' }
#'
#'
#' \bold{saveWorkbook Parameters}
#' \itemize{
#'   \item{\bold{overwrite}}{ Overwrite existing file (Defaults to TRUE as with write.table)}
#' }
#'
#'
#' columns of x with class Date or POSIXt are automatically
#' styled as dates and datetimes respectively.
#' @seealso \code{\link{addWorksheet}}
#' @seealso \code{\link{writeData}}
#' @seealso \code{\link{createStyle}} for style parameters
#' @seealso \code{\link{buildWorkbook}}
#' @return A workbook object
#' @examples
#'
#' ## write to working directory
#' options("openxlsx.borderColour" = "#4F80BD") ## set default border colour
#' \dontrun{
#' write.xlsx(iris, file = "writeXLSX1.xlsx", colNames = TRUE, borders = "columns")
#' write.xlsx(iris, file = "writeXLSX2.xlsx", colNames = TRUE, borders = "surrounding")
#' }
#'
#'
#' hs <- createStyle(
#'   textDecoration = "BOLD", fontColour = "#FFFFFF", fontSize = 12,
#'   fontName = "Arial Narrow", fgFill = "#4F80BD"
#' )
#' \dontrun{
#' write.xlsx(iris,
#'   file = "writeXLSX3.xlsx",
#'   colNames = TRUE, borders = "rows", headerStyle = hs
#' )
#' }
#'
#' ## Lists elements are written to individual worksheets, using list names as sheet names if available
#' l <- list("IRIS" = iris, "MTCATS" = mtcars, matrix(runif(1000), ncol = 5))
#' \dontrun{
#' write.xlsx(l, "writeList1.xlsx", colWidths = c(NA, "auto", "auto"))
#' }
#'
#' ## different sheets can be given different parameters
#' \dontrun{
#' write.xlsx(l, "writeList2.xlsx",
#'   startCol = c(1, 2, 3), startRow = 2,
#'   asTable = c(TRUE, TRUE, FALSE), withFilter = c(TRUE, FALSE, FALSE)
#' )
#' }
#'
#' # specify column widths for multiple sheets
#' \dontrun{
#' write.xlsx(l, "writeList2.xlsx", colWidths = 20)
#' write.xlsx(l, "writeList2.xlsx", colWidths = list(100, 200, 300))
#' write.xlsx(l, "writeList2.xlsx", colWidths = list(rep(10, 5), rep(8, 11), rep(5, 5)))
#' }
#'
#' @export
write.xlsx <- function(x, file, asTable = FALSE, overwrite = FALSE, ...) {
  wb <- buildWorkbook(x, asTable = asTable, ...)
  saveWorkbook(wb, file = file, overwrite = overwrite)
  invisible(wb)
}