Add a data.frame to a sheet, allowing for different column styles.
Useful when constructing the spreadsheet from scratch.
addDataFrame(
x,
sheet,
col.names = TRUE,
row.names = TRUE,
startRow = 1,
startColumn = 1,
colStyle = NULL,
colnamesStyle = NULL,
rownamesStyle = NULL,
showNA = FALSE,
characterNA = "",
byrow = FALSE
)a data.frame.
a Sheet object.
a logical value indicating if the column names of x
are to be written along with x to the file.
a logical value indicating whether the row names of
x are to be written along with x to the file.
a numeric value for the starting row.
a numeric value for the starting column.
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.
a CellStyle object to customize the table
header.
a CellStyle object to customize the row
names (if row.names=TRUE).
a boolean value to control how NA's are displayed on the
sheet. If FALSE, NA values will be represented as blank cells.
a string value to control how character NA will be shown in the spreadsheet.
a logical value indicating if the data.frame should be added to the sheet in row wise fashion.
None. The modification to the workbook is done in place.
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.
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)