setCellStyle-methods {XLConnect} | R Documentation |
Sets cell styles for specific cells in a workbook
.
## 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)
object |
The |
formula |
A formula specification in the form Sheet!B8:C17. Use either the argument |
sheet |
Name or index of the sheet the cell is on. Use either the argument |
row |
Row index of the cell to apply the cellstyle to. |
col |
Column index of the cell to apply the cellstyle to. |
cellstyle |
|
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
.
Martin Studer
Mirai Solutions GmbH http://www.mirai-solutions.com
workbook
,
cellstyle
,
createCellStyle
,
setDataFormat
,
setBorder
,
setFillBackgroundColor
,
setFillForegroundColor
,
setFillPattern
,
setWrapText
# 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)