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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/writeData.R
\name{writeFormula}
\alias{writeFormula}
\title{Write a character vector as an Excel Formula}
\usage{
writeFormula(
wb,
sheet,
x,
startCol = 1,
startRow = 1,
array = FALSE,
xy = NULL
)
}
\arguments{
\item{wb}{A Workbook object containing a worksheet.}
\item{sheet}{The worksheet to write to. Can be the worksheet index or name.}
\item{x}{A character vector.}
\item{startCol}{A vector specifying the starting column to write to.}
\item{startRow}{A vector specifying the starting row to write to.}
\item{array}{A bool if the function written is of type array}
\item{xy}{An alternative to specifying \code{startCol} and
\code{startRow} individually. A vector of the form
\code{c(startCol, startRow)}.}
}
\description{
Write a a character vector containing Excel formula to a worksheet.
}
\details{
Currently only the english version of functions are supported. Please don't use the local translation.
The examples below show a small list of possible formulas:
\itemize{
\item{SUM(B2:B4)}
\item{AVERAGE(B2:B4)}
\item{MIN(B2:B4)}
\item{MAX(B2:B4)}
\item{...}
}
}
\examples{
## 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
\dontrun{
saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)
}
## 4. - Writing internal hyperlinks
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
addWorksheet(wb, "Sheet2")
writeFormula(wb, "Sheet1", x = '=HYPERLINK("#Sheet2!B3", "Text to Display - Link to Sheet2")')
## Save workbook
\dontrun{
saveWorkbook(wb, "writeFormulaHyperlinkExample.xlsx", overwrite = TRUE)
}
}
\seealso{
\code{\link[=writeData]{writeData()}} \code{\link[=makeHyperlinkString]{makeHyperlinkString()}}
}
\author{
Alexander Walker
}
|
