setCellFormula-methods
From XLConnect v1.0.1
by Martin Studer
Setting cell formulas
Usage
# S4 method for workbook,character
setCellFormula(object,sheet,row,col,formula)
# S4 method for workbook,numeric
setCellFormula(object,sheet,row,col,formula)
Arguments
Details
Note that the arguments are vectorized such that multiple cells can be set with one method call.
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")
# }
Community examples
Looks like there are no examples yet.