Integral Style Excel Formatting

[jzadra]

Lets use this issue to keep track of the excel formatting functionality goals and progress.

Features:

• [ ] Pre-defined styles (bold, italic, justification, valign, borders, wrapping, etc)
• [ ] Method for handling custom notes that appear after the table (possibly pull from either another excel file or text file)
• [ ] Method for handling merged cells
• [ ] Method for handling hanging indents
• [ ] Generalized function for determining starting rows for styles/indents based on column title rows and previous data
• [ ] Set or determine page breaks for printing/pdf
• [ ] Header/footer functions that include specialized Excel codes
• [ ] Superscript/subscript style

Things to look into:

• Can we import an excel file as a template and then use the styles and formatting in it to apply to new data?

General Thoughts/Questions:

• Who is our target user?
• We need to decide where to draw the line on completely automated formatting, vs things that require custom scripting
• Can we get as many samples of custom tables used on projects as possible for testing?

[jzadra]

Pre-defined styles:

Lets brainstorm styles here, and consider a standard naming scheme.

Should we prefix all with something like style_? Should we have a single style function for border that has arguments for top/bottom/double, etc, or keep them standalone?

Lets also make sure that all styles are stacked by default.

border_top <- createStyle(border = "top")
border_bottom <- createStyle(border = "bottom")
border_bottom_double <- createStyle(border = "bottom", borderStyle = "double")
halign_left <- createStyle(halign = "left")
halign_center <- createStyle(halign = "center")
halign_right <- createStyle(halign = "right")
italic <- createStyle(textDecoration = "italic")
wrap_text <- createStyle(wrapText = TRUE)
indent <- createStyle(indent = 1)
bold <- createStyle(textDecoration = "bold")
valign_top <- createStyle(valign = "top")
valign_bottom <- createStyle(valign = "bottom")

[jzadra]

Detecting cells to merge, here is something in progress that detects contiguous runs of the same value:

index_identical_rows <- function(.data, column, startRow = 1) {
  column <- rlang::enquo(column)

  .data %>%
    dplyr::mutate(start = (!!column != dplyr::lag(!!column) | is.na(dplyr::lag(!!column))) & !is.na(!!column),
           end = (!!column != dplyr::lead(!!column) | is.na(dplyr::lead(!!column))) & !is.na(!!column)) %>%
    dplyr::summarize(start = which(start),
              end = which(end)) %>%
    dplyr::filter(start != end) %>%
    dplyr::mutate(dplyr::across(tidyselect::everything(), ~ . + startRow - 1)) %>%
    purrr::array_tree(margin = 1)
}