Previous chapter
Write Excel FilesFormulas with openxlsx
Next chapter

## Formulas with openxlsx

openxlsx also supports writing cell formulas in Excel. This functionality is supported in 3 ways by openxlsx:

1. As a character vector using `writeFormula()`
2. As a data.frame column with class “formula” using `writeData()`
3. As a vector with class “formula” using `writeData()`

We start with a simple workbook and the `iris` dataset:

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

This example stems from the `writeFormula` examples section. See also `?writeFormula`.

## 1. `writeFormula`

The `writeFormula` function can directly be used with a character vector:

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

## Class “formula” (vector)

Alternatively, the class of the character data type can be adjusted to `"formula"` and the formula is written 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)``````

## Class “formula” (data.frame)

The previous approach can also be extended to a `data.frame`. Here, all columns which contain formulas need to be re-classed to `"formula"`:

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

## Save workbook

Finally, the resulting workbook can be saved using `saveWorkbook()`:

``saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)``