openxlsx: read formula in cell as string-CodePudding

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?

CodePudding user response：

I think openxlsx doesn't have this option.

But you could use:

library(xlsx)

sheetname=1
xls_file = read.xlsx(file, sheetName=sheetName, header=TRUE, keepFormulas=TRUE)

Output:

> xls_file
   Project       Date Phase                       Test
1        A 2021-01-01  Init                        2*6
2        A 2021-04-10   P2O                        2*7
3        B 2021-02-01  Init                        2*6
4        B 1931-03-20   P2O       IF(1=1, TRUE, FALSE)
5        B 2021-04-01 Build                        2*6
6        B 2021-08-01   Doc                        2*7
7        C 2021-03-10  Init                        2*6
8        C 2021-03-31   P2O VLOOKUP(B9,H6:L15,2,FALSE)
9        C 2021-05-20 Build                        2*6
10       D 2021-01-30  Init                        2*7
11       D 2021-07-30   P2O                        2*6

Column Test is the one with the formulas:

CodePudding user response：

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