0

I am trying to write formulas from an R dataframe to XLSX file. I want to reference data from one worksheet in another using cell references. One worksheet is suppposed to be a "database" and then the other sheet is supposed to have the "formula" which references data from the "database" sheet.

The R code:

require(openxlsx)

# create workbook ---------------------------------------------------------

wb <- createWorkbook()

# add worksheets ----------------------------------------------------------

addWorksheet(wb, "database")
addWorksheet(wb, "database space")
addWorksheet(wb, "reference")

# make database -----------------------------------------------------------

df1 <- data.frame(
    a = 1,
    b = 2,
    formula_1 = "A2 + B2"
)

## change class to formula
class(df1$formula_1) <- c(class(df1$formula_1), "formula")

# prepare formula ---------------------------------------------------------

df2 <- data.frame(
    formula_2 = "$database.A2",
    formula_3 = "$'database space'.B2"
)

# change class to formula
class(df2$formula_2) <- c(class(df2$formula_2), "formula")
class(df2$formula_3) <- c(class(df2$formula_3), "formula")

# write to XLSX file ------------------------------------------------------

writeData(wb, sheet = "database", x = df1)
writeData(wb, sheet = "database space", x = df1)
writeData(wb, sheet = "reference", x = df2)

# open XLSX file ----------------------------------------------------------

openXL(wb)

The usual way of adding a formula works. In the openend XLSX file this is demonstrated in cell C3 of worksheet database and database space. But, when opening worksheet formula, my issue presents itself (see screenshot 1). Cells A2 and B2 both show #NAME? and not the value of the other worksheets.

screenshot 1

However, when I add the cell reference manually, it works (see screenshot 2).

screenshot 2

openxlsx version

> packageVersion("openxlsx")
[1] ‘4.0.17’

How can I solve this problem?

larnsce
  • 103
  • 8
  • 1
    Boy, I dearly wish you had not used the name `formula` for your function. It's the name of such a core R function. – IRTFM May 03 '18 at 20:39
  • Thanks for your comment! I have renamed the objects to `df1` and `df2`. Do you have any idea how I can solve my problem @42-? – larnsce May 04 '18 at 09:06

1 Answers1

0

The problem here are the names that are passed to df2. The solution to the problem is to remove the $ and replace the . with a !.

Replacing df2 in the code above with the following works:

df2 <- data.frame(
    formula_2 = "database!A2",
    formula_3 = "'database space'!B2"
)

The character strings are now evaluated as formulas and correctly reference cells from other sheets. I found the solution by working through the XLConnect R package, where an example in the help file for setCellFormula-methods includes a formula as a reference to a separate sheet.

larnsce
  • 103
  • 8