Write a data.frame to an Excel workbook.

write.xlsx(
  x,
  file,
  sheetName = "Sheet1",
  col.names = TRUE,
  row.names = TRUE,
  append = FALSE,
  showNA = TRUE,
  password = NULL
)

write.xlsx2(
  x,
  file,
  sheetName = "Sheet1",
  col.names = TRUE,
  row.names = TRUE,
  append = FALSE,
  password = NULL,
  ...
)

Arguments

x

a data.frame to write to the workbook.

file

the path to the output file.

sheetName

a character string with the sheet name.

col.names

a logical value indicating if the column names of x are to be written along with x to the file.

row.names

a logical value indicating whether the row names of x are to be written along with x to the file.

append

a logical value indicating if x should be appended to an existing file. If TRUE the file is read from disk.

showNA

a logical value. If set to FALSE, NA values will be left as empty cells.

password

a String with the password.

...

other arguments to addDataFrame in the case of read.xlsx2.

Details

This function provides a high level API for writing a data.frame to an Excel 2007 worksheet. It calls several low level functions in the process. Its goal is to provide the conveniency of write.csv by borrowing from its signature.

Internally, write.xlsx uses a double loop in over all the elements of the data.frame so performance for very large data.frame may be an issue. Please report if you experience slow performance. Dates and POSIXct classes are formatted separately after the insertion. This also adds to processing time.

If x is not a data.frame it will be converted to one.

Function write.xlsx2 uses addDataFrame which speeds up the execution compared to write.xlsx by an order of magnitude for large spreadsheets (with more than 100,000 cells).

The default formats for Date and DateTime columns can be changed via the two package options xlsx.date.format and xlsx.datetime.format. They need to be specified in Java date format https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.

Writing of password protected workbooks is supported for Excel 2007 OOXML format only. Note that in Linux, LibreOffice is not able to read password protected spreadsheets.

See also

read.xlsx for reading xlsx documents. See also addDataFrame for writing a data.frame to a sheet.

Author

Adrian Dragulescu

Examples


if (FALSE) {

file <- paste(tempdir(), "/usarrests.xlsx", sep="")
res <- write.xlsx(USArrests, file)

# to change the default date format
oldOpt <- options()
options(xlsx.date.format="dd MMM, yyyy")
write.xlsx(x, sheet) # where x is a data.frame with a Date column.
options(oldOpt)      # revert back to defaults

}