setCellStyle-methods {XLConnect}

Setting cell styles

Description

Sets cell styles for specific cells in a workbook.

Usage

## S4 method for signature 'workbook,missing,character'
setCellStyle(object,formula,sheet,row,col,cellstyle)
## S4 method for signature 'workbook,missing,numeric'
setCellStyle(object,formula,sheet,row,col,cellstyle)
## S4 method for signature 'workbook,character,missing'
setCellStyle(object,formula,sheet,row,col,cellstyle)

Arguments

object

The workbook to use

formula

A formula specification in the form Sheet!B8:C17. Use either the argument formula or the combination of sheet, row and col.

sheet

Name or index of the sheet the cell is on. Use either the argument formula or the combination of sheet, row and col.

row

Row index of the cell to apply the cellstyle to.

col

Column index of the cell to apply the cellstyle to.

cellstyle

cellstyle to apply

Details

Sets the specified cellstyle for the specified cell (row, col) on the specified sheet or alternatively for the cells referred to by formula. Note that the arguments are vectorized such that multiple cells can be styled with one method call. Use either the argument formula or the combination of sheet, row and col.

Author(s)

Martin Studer
Mirai Solutions GmbH https://mirai-solutions.ch

See Also

workbook, cellstyle, createCellStyle, setDataFormat, setBorder,
setFillBackgroundColor, setFillForegroundColor, setFillPattern,
setWrapText

Examples

## Not run: 
# Load workbook (create if not existing)
wb <- loadWorkbook("setCellStyle.xlsx", create = TRUE)

# We don't set a specific style action in this demo, so the default 
# 'XLConnect' will be used (XLC$"STYLE_ACTION.XLCONNECT")

# Create a sheet named 'mtcars'
createSheet(wb, name = "mtcars")

# Create a named region called 'mtcars' referring to the sheet
# called 'mtcars'
createName(wb, name = "mtcars", formula = "mtcars!$C$4")

# Write built-in data set 'mtcars' to the above defined named region.
# This will use the default style action 'XLConnect'.
writeNamedRegion(wb, mtcars, name = "mtcars")

# Now let's color all weight cells of cars with a weight > 3.5 in red
# (mtcars$wt > 3.5)

# First, create a corresponding (named) cell style
heavyCar <- createCellStyle(wb, name = "HeavyCar")

# Specify the cell style to use a solid foreground color
setFillPattern(heavyCar, fill = XLC$"FILL.SOLID_FOREGROUND")

# Specify the foreground color to be used
setFillForegroundColor(heavyCar, color = XLC$"COLOR.RED")

# Which cars have a weight > 3.5 ?
rowIndex <- which(mtcars$wt > 3.5)

# NOTE: The mtcars data.frame has been written offset with
# top left cell C4 - and we have also written a header row!
# So, let's take that into account appropriately. Obviously,
# the two steps could be combined directly into one ...
rowIndex <- rowIndex + 4

# The same holds for the column index
colIndex <- which(names(mtcars) == "wt") + 2

# Set the 'HeavyCar' cell style for the corresponding cells.
# Note: the row and col arguments are vectorized!
setCellStyle(wb, sheet = "mtcars", row = rowIndex, col = colIndex, 
             cellstyle = heavyCar)

# Save workbook (this actually writes the file to disk)
saveWorkbook(wb)

# clean up 
file.remove("setCellStyle.xlsx")

## End(Not run)