setCellFormula-methods: Setting cell formulas

In XLConnect: Excel Connector for R

Setting cell formulas

Description

Sets cell formulas for specific cells in a workbook.

Usage

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

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

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

## End(Not run)

XLConnect documentation built on Feb. 16, 2023, 5:53 p.m.