Suggestion: wb_add_alternating_row_fill_color()

[SchmidtPaul]

Hey, I sometimes need this but did not see any implementation of it. Does something like this seem worth implementing to you?

#' Add Alternating Row Fill Colors to Workbook
#'
#' This function adds alternating row fill colors to a specified worksheet in a workbook based on unique identifiers in a data frame column.
#'
#' @param wb A `wbWorkbook` object.
#' @param sheet The name of the worksheet within the workbook. Defaults to the current sheet.
#' @param x A data frame containing the data.
#' @param alternate_for A column name in the data frame to alternate the row colors for. Defaults to 1 (i.e. the first column).
#' @param wb_color1 The first fill color. Defaults to white.
#' @param wb_color2 The second fill color. Defaults to grey80.
#' @param progressbar Logical. Whether to show a progress bar. Automatically shows for more than 30 unique identifiers if not specified.
#'
#' @return A `wbWorkbook` object with the specified alternating row fill colors applied.
#' @import openxlsx2
#' @export
#' @examples
#' library(openxlsx2)
#' exdat <- data.frame(
#'   exp_id = rep(1:5, each = 5),
#'   value = sample(c(1:10, NA), 25, replace = TRUE)
#' )
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb, "Sheet1")
#' wb <- wb_add_data(wb, sheet = "Sheet1", x = exdat)
#' wb <- wb_add_alternating_row_fill_color(
#'   wb = wb,
#'   sheet = "Sheet1",
#'   x = exdat,
#'   alternate_for = "exp_id",
#'   progressbar = TRUE
#' )
#' # wb_open(wb)

wb_add_alternating_row_fill_color <- function(wb,
                                              sheet = current_sheet(),
                                              x,
                                              alternate_for = 1,
                                              wb_color1 = wb_color(name = "white"),
                                              wb_color2 = wb_color(name = "grey80"),
                                              progressbar) {
  ids <- x[[alternate_for]]
  
  unique_ids <- unique(ids)
  current_wb_color <- wb_color1
  cols <- 1:ncol(x)
  
  total <- length(unique_ids)
  
  if (missing(progressbar)) {
    show_progress <- total > 30
  } else {
    show_progress <- isTRUE(progressbar)
  }
  
  # progress bar init
  if (show_progress) {
    pb <- txtProgressBar(min = 0, max = total, style = 3)
  }
  
  for (i in seq_along(unique_ids)) {
    id <- unique_ids[i]
    rows <- which(ids == id) + 1
    dims <- wb_dims(rows = rows, cols = cols)
    wb <- wb_add_fill(
      wb = wb,
      sheet = sheet,
      dims = dims,
      color = current_wb_color
    )
    
    # Toggle color for next group
    if (current_wb_color == wb_color1) {
      current_wb_color <- wb_color2
    } else {
      current_wb_color <- wb_color1
    }
    
    # progress bar update
    if (show_progress) {
      setTxtProgressBar(pb, i)
    }
  }
  
  # progress bar close
  if (show_progress) {
    close(pb)
  }
  
  return(wb)
}

Example

library(openxlsx2)
exdat <- data.frame(
  exp_id = rep(1:5, each = 5),
  value = sample(c(1:10, NA), 25, replace = TRUE)
)
wb <- wb_workbook()
wb <- wb_add_worksheet(wb, "Sheet1")
wb <- wb_add_data(wb, sheet = "Sheet1", x = exdat)
wb <- wb_add_alternating_row_fill_color(
  wb = wb,
  sheet = "Sheet1",
  x = exdat,
  alternate_for = "exp_id",
  progressbar = TRUE
)
#> |======================================================================| 100%

^{Created on 2024-07-08 with reprex v2.1.0}

wb_open(wb)

[JanMarvin]

Hi @SchmidtPaul ,

thanks for the suggestion. I'll think about it, but I'm somewhat inclined to reject it. I'd like to keep the functionality of openxlsx2 to be simply xlsx tweaking related.

I'd really love to see something like a openxlsx2extra package for user contributed functions that extend the package, similar to how flexlsx was created. We have other examples how to do cool things with our little package in the show and tell area of our discussion area, something similar for instance is: #881, further extensions: #784, or little helpers like #710. Maybe a selection of these could be polished a bit more and including your function be the start of a new package?

[JanMarvin]

I have moved this to show and tell. But as stated, I am very supportive of some openxlsx2 extension package.
Let me know if you want to work on this. I wouldn't want to maintain this too, my time and energy has limits, but obviously would help out where needed.

Something like #93 would be good fit too.