5

openxlsx functions to write formula seem to add formula columnwise only.

Is there an efficient way to write formula rowwise?

Working through a reproducible example to illustrate the issue:

df <- data.frame(a = 1:3,
                 b = 4:6,
                 c = 7:9)

# create workbook, worksheet and write data to worksheet
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, "Sheet 1", x = df)

#  formula to be added rowwise to cells A5:C5
f <- c("SUM(A2:A4)", "SUM(B2:B4)", "SUM(C2:C4)")

# Using the openxlsx function results in columnwise addition of the formula vector: 
writeFormula(wb, sheet = 1, x = f, startCol = 1, startRow = 5)

saveWorkbook(wb, "rowwise_writeFormula.xlsx", overwrite = TRUE)

This results in:

enter image description here

writeFormula rowwise

The naive approach is to add each formula individually, maybe OK for small data sets.

writeFormula(wb, sheet = 1, x = "SUM(A2:A4)", startCol = 1, startRow = 5)
writeFormula(wb, sheet = 1, x = "SUM(B2:B4)", startCol = 2, startRow = 5)
writeFormula(wb, sheet = 1, x = "SUM(B2:B4)", startCol = 3, startRow = 5)

This provides what I want but is not a viable approach for hundreds of columns.

Using purrr::pwalk this can be wrapped up in a function, which is an improvement:

formula_inputs <- list(formula = f,
                       start_col = 1:3,
                       start_row = rep(5, 3))

purrr::pwalk(formula_inputs, function(formula, start_col, start_row) writeFormula(wb, sheet = 1, x = formula, startCol = start_col, startRow = start_row))

Either way this does what is required:

enter image description here

My question: is there a better way to do this? or might it be an enhancement to openxlsx?

In my real case I have 100 variables for 10 data frames in a list each requiring multiple (10) rowwise formula to build up to a total. In all about 10,000 formula need to be written, this takes several minutes; so any ideas on how to speed up the process would be welcome.

Note: openxlsx's alternative approach using writeData for vectors of class 'formula' also add the vectors as columns; when I try to transpose the vectors into rows the formula class is lost.

Peter
  • 11,500
  • 5
  • 21
  • 31
  • Why don't you just `f <- colSums(df)` then `rbind f` to `df` first and then export to the workbook? – SteveM Aug 15 '20 at 13:03
  • 2
    I would love to! but the workbook has to be populated by formula as the data is to be used by excel users and they want to change inputs to see the impact this has on totals; Although for the MRE I've used sum(), in the actual workbook there are more complex formula which show changes in, for example, inflation. The workbook needs to be dynamic/responsive: totals and subtotals need to respond to input changes. – Peter Aug 15 '20 at 13:25
  • It seems to me then that you should have a workbook template with the formulas embedded in it and then write the r outputs to copies of the template. Make sense? – SteveM Aug 15 '20 at 16:08
  • That's an interesting idea, however, in the real world beyond the MRE each data frame in the list has a different number of rows ranging from tens to thousands in number, not only do they vary but the number of rows is likely to change as the project develops. So I am keen to develop a solution which is programmed as much as possible and avoid working directly in excel. – Peter Aug 15 '20 at 18:03
  • I see with openxlsx that you can export to a named region, i.e, an Excel range name. In that case, if you build templates with functions that use the range names rather than absolute cell references, you can export data with different dimensions as names and still have the functions process properly. – SteveM Aug 16 '20 at 13:39
  • 1
    I am not sure this is possible, probably the best option available is to use `apply` family or `purrr` as per your approach above. There is another post on this [here](https://stackoverflow.com/questions/62299306/openxlsx-excel-formulae-in-a-row-how-to-create-formula-dynamically-for-each-col) – MKa Aug 17 '20 at 01:09
  • @Mka thank you for pointing out this answer: I missed it in my initial searching. I'll try it out: the `apply` approach may be quicker than `purrr` – Peter Aug 17 '20 at 10:50

1 Answers1

2

I found a for loop to be very helpful here:

for(c in 1:3){
      writeFormula(wb, 1, f[c], startCol = c, startRow = 5)
}
Laura
  • 97
  • 6