R Graphical Manual

Browse All

Last data update: 2014.03.03

R: Create and style a block of cells.

CellBlock

R Documentation

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


## Default S3 method:
CellBlock(sheet, startRow, startColumn, noRows, noColumns,
  create=TRUE)

is.CellBlock( cellBlock )

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.
`rowOffset`	a numeric value for the starting row.
`colOffset`	a numeric value for the starting column.
`showNA`	a logical value. If set to `FALSE`, NA values will be left as empty cells.
`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`.
`rowStyle`	a `CellStyle` object used to style the row.
`colStyle`	a `CellStyle` object used to style the column.
`cellStyle`	a `CellStyle` object.
`border`	a Border object, as returned by `Border`.
`fill`	a Fill object, as returned by `Fill`.
`font`	a Font object, as returned by `Font`.
`colIndex`	a numeric vector specifiying the columns you want relative to the `startColumn`.
`rowIndex`	a numeric vector specifiying the rows you want relative to the `startRow`.
`x`	the data you want to add to the cell block, a vector or a matrix depending on the function.

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.

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.

Author(s)

Adrian Dragulescu

Examples


  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:1000, 1)


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