write data to an xlsx file

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

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)

See also

addWorksheet()

writeData()

createStyle() for style parameters

buildWorkbook()

Author

Alexander Walker, Jordan Mark Barbone

Examples

## write to working directory
options("openxlsx.borderColour" = "#4F80BD") ## set default border colour
if (FALSE) {
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"
)
if (FALSE) {
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))
if (FALSE) {
write.xlsx(l, "writeList1.xlsx", colWidths = c(NA, "auto", "auto"))
}

## different sheets can be given different parameters
if (FALSE) {
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
if (FALSE) {
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)))
}