xlsx (version 0.6.5)

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.

Value

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

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.

Examples

# NOT RUN {

  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)

# }

Run the code above in your browser using DataCamp Workspace