xlsx (version 0.6.5)

CellBlock: Create and style a block of cells.

Description

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.

Usage

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)

Arguments

sheet

a Sheet object.

startRow

a numeric value for the starting row.

startColumn

a numeric value for the starting column.

noRows

a numeric value to specify the number of rows for the block.

noColumns

a numeric value to specify the number of columns for the block.

create

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.

cellBlock

a cell block object as returned by CellBlock.

x

the data you want to add to the cell block, a vector or a matrix depending on the function.

colIndex

a numeric vector specifiying the columns you want relative to the startColumn.

rowOffset

a numeric value for the starting row.

showNA

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

colStyle

a CellStyle object used to style the column.

rowIndex

a numeric vector specifiying the rows you want relative to the startRow.

colOffset

a numeric value for the starting column.

rowStyle

a CellStyle object used to style the row.

cellStyle

a CellStyle object.

fill

a Fill object, as returned by Fill.

font

a Font object, as returned by Font.

border

a Border object, as returned by Border.

Value

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.

Details

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.

Examples

# NOT RUN {
# }
# NOT RUN {
  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)
# }
# NOT RUN {
# }