setCellFormula-methods {XLConnect}

Setting cell formulas

Description

Sets cell formulas for specific cells in a workbook.

Usage

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

Arguments

object

The workbook to use

sheet

Name or index of the sheet the cell is on

row

Row index of the cell to edit

col

Column index of the cell to edit

formula

The formula to apply to the cell, without the initial = character used in Excel

Details

Note that the arguments are vectorized such that multiple cells can be set with one method call.

Author(s)

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

See Also

workbook, getCellFormula,

Examples

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

# 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.
writeNamedRegion(wb, mtcars, name = "mtcars")

# Now, let us get Excel to calculate average weights.
# Where did we write the dataset?
corners <- getReferenceCoordinatesForName(wb, "mtcars")
# Put the average under the wt column
colIndex <- which(names(mtcars) == "wt") 
rowIndex <- corners[2,1] + 1

# Construct the input range & formula
input <- paste(idx2cref(c(corners[1,1], colIndex, 
                          corners[2,1], colIndex)), collapse=":")
formula <- paste("AVERAGE(", input, ")", sep="") 
            
setCellFormula(wb, "mtcars", rowIndex, colIndex, formula)

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

# clean up 
file.remove("setCellFormula.xls")

## End(Not run)