| write.xlsx {openxlsx} | R Documentation |
write a data.frame or list of data.frames to an xlsx file
write.xlsx(x, file, asTable = FALSE, overwrite = FALSE, ...)
x |
A data.frame or a (named) list of objects that can be handled by
|
file |
A file path to save the xlsx file |
asTable |
If |
overwrite |
If 'TRUE' will save over 'file' if present (default: 'FALSE')
see details. |
... |
Additional arguments passed to |
Optional parameters are:
createWorkbook Parameters
creator A string specifying the workbook author
addWorksheet Parameters
sheetName Name of the worksheet
gridLines A logical. If FALSE, the worksheet grid lines will be hidden.
tabColour Colour of the worksheet tab. A valid colour (belonging to colours()) or a valid hex colour beginning with "#".
zoom A numeric between 10 and 400. Worksheet zoom level as a percentage.
writeData/writeDataTable Parameters
startCol A vector specifying the starting column(s) to write df
startRow A vector specifying the starting row(s) to write df
xy An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow)
colNames or col.names If TRUE, column names of x are written.
rowNames or row.names If TRUE, row names of x are written.
headerStyle Custom style to apply to column names.
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 "all" all cell borders are drawn.
borderColour Colour of cell border
borderStyle Border line style.
keepNA If TRUE, NA values are converted to #N/A (or na.string, if not NULL) in Excel, else NA cells will be empty. Defaults to FALSE.
na.string If not NULL, and if keepNA is TRUE, NA values are converted to this string in Excel. Defaults to NULL.
freezePane Parameters
firstActiveRow Top row of active region to freeze pane.
firstActiveCol Furthest left column of active region to freeze pane.
firstRow If TRUE, freezes the first row (equivalent to firstActiveRow = 2)
firstCol If TRUE, freezes the first column (equivalent to firstActiveCol = 2)
colWidths Parameters
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)
saveWorkbook Parameters
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.
A workbook object
Alexander Walker, Jordan Mark Barbone
createStyle for style parameters
## write to working directory
options("openxlsx.borderColour" = "#4F80BD") ## set default border colour
## Not run:
write.xlsx(iris, file = "writeXLSX1.xlsx", colNames = TRUE, borders = "columns")
write.xlsx(iris, file = "writeXLSX2.xlsx", colNames = TRUE, borders = "surrounding")
## End(Not run)
hs <- createStyle(
textDecoration = "BOLD", fontColour = "#FFFFFF", fontSize = 12,
fontName = "Arial Narrow", fgFill = "#4F80BD"
)
## Not run:
write.xlsx(iris,
file = "writeXLSX3.xlsx",
colNames = TRUE, borders = "rows", headerStyle = hs
)
## End(Not run)
## 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))
## Not run:
write.xlsx(l, "writeList1.xlsx", colWidths = c(NA, "auto", "auto"))
## End(Not run)
## different sheets can be given different parameters
## Not run:
write.xlsx(l, "writeList2.xlsx",
startCol = c(1, 2, 3), startRow = 2,
asTable = c(TRUE, TRUE, FALSE), withFilter = c(TRUE, FALSE, FALSE)
)
## End(Not run)
# specify column widths for multiple sheets
## Not run:
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)))
## End(Not run)