0

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?

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
P.A
  • 3
  • 1

1 Answers1

0

You should parse the text before evaluating it:

eval(parse(text=paste0("cells$'",r,".",c,"'$setCellFormula('A1')")))
Waldi
  • 39,242
  • 6
  • 30
  • 78
  • 1
    Thanks, that works. However, when I try to create a formula pointing to a different tab like this: eval(parse(text=paste0("cells$'",r,".",c,"'$setCellFormula('Sheet 3'!A1)"))) I get the following error message: Error in parse(text = paste0("cells$'", r, ".", c, "'$setCellFormula('Sheet 3'!A1)")) : :1:37: unexpected '!' 1: cells$'1.2'$setCellFormula('Sheet 3'! I tried escaping the "!" but it didn't work. Any idea? – P.A Mar 11 '22 at 00:28
  • I would try to use `setCellFormula(cell,formula)` function directly instead of parsing text but I didn't find a description of this function in `xlsx` package documentation – Waldi Mar 11 '22 at 08:34