sparklines everywhere!

[JanMarvin]

A nice way to visualize data is via sparklines. Since openxlsx2 already provides the functionality to create them, it is also possible to add them wherever you want. Find below an example adding sparklines next to columns and rows of a data object.

library(openxlsx2)

sparklines <- function(x, col_names = TRUE, rows = TRUE, cols = TRUE) {
  
  out <- NULL
  
  if (rows) {
    row <- seq_len(nrow(x)) + col_names
    
    tmp <- vapply(row, function(ll) {
      sqref <- wb_dims(cols = seq_along(x), rows = ll)
      dims <- wb_dims(cols = ncol(x) + 1L, rows = ll)
      create_sparklines("Sheet 1", sqref, dims)  
    }, NA_character_)
    out <- c(out, tmp)
  }
  
  if (cols) {
    
    col <- seq_len(ncol(x))
    
    tmp <- vapply(col, function(ll) {
      sqref <- wb_dims(cols = ll, rows = seq_len(nrow(x)) + col_names)
      dims <- wb_dims(cols = ll, rows = nrow(x) + col_names + 1L)
      create_sparklines("Sheet 1", sqref, dims)
    }, NA_character_)
    out <- c(out, tmp)
  }
  
  out
  
}

sparklines <- sparklines(mtcars)

wb <- wb_workbook() %>%
  wb_add_worksheet() %>%
  wb_add_data(x = mtcars) %>%
  wb_add_sparklines(sparklines = sparklines)

if (interactive()) wb$open()

[Edit:] Fixed a thinko in my initial solution, that was avoided in @olivroys solution below

[olivroy]

To use wb_dims() "magic" (relative positioning), the function can also be:

sparklines <- function(x, col_names = TRUE, rows = TRUE, cols = TRUE) {
  
  out <- NULL
  
  if (rows) {
    row <- seq_len(nrow(x))
    
    tmp <- vapply(row, function(ll) {
      sqref <- wb_dims(x = x, rows = ll, col_names = col_names)
      dims  <- wb_dims(x = x, rows = ll, cols = ncol(x) + 1L, col_names = col_names)
      create_sparklines("Sheet 1", sqref, dims)  
    }, NA_character_)
    out <- c(out, tmp)
  }
  
  if (cols) {
    
    col <- seq_len(ncol(x))
    
    tmp <- vapply(col, function(ll) {
      sqref <- wb_dims(x = x, cols = ll, col_names = col_names)
      dims  <- wb_dims(x = x, cols = ll, rows = nrow(x) + 1L, col_names = col_names)
      create_sparklines("Sheet 1", sqref, dims)
    }, NA_character_)
    out <- c(out, tmp)
  }
  
  out
  
}

[JanMarvin]

Thanks, I was wondering if it’s worth adding something like this function to the package . But shoving all possible arguments down instantly felt to much copy and paste.

[olivroy]

Maybe an auto argument to wb_add_sparklines()? I haven't given this much thought, but by default.

A sparkline for rows is expected in the column right after .
I think it would be common, not to use every column for a column sparkline.
However, most definitely, every row likely would be used .

I think that a openxlsx2 sparklines R focused function and argument defaults could look like nanoplots from gt? https://gt.rstudio.com/reference/cols_nanoplot.html?

[JanMarvin]

Something like an auto argument might indeed be a good idea. For the future, I planned to embed create_sparklines() in wb_add_sparklines() and the same for create_comment() in wb_add_comment(). When I started working on create_sparklines(), I was inspired by create_comment(), and now I didn't want to break things. But for instance the sheet argument in create_sparklines() requires a wbWorkbook object and we don't pass any.

I will have a look at the gt function. Having small graphs in cells is similarly possible in flextable with gg_chunk. I'm not using either package yet, but maybe it will spark an idea (sparklines -> spark ... ah it's been a long day 😃 ). One difference maybe, sparklines can be overlays on cells similar to databars.

PS: The above example came to mind today when I was thinking about #918 and I was actually surprised at how easy it was to create something like the above function.