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)

stefan · Accepted Answer · 2024-05-23 12:42:33Z

1

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")

edited May 23, 2024 at 12:42

answered May 23, 2024 at 12:34

stefan

130k6 gold badges42 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Heather Hays Over a year ago

Yes, thank you for that. However, excel allows you to specify columns in one group - so the end user who will only have access to the excel output can then edit which columns are included in the sparks, as a GROUP and not have to do each individual spark at a time. I was hoping that openxlsx2 would also allow the group option. My sample screenshot above will show what excel has.

Jan Marvin · Accepted Answer · 2024-05-24 17:40:34Z

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()

answered May 24, 2024 at 17:40

Jan Marvin

1,0341 gold badge7 silver badges11 bronze badges

3 Comments

stefan Over a year ago

Nice, Jan. Will keep this approach to manipulate the xml code in mind. BTW: Just out of curiosity I prepared a draft for a PR which allows to add multiple sparklines as a group using the OPs code. See here.

Jan Marvin Over a year ago

:) If it works and passes our tests (and maybe a new one), this will be a very welcome addition. (Honestly, I hadn't heard of the whole feature until I saw this post yesterday).

stefan Over a year ago

Great, Jan. Will do. It works and (after a small change) passes all tests. Of course will I add a new test.

Collectives™ on Stack Overflow

Openxlsx2: Ability to create_speaklines in a group

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related