xlsx: addDataFrame – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

addDataFrame

Add a data.frame to a sheet.

Description

Add a data.frame to a sheet, allowing for different column styles. Useful when constructing the spreadsheet from scratch.

Usage

addDataFrame(
  x,
  sheet,
  col.names = TRUE,
  row.names = TRUE,
  startRow = 1,
  startColumn = 1,
  colStyle = NULL,
  colnamesStyle = NULL,
  rownamesStyle = NULL,
  showNA = FALSE,
  characterNA = "",
  byrow = FALSE
)

Arguments

`x`	a `data.frame`.
`sheet`	a `Sheet` object.
`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.
`startRow`	a numeric value for the starting row.
`startColumn`	a numeric value for the starting column.
`colStyle`	a list of `CellStyle`. If the name of the list element is the column number, it will be used to set the style of the column. Columns of type `Date` and `POSIXct` are styled automatically even if `colSyle=NULL`.
`colnamesStyle`	a `CellStyle` object to customize the table header.
`rownamesStyle`	a `CellStyle` object to customize the row names (if `row.names=TRUE`).
`showNA`	a boolean value to control how NA's are displayed on the sheet. If `FALSE`, NA values will be represented as blank cells.
`characterNA`	a string value to control how character NA will be shown in the spreadsheet.
`byrow`	a logical value indicating if the data.frame should be added to the sheet in row wise fashion.

Details

Starting with version 0.5.0 this function uses the functionality provided by CellBlock which results in a significant improvement in performance compared with a cell by cell application of setCellValue and with other previous atempts.

It is difficult to treat NA's consistently between R and Excel via Java. Most likely, users of Excel will want to see NA's as blank cells. In R character NA's are simply characters, which for Excel means "NA".

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.

Value

None. The modification to the workbook is done in place.

Author(s)

Adrian Dragulescu

Examples

wb <- createWorkbook()
  sheet  <- createSheet(wb, sheetName="addDataFrame1")
  data <- data.frame(mon=month.abb[1:10], day=1:10, year=2000:2009,
    date=seq(as.Date("1999-01-01"), by="1 year", length.out=10),
    bool=c(TRUE, FALSE), log=log(1:10),
    rnorm=10000*rnorm(10),
    datetime=seq(as.POSIXct("2011-11-06 00:00:00", tz="GMT"), by="1 hour",
      length.out=10))
  cs1 <- CellStyle(wb) + Font(wb, isItalic=TRUE)           # rowcolumns
  cs2 <- CellStyle(wb) + Font(wb, color="blue")
  cs3 <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border()  # header
  addDataFrame(data, sheet, startRow=3, startColumn=2, colnamesStyle=cs3,
    rownamesStyle=cs1, colStyle=list(`2`=cs2, `3`=cs2))

  # to change the default date format use something like this
  # options(xlsx.date.format="dd MMM, yyyy")


  # Don't forget to save the workbook ...
  # saveWorkbook(wb, file)

xlsx

Read, Write, Format Excel 2007 and Excel 97/2000/XP/2003 Files

v0.6.5

GPL-3

Authors

Adrian Dragulescu [aut], Cole Arendt [aut, cre]

Initial release