setCellFormula-methods: Setting cell formulas

Description Usage Arguments Details Author(s) See Also Examples

Description

Sets cell formulas for specific cells in a workbook.

Usage

1
2
3
4
## 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 http://www.mirai-solutions.com

See Also

workbook, getCellFormula,

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 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)

Example output

Loading required package: XLConnectJars
OpenJDK 64-Bit Server VM warning: Can't detect initial thread stack location - find_vma failed
XLConnect 0.2-12 by Mirai Solutions GmbH [aut],
  Martin Studer [cre],
  The Apache Software Foundation [ctb, cph] (Apache POI, Apache Commons
    Codec),
  Stephen Colebourne [ctb, cph] (Joda-Time Java library),
  Graph Builder [ctb, cph] (Curvesapi Java library)
http://www.mirai-solutions.com ,
http://miraisolutions.wordpress.com
ZoneInfo: /usr/share/javazi/ZoneInfoMappings (Permission denied)
Warning message:
system call failed: Cannot allocate memory 

XLConnect documentation built on May 29, 2017, 7:04 p.m.