Write MMult function in excel via R script-CodePudding

I'm using R to create a set of matrixes than I need to publish in an excel workbook. The final result is a matrix (M) and a vector (X) and a vector (Y), where M*X=Y. The "user" of the workbook will modify the X vector to view the impact on Y.

I'm using the openxlsx library to create the excel workbook from the R script. But I haven't found a way of writing the MMult formula that calculates the Y vector to the result workbook.

Is there a way to write the MMult formula? Or what other strategy could I use?

I hope the explanation wasn't too convoluted. Thanks in advance

CodePudding user response：

Up front: if I'm interpreting you correctly, then this is not currently supported.

What does work

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
writeData(wb = wb, sheet = "Sheet1", x = matrix(1:6, ncol = 2), startCol = 1)
writeData(wb = wb, sheet = "Sheet1", x = matrix(7:12, nrow = 2), startCol = 4)
writeFormula(wb = wb, sheet = "Sheet1", x = "MMULT(A2:B4, D2:F3)", startCol = 8, array = T)
saveWorkbook(wb = wb, file = "quux.xlsx")

If I click on H1, edit it (do nothing) and hit enter, this becomes:

Imperfect, but at least to get it working does not take significant effort. Since it appears this is for a user to explore changing one of the vectors, it may not be too onerous to say "... click in $H$1, click Fn-2 (edit), hit Enter".

(Interesting side note: it does not impose the {...} curly braces around the formula that normally denote an array-formula. Editing the field and pressing Shift-Control-Enter does add the braces, same numeric results.)

Background

It appears the premise of adding formulas was added in Jan 2021, as PR #131. Unfortunately, the author of the PR (since merged) replied 3 months later with this very point: it requires editing the cell to get it to expand to the full matrix.

Since the issue is merged and there has been a bit of work since that PR, I suspect that either the maintainer(s) have forgotten about comments on a merged PR, or perhaps they aren't interested in fixing it. I suggest it might be useful for you to raise a new issue and perhaps pinging the two other commenters in #131 to get their support, comments, etc.