Write a character vector as an Excel Formula

Share:

Description

Write a a character vector containing Excel formula to a worksheet

Usage

1
writeFormula(wb, sheet, x, startCol = 1, startRow = 1, xy = NULL)

Arguments

wb

A Workbook object containing a worksheet.

sheet

The worksheet to write to. Can be the worksheet index or name.

x

A character vector.

startCol

A vector specifiying the starting column to write to.

startRow

A vector specifiying the starting row to write to.

xy

An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow).

Author(s)

Alexander Walker

See Also

writeData

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
30
31
32
33
34
35
36
37
38
39
40
## There are 3 ways to write a formula

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", x = iris)

## SEE int2col() to convert int to Excel column label

## 1. -  As a character vector using writeFormula

v <- c("SUM(A2:A151)", "AVERAGE(B2:B151)") ## skip header row
writeFormula(wb, sheet = 1, x = v, startCol = 10, startRow = 2)
writeFormula(wb, 1, x = "A2 + B2", startCol = 10, startRow = 10)


## 2. - As a data.frame column with class "formula" using writeData

df <- data.frame(x=1:3,
                 y = 1:3,
                 z = paste(paste0("A", 1:3+1L), paste0("B", 1:3+1L), sep = " + "),
                 z2 = sprintf("ADDRESS(1,%s)", 1:3),
                 stringsAsFactors = FALSE)

class(df$z) <- c(class(df$z), "formula")
class(df$z2) <- c(class(df$z2), "formula")

addWorksheet(wb, "Sheet 2")
writeData(wb, sheet = 2, x = df)



## 3. - As a vector with class "formula" using writeData

v2 <- c("SUM(A2:A4)", "AVERAGE(B2:B4)", "MEDIAN(C2:C4)")
class(v2) <- c(class(v2), "formula")

writeData(wb, sheet = 2, x = v2, startCol = 10, startRow = 2)

## Save workbook
saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)

Want to suggest features or report bugs for rdrr.io? Use the GitHub issue tracker.