Setting cell formulas

Sets cell formulas for specific cells in a workbook.

# S4 method for class 'workbook,character'
setCellFormula(object,sheet,row,col,formula)
# S4 method for class '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

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

Examples

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