Openxlsx2: Ability to create_speaklines in a group

Question

I'm using openxlsx2 in R/Posit to create sparklines and hoping to create one group all at once. Excel allows the user to edit a group of sparklines at one time which makes it easier than editing a single spark at a time for many rows. Does openxlsx2 allow this functionality? I have not been able to get it to work, and might be doing something wrong.

This is the example I'm trying to replicate: Sample

And here is the reprex code I'm trying to mimic this functionality with based on the package documentation:

sparklines <- c(create_sparklines("Sheet 1", "A2:L13", "M2:M13", markers = "1"))

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))

wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data(x = t2)$
  add_sparklines(sparklines = sparklines)

Answer 1

You can use this:

# helper function
sparkline_group <- function(sparklines) {
  slg <- sparklines[1]
  sl <- xml_node(sparklines[-1], "x14:sparklineGroup", "x14:sparklines", "x14:sparkline")
  xml_add_child(slg, sl, level = c("x14:sparklines"))
}

In @stefan's example

library(openxlsx2)

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))

sparklines <- sapply(
  seq_len(nrow(t2)), \(row) {
    create_sparklines(
      "Sheet 1",
      rowcol_to_dims(row + 1, 1:12),
      rowcol_to_dims(row + 1, 13),
      markers = "1"
    )
  }
)

sparklines_grp <- sparkline_group(sparklines)

sparklines_grp |> length()
#> [1] 1

wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data(x = t2)$
  add_sparklines(sparklines = sparklines_grp)

if (interactive()) wb$open()

Answer 2

Not sure whether I understand you correctly, but you could use e.g. sapply (or a for loop) to create the vector of sparklines where I use rowcol_to_dims for convenience like so:

library(openxlsx2)

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))

sparklines <- sapply(
  seq_len(nrow(t2)), \(row) {
    create_sparklines(
      "Sheet 1",
      rowcol_to_dims(row + 1, 1:12),
      rowcol_to_dims(row + 1, 13),
      markers = "1"
    )
  }
)

sparklines |> length()
#> [1] 12

wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data(x = t2)$
  add_sparklines(sparklines = sparklines)

wb$save("foo.xlsx")