How can use variables for the row and columns in setCellFormula in the xlsx package in R?-CodePudding

I am trying to create a formula in a cell of an existing Excel document. I can easily do it this way:

#Load workbook
  wb<-loadWorkbook('test.xlsx') #Let this be any xlsx document with borders on cell B1 of Sheet2
#Get tab 
  tab=wb$getSheet('Sheet2')  
  rows<-getRows(tab)
  cells <- getCells(rows)
#You can set a formula in a cell
  cells$'1.2'$setCellFormula("A1") #Cell B1 will now have the formula "=A1"
  cells$`1.2`
#Save the workbook with a new name
  saveWorkbook(wb,"test2.xlsx")

What I would like to do is to be able to easily modify the address of the cell based on variables that I would calculate beforehand. Some thing such as:

#You can set a formula in a cell
  r<-1
  c<-2
  cells$'r.c'$setCellFormula("A1") #Cell B1 will now have the formula "=A1"
  cells$`r.c`

I tried something like this:

eval(paste0("cells$`",r,".",c,"'$setCellFormula('A1')"))

but all I get is:

[1] "cells$`2.2'$setCellFormula('A1')"

Does any one know how I can easily replace the row and column numbers by variables? Thanks for your help!

CodePudding user response：

You should parse the text before evaluating it:

eval(parse(text=paste0("cells$'",r,".",c,"'$setCellFormula('A1')")))