openxlsx::writeFormula lets us write a character vector as an Excel formula, such as SUM(B2:B4). However, the package does not have a readFormula counterpart that lets us read an Excel formula as a character vector when reading from a workbook, receiving strings such as SUM(B2:B4). I've tried to check through the workbook object with no success of finding these strings. Any idea how to find the formulas in the workbook object and convert it to a string?
The other answer from Marco_CH works great and comes right out of the box, but the downside about using the xlsx package is that it requires Java. I did, however, eventually find a solution with openxlsx:
readFormula <- function(wb, sheet, row, col) {
require(dplyr)
if (!is.numeric(sheet)) {
sheet <- which(wb$sheet_names == sheet)
}
sheet_data <- wb[["worksheets"]][[sheet]][[".->sheet_data"]]
cell <- ((sheet_data$cols == col) & (sheet_data$rows == row))
formula <-
sheet_data$f[cell] %>%
stringr::str_remove("^<f>") %>%
stringr::str_remove("</f>$")
return(formula)
}