Ability to read all sheet formulas at once

[woodwards]

I want to read the values and formulas from a large Excel workbook into R. Currently formulas can only be read one cell at a time, and it is very slow. Is it possible to read a whole sheet of formulas at a time to make it faster? Thanks.

# excel crawler using XLConnect

# https://stackoverflow.com/questions/7963393/out-of-memory-error-java-when-using-r-and-xlconnect-package
options(java.parameters="-Xmx4g") # increase Java memory
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jdk-11.0.2') # for 64-bit version
library(tidyverse)
library(XLConnect)

# my separator (must be legal but not found in any wbname or wsname)
mysep <- "@@@@"

# workbook to import
wbname <- "Copy of NBO 2019 _Final_withGHG.xlsx" # 9Mb workbook

# add workbook
print(paste("Adding workbook", wbname))
stopifnot(str_detect(wbname, mysep)==FALSE)
wbo <- loadWorkbook(wbname, create=FALSE) # connection to workbook object
wsnames <- getSheets(wbo)
for (wsname in wsnames){
	print(paste("Adding worksheet", wsname))
	stopifnot(str_detect(wsname, mysep)==FALSE)
	value <- readWorksheet(wbo, wsname, header=FALSE, colTypes="character")
	formula <- value
	if (ncol(value)>0 && nrow(value)>0){
	  for (col in 1:ncol(value)){
		for (row in 1:nrow(value)){
			if (!is.na(value[row,col])){
				try(
					formula[row,col] <- getCellFormula(wbo, wsname, row, col),
					silent=TRUE
				)
			}
		}
	  }
        }
	fname <- paste0("temp/", wbname, mysep, wsname, ".rds")
	saveRDS(list(wbname=wbname, wsname=wsname, value=value, formula=formula),
			fname)
}