XLConnect (version 1.0.7)

createCellStyle-methods: Creating custom named and anonymous cell styles

Description

Creates a custom named or anonymous cellstyle.

Usage

# S4 method for workbook,character
createCellStyle(object,name)

Arguments

object

The workbook to use

name

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

Author

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

Details

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.

See Also

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

Examples

if (FALSE) {
# 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)
saveWorkbook(wb)

# clean up 
file.remove("createCellstyles.xlsx")
}