openxlsx not able to read from .xlsx file in R

Question

I am trying to read value from a .xlsx file using openxlsx package in R. In simple words, I need to write a row of data, which then populates some output cell that has to be read back in R. I will share an example to better explain the problem.

Initial state of the .xlsx file:

I'm now trying to write new values to the cell : A2:A3 = c("c", 5). So ideally, I'm expecting A6 = 15

Below is the code used :

require(openxlsx)
path <- "C:/path_to_file/for_SO1.xlsx"
input_row <- c("c", 5)
# Load workbook; create if not existing
wb <- loadWorkbook(path)
# createSheet(wb, name = "1")
writeData(wb, 
          sheet = "Sheet1",
          x = data.frame(input_row),
          startCol=1,
          startRow=1
)  

data_IM <- read.xlsx(wb, 
                     sheet = "Sheet1",
                     rows = c(5,6),
                     cols = c(1))
# Save workbook
saveWorkbook(wb, file = path, overwrite = TRUE)

#> data_IM
#  output_row
#1          3

But I get the inital value(3). However, If i open the .xlsx file, I can see the 15 residing there:

What could be the reason for not able to read this cell? I tried saving it after writing to the file and again reading it but even that failed. openxlsx is the only option I have due to JAVA errors from XLConnect etc.

Erivan

Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – Walburga

@Walburga thankyou for informing.. I am not getting nay replies and I'm still stuck here – Erivan

Might be worth adding the Java errors you have mentioned. Also, tomorrow you can add a bounty to the question, to tempt people into answering. – Walburga

Answer 1

?read.xlsx

Formulae written using writeFormula to a Workbook object will not get picked up by read.xlsx(). This is because only the formula is written and left to be evaluated when the file is opened in Excel. Opening, saving and closing the file with Excel will resolve this.

So the file needs to be opened in Excel and then saved, I can verify that this does work. However this may not be suitable for you.

XLConnect seems to have the desired functionality

# rjava can run out of memory sometimes, this can help.
options(java.parameters = "-Xmx1G")
library(XLConnect)

file_path = "test.xlsx"

input_row <- c("c", 5)

wb <- loadWorkbook(file_path, create=F)
writeWorksheet(wb, 1, startRow = 1, startCol = 1, data = data.frame(input_row))
setForceFormulaRecalculation(wb, 1, TRUE)
saveWorkbook(wb)

# checking
wb <- loadWorkbook(file_path, create=F)
readWorksheet(wb, 1)

Atlas

XLConnect can dynamically evaluate the formula and does give me the naswer but the issue with it is it doesn't support TRANSPOSE from excel and some other functions(being used in my calculations). It keeps giving me Apache POI Array error. – Erivan

So use openxlsx, save it, read it in with XLConnect, recalc, then save? I've otherwise 100% addressed the question. – Atlas

though I am still stuck, it's my duty to tickmark this! thank you – Erivan

Answer 2

The file https://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf says

Workbook object will not get picked up by read.xlsx(). This is because only the formula is written and left to be evaluated when the file is opened in Excel. Opening, saving and closing the file with Excel will resolve this. So if you are using windows then save following file vbs file to for example opensaveexcel.vbs

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("D:\Book2.xlsx")
objWorkbook.Save
objWorkbook.Close 
objExcel.Quit
Set objExcel = Nothing
Set objWorkbook = Nothing

and then you can write R code as cell A4 has formula in Book1.xlsx as =A3*5

mywritexlsx(fname="d:/Book1.xlsx",data = 20,startCol = 1,startRow = 3)
system("cp d:\\Book1.xlsx d:\\Book2.xlsx")
system("cscript //nologo d:\\opensaveexcel.vbs")
tdt1=read.xlsx(xlsxFile = "d:/Book1.xlsx",sheet = "Sheet1",colNames = FALSE)
tdt2=read.xlsx(xlsxFile = "d:/Book2.xlsx",sheet = "Sheet1",colNames = FALSE)

Works for me by the way mywritexlsx is as

mywritexlsx<-function(fname="temp.xlsx",sheetname="Sheet1",data,
                  startCol = 1, startRow = 1, colNames = TRUE, rowNames = FALSE)
{
  if(!file.exists(fname))
  {
   wb = openxlsx::createWorkbook()
   sheet = openxlsx::addWorksheet(wb, sheetname)
  }
  else
 {
   wb <- openxlsx::loadWorkbook(file =fname)
   if(!(sum(openxlsx::getSheetNames(fname)==sheetname)))
   sheet = openxlsx::addWorksheet(wb, sheetname)
   else
    sheet=sheetname
  }

  openxlsx::writeData(wb,sheet,data,startCol = startCol, startRow = startRow, 
          colNames = colNames, rowNames = rowNames)
  openxlsx::saveWorkbook(wb, fname,overwrite = TRUE)
}

Saury