write.xlsx {openxlsx}

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

See Also

addWorksheet()

writeData()

createStyle() for style parameters

buildWorkbook()

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)