Functions to create and style (not read) a block of cells. Use it to set/update cell values and cell styles in an efficient manner.
CellBlock(sheet, startRow, startColumn, noRows, noColumns, create = TRUE)
is.CellBlock(cellBlock)
# S3 method for default
CellBlock(sheet, startRow, startColumn, noRows, noColumns, create = TRUE)
CB.setColData(
cellBlock,
x,
colIndex,
rowOffset = 0,
showNA = TRUE,
colStyle = NULL
)
CB.setRowData(
cellBlock,
x,
rowIndex,
colOffset = 0,
showNA = TRUE,
rowStyle = NULL
)
CB.setMatrixData(
cellBlock,
x,
startRow,
startColumn,
showNA = TRUE,
cellStyle = NULL
)
CB.setFill(cellBlock, fill, rowIndex, colIndex)
CB.setFont(cellBlock, font, rowIndex, colIndex)
CB.setBorder(cellBlock, border, rowIndex, colIndex)a Sheet object.
a numeric value for the starting row.
a numeric value for the starting column.
a numeric value to specify the number of rows for the block.
a numeric value to specify the number of columns for the block.
If TRUE cells will be created if they don't exist, if
FALSE only existing cells will be used. If cells don't exist (on a
new sheet for example), you have to use TRUE. On an existing sheet
with data, use TRUE if you want to blank out an existing cell block.
Use FALSE if you want to keep the styling of existing cells, but just
modify the value of the cell.
a cell block object as returned by CellBlock.
the data you want to add to the cell block, a vector or a matrix depending on the function.
a numeric vector specifiying the columns you want relative
to the startColumn.
a numeric value for the starting row.
a logical value. If set to FALSE, NA values will be
left as empty cells.
a CellStyle object used to style the column.
a numeric vector specifiying the rows you want relative to
the startRow.
a numeric value for the starting column.
a CellStyle object used to style the row.
a CellStyle object.
a Fill object, as returned by Fill.
a Font object, as returned by Font.
a Border object, as returned by Border.
For CellBlock a cell block object.
For CB.setColData, CB.setRowData, CB.setMatrixData,
CB.setFill, CB.setFont, CB.setBorder nothing as he
modification to the workbook is done in place.
Introduced in version 0.5.0 of the package, these functions speed up the
creation and styling of cells that are part of a "cell block" (a rectangular
shaped group of cells). Use the functions above if you want to create
efficiently a complex sheet with many styles. A simple by-column styling
can be done by directly using addDataFrame. With the
functionality provided here you can efficiently style individual cells, see
the example.
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".
If you try to set more data to the block than you have cells in the block, only the existing cells will be set.
Note that when modifying the style of a group of cells, the changes are made
to the pairs defined by (rowIndex, colIndex). This implies that the
length of rowIndex and colIndex are the same value. An
exception is made when either rowIndex or colIndex have length
one, when they will be expanded internally to match the length of the other
index.
Function CB.setMatrixData works for numeric or character matrices.
If the matrix x is not of numeric type it will be converted to a
character matrix.
# \donttest{
wb <- createWorkbook()
sheet <- createSheet(wb, sheetName="CellBlock")
cb <- CellBlock(sheet, 7, 3, 1000, 60)
CB.setColData(cb, 1:100, 1) # set a column
CB.setRowData(cb, 1:50, 1) # set a row
# add a matrix, and style it
cs <- CellStyle(wb) + DataFormat("#,##0.00")
x <- matrix(rnorm(900*45), nrow=900)
CB.setMatrixData(cb, x, 10, 4, cellStyle=cs)
# highlight the negative numbers in red
fill <- Fill(foregroundColor = "red", backgroundColor="red")
ind <- which(x < 0, arr.ind=TRUE)
CB.setFill(cb, fill, ind[,1]+9, ind[,2]+3) # note the indices offset
# set the border on the top row of the Cell Block
border <- Border(color="blue", position=c("TOP", "BOTTOM"),
pen=c("BORDER_THIN", "BORDER_THICK"))
CB.setBorder(cb, border, 1, 1:1000)
# Don't forget to save the workbook ...
# saveWorkbook(wb, file)
# }