write.xlsx: write data to an xlsx file
In openxlsx: Read, Write and Edit xlsx Files

write.xlsx

R Documentation

write data to an xlsx file

Description

write a data.frame or list of data.frames to an xlsx file

Usage

write.xlsx(x, file, asTable = FALSE, overwrite = TRUE, ...)

Arguments

`x`	A data.frame or a (named) list of objects that can be handled by `writeData()` or `writeDataTable()` to write to file
`file`	A file path to save the xlsx file
`asTable`	If `TRUE` will use `writeDataTable()` rather than `writeData()` to write `x` to the file (default: `FALSE`)
`overwrite`	Overwrite existing file (Defaults to `TRUE` as with `write.table`)
`...`	Additional arguments passed to `buildWorkbook()`; see details

Value

A workbook object

Optional Parameters

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)

Author(s)

Alexander Walker, Jordan Mark Barbone

Examples


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

openxlsx documentation built on Sept. 20, 2024, 5:08 p.m.