setCellFormula-methods

Setting cell formulas

Sets cell formulas for specific cells in a '>workbook.

Usage

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

Details

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

See Also

'>workbook, getCellFormula,

Aliases

• setCellFormula
• setCellFormula-methods
• setCellFormula,workbook,character-method
• setCellFormula,workbook,numeric-method

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")
# }