setCellFormula-methods: Setting cell formulas

Description

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)

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

Author

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

Details

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

Examples

Run this code

if (FALSE) {
# 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")
}