XLConnect (version 0.2-9)

setStyleAction-methods: Controlling application of cell styles when writing data to Excel

Description

Controls the application of cellstyles when writing data to Excel.

Usage

## S3 method for class 'workbook':
setStyleAction(object,type)

Arguments

object

The workbook to use

type

Defines the style action to be used when writing data (writeNamedRegion, writeWorksheet)

Details

The following style actions are supported:

• XLC$"STYLE_ACTION.XLCONNECT": This is the default.data.frameheaders (if specified to be written) are colored in solid light grey (25 percent). character, numeric and logical vectors are written using Excel's "General" data format. Time/date vectors e.g.DateorPOSIXt) are written with the "mm/dd/yyyy hh:mm:ss" data format. All cells are specified to wrap the text if necessary. The corresponding custom cell styles are calledXLConnect.Header,XLConnect.String,XLConnect.Numeric,XLConnect.BooleanandXLConnect.Date.
• XLC$"STYLE_ACTION.DATATYPE": This style action instructsXLConnectto applycellstyles per data type as set by thesetCellStyleForTypemethods. In contrast to theXLC$"STYLE_ACTION.DATA_FORMAT_ONLY"style action (see below) which only sets a data format to an existing cell style, this action actually sets a newcellstyle.
• XLC$"STYLE_ACTION.NONE": This style action instructsXLConnectto apply no cell styles when writing data. Cell styles are kept as they are. This is useful in a scenario where all styling is predefined in an Excel template which is then only filled with data.
• XLC$"STYLE_ACTION.PREDEFINED": This style action instructsXLConnectto use existing (predefined)cellstyles when writing headers and columns. This is useful in a template-based approach where an Excel template with predefinedcellstyles for headers and columns is available. Normally, this would be used when the column dimensions (and potentially also the row dimensions) of the data tables are known up-front and as such a layout and corresponding cell styles can be pre-specified. If adata.frameis written including its header, it is assumed that the Excel file being written to has predefinedcellstyles in the header row. Furthermore, the first row of data is assumed to contain the cell styles to be replicated for any additional rows. As such, this style action may only be useful if the same column cell style should be applied across all rows. Please refer to the available demos for some examples.
• XLC$"STYLE_ACTION.NAME_PREFIX": This style action instructsXLConnectto look for custom (named)cellstyles with a specified prefix when writing columns and headers. This style name prefix can be set via the methodsetStyleNamePrefix. For column headers, it first checks if there is a cell style named .Header.. If there is no such cell style, it checks for a cell style named .Header.. Again, if there is no such cell style, it checks for .Header (no specific column discrimination). As a final resort, it just takes the workbook default cell style. For columns,XLConnectfirst checks the availability of a cell style named .Column.. If there is no such cell style, it checks for .Column.. If again there is no such cell style, it checks for .Column. withbeing the corresponding data type from the set:{Numeric, String, Boolean, DateTime}. As a last resort, it would make use of the workbook's default cell style.
• XLC$"STYLE_ACTION.DATA_FORMAT_ONLY": This style action instructsXLConnectto only set the data format for a cell but not to apply any other styling but rather keep the existing one. The data format to apply is determined by the data type of the cell (which is in turn determined by the corresponding R data type). The data format for a specific type can be set via the methodsetDataFormatForType. The default data format is "General" for the data typesNumeric,StringandBooleanand is "mm/dd/yyyy hh:mm:ss" for the data typeDateTime.

See Also

workbook, cellstyle, createCellStyle, writeNamedRegion, writeWorksheet, setStyleNamePrefix, setCellStyleForType, setDataFormatForType

Examples

# Load workbook (create if not existing)
wb <- loadWorkbook("styleaction.xlsx", create = TRUE)

# Set style action to 'name prefix' 
setStyleAction(wb, XLC$"STYLE_ACTION.NAME_PREFIX")
# Set the name prefix to 'MyPersonalStyle'
setStyleNamePrefix(wb, "MyPersonalStyle")

# We now create a named cell style to be used for the header 
# (column names) of a data.frame
headerCellStyle <- createCellStyle(wb, 
                             name = "MyPersonalStyle.Header")

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

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

# Specify a thick black bottom border
setBorder(headerCellStyle, side = "bottom", 
          type = XLC$"BORDER.THICK", 
          color = XLC$"COLOR.BLACK")

# We now create a named cell style to be used for 
# the column named 'wt' (as you will see below, we will 
# write the built-in data.frame 'mtcars')
wtColumnCellStyle <- createCellStyle(wb, 
                           name = "MyPersonalStyle.Column.wt")

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

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

# We now create a named cell style to be used for 
# the 3rd column in the data.frame
wtColumnCellStyle <- createCellStyle(wb, 
                            name = "MyPersonalStyle.Column.3")

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

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

# 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!$A$1")

# Write built-in data set 'mtcars' to the above defined named region.
# The style action 'name prefix' will be used when writing the data
# as defined above.
writeNamedRegion(wb, mtcars, name = "mtcars")

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