Write a a character vector containing Excel formula to a worksheet

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

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

Alexander Walker

`writeData`

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 | ```
## 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)
## Writing internal hyperlinks
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
addWorksheet(wb, "Sheet2")
writeFormula(wb, "Sheet1", x = '=HYPERLINK("#Sheet2!B3", "Text to Display - Link to Sheet2")')
saveWorkbook(wb, "writeFormulaHyperlinkExample.xlsx", overwrite = TRUE)
``` |

Questions? Problems? Suggestions? Tweet to @rdrrHQ or email at ian@mutexlabs.com.

All documentation is copyright its authors; we didn't write any of that.