setCellFormula-methods

0th

Percentile

Setting cell formulas

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

Keywords
methods, utilities
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

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)
# }
Documentation reproduced from package XLConnect, version 0.2-14, License: GPL-3

Community examples

Looks like there are no examples yet.