createCellStyle-methods: Creating custom named and anonymous cell styles

createCellStyle-methodsR Documentation

Creating custom named and anonymous cell styles


Creates a custom named or anonymous cellstyle.


## S4 method for signature 'workbook,character'



The workbook to use


The name of the new cellstyle to create. Omit to create an anonymous cellstyle.


Creates a named cellstyle with the specified name. Named cell styles may be used in conjunction with the name prefix style action (see setStyleAction) or may also be used directly with the method setCellStyle. Named cell styles can easily be changed from within Excel using the cell styles menu.

If name is missing, an anonymous cell style is created. Anonymous cell styles can be used in conjunction with the setCellStyle method.


Martin Studer
Mirai Solutions GmbH

See Also

workbook, cellstyle, getOrCreateCellStyle, existsCellStyle, setStyleAction, setStyleNamePrefix, setCellStyle, setDataFormat, setBorder, setFillBackgroundColor, setFillForegroundColor, setFillPattern, setWrapText


## Not run: 
# Load workbook (create if not existing)
wb <- loadWorkbook("createCellstyles.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)

# clean up 

## End(Not run)

XLConnect documentation built on Feb. 16, 2023, 5:53 p.m.