R/build_template.R

Defines functions template_content template_mixture_comment template_comment template_source template_expected_input template_entries template_rich_text template_inline template_markdown_rows template_col_widths template_style template_lists build_template

#' Build the management data template
#'
#' Generates the Excel template for management data from the specifications
#'  in `inst/spec`: the Read_me (from Markdown), the descriptions of all
#'  attributes and vocabulary, the allowed options, crops and products, and the
#'  management sheet with dependent dropdowns (category > operation > device >
#'  unit) and checks. Used by `tools/build-template.R`.
#'
#' @param spec list of parsed specifications, see `read_specifications()`
#' @param spec_dir directory of the specifications (for the Read_me and images)
#' @param out_file path of the xlsx file to write
#' @param package_version version of SoilManageR shown on the Read_me sheet
#' @param commit source commit shown on the Read_me sheet
#'
#' @return `out_file` (invisibly)
#'
#' @md
#' @noRd
build_template <- function(spec, spec_dir, out_file, package_version = NULL, commit = NULL) {

  if (!requireNamespace("openxlsx2", quietly = TRUE)) {
    stop("Package 'openxlsx2' is needed to build the template. Please install it.")
  }
  ex <- spec$excel
  sheet <- function(role) ex$sheets[[role]]$name
  n_rows <- ex$validation_rows
  font <- ex$default_font

  wb <- openxlsx2::wb_workbook(creator = "SoilManageR", title = "SoilManageR management data template")
  wb <- wb$set_base_font(font_size = font$size, font_name = font$name)
  style <- function(wb, sh, dims, name) template_style(wb, sh, dims, ex$styles[[name]], font)

  # Read_me ----------------
  sh <- sheet("readme")
  wb <- wb$add_worksheet(sh, grid_lines = FALSE)
  wb <- template_col_widths(wb, sh, ex$sheets$readme$col_widths)
  version <- paste(c(paste("Template version", spec$manifest$spec_version),
                     if (!is.null(package_version)) paste("SoilManageR", package_version),
                     if (!is.null(commit)) paste("commit", commit)), collapse = paste0(" ", intToUtf8(183), " "))
  rows <- template_markdown_rows(readLines(file.path(spec_dir, ex$readme$file), encoding = "UTF-8", warn = FALSE))
  rows <- append(rows, list(list(type = "version", runs = list(list(text = version)))), after = 1)
  r <- 1
  default_height <- 15
  heights <- numeric()                                                               # row heights in pt
  for (row in rows) {
    if (row$type == "heading" && r > 1) {                                           # empty row before headings
      heights[r] <- default_height
      r <- r + 1
    }
    dims <- paste0("A", r)
    style_name <- switch(row$type, title = "title", version = "version", heading = "heading", "paragraph")
    size <- ex$styles[[style_name]]$font$size
    wb <- wb$add_data(sh, template_rich_text(row$runs, size, font$name), dims = dims, col_names = FALSE)
    wb <- style(wb, sh, dims, style_name)
    text <- paste(vapply(row$runs, function(x) x$text, character(1)), collapse = "")
    lines <- max(1, ceiling(nchar(text) / ex$readme$chars_per_line))
    height <- switch(row$type, title = 32, heading = 22, lines * (if (is.null(size)) font$size else size) * 1.35 + 4)
    wb <- wb$set_row_heights(sh, rows = r, heights = height)
    heights[r] <- height
    r <- r + 1
  }
  heights[is.na(heights)] <- default_height
  # images are placed by their distance from the top: find the anchor row and the offset within that row.
  # openxlsx2 converts the image size, but writes the offsets unconverted: they must be whole EMU (1 cm = 360000 EMU)
  emu <- function(cm) if (is.null(cm)) 0L else as.integer(round(cm * 360000))
  row_top <- c(0, cumsum(heights)) * 2.54 / 72                                       # upper edge of each row in cm
  for (img in ex$readme$images) {
    anchor <- max(which(row_top <= img$top_cm))
    wb <- wb$add_image(sh, dims = paste0(img$column, anchor), file = file.path(spec_dir, img$file),
                                  width = img$width_cm, height = img$height_cm,
                                  col_offset = emu(img$col_offset_cm), row_offset = emu(img$top_cm - row_top[anchor]),
                                  units = "cm")
  }

  # Description ----------------
  sh <- sheet("description")
  wb <- wb$add_worksheet(sh)
  wb <- template_col_widths(wb, sh, ex$sheets$description$col_widths)
  header <- data.frame("Parameter", "Description", "Expected input", "Source")
  wb <- wb$add_data(sh, header, dims = "A1", col_names = FALSE)
  wb <- style(wb, sh, "A1:D1", "table_header")
  r <- 2
  add_block <- function(wb, df, label_style = NULL) {
    if (nrow(df) == 0) return(list(wb = wb, r = r))
    dims <- paste0("A", r, ":D", r + nrow(df) - 1)
    wb <- wb$add_data(sh, df, dims = paste0("A", r), col_names = FALSE, na.strings = "")
    wb <- style(wb, sh, dims, "text")
    if (!is.null(label_style)) {
      for (i in seq_len(nrow(df))) wb <- style(wb, sh, paste0("A", r + i - 1), label_style[i])
    }
    list(wb = wb, r = r + nrow(df))
  }
  add_section <- function(wb, title) {
    r <<- r + 1
    wb <- wb$add_data(sh, title, dims = paste0("A", r), col_names = FALSE)
    wb <- wb$merge_cells(sh, dims = paste0("A", r, ":D", r))
    wb <- style(wb, sh, paste0("A", r, ":D", r), "section")
    r <<- r + 1
    wb
  }
  attributes <- spec$attributes$attributes
  res <- add_block(wb, data.frame(
    vapply(attributes, function(a) a$name, ""), vapply(attributes, function(a) a$description, ""),
    vapply(attributes, template_expected_input, ""), NA_character_),
    label_style = vapply(attributes, function(a) ex$header_styles[[a$excel$header_group]], ""))
  wb <- res$wb; r <- res$r
  wb <- add_section(wb, "Description of categories")
  res <- add_block(wb, template_entries(spec$operations$categories, function(x) x$id)); wb <- res$wb; r <- res$r
  wb <- add_section(wb, "Description of operations")
  res <- add_block(wb, template_entries(spec$operations$operations, function(x) paste(x$category, x$id, sep = " > ")))
  wb <- res$wb; r <- res$r
  wb <- add_section(wb, "Description of devices")
  res <- add_block(wb, template_entries(spec$devices$devices, function(x) x$id)); wb <- res$wb; r <- res$r
  notes <- do.call(rbind, lapply(spec$management_options$options, function(o) {
    do.call(rbind, lapply(o$devices, function(d) {
      if (is.null(d$note)) return(NULL)
      data.frame(paste(o$operation, d$device, sep = " > "), d$note, "String from list", NA_character_)
    }))
  }))
  if (!is.null(notes)) {
    wb <- add_section(wb, "Notes on devices in specific operations")
    res <- add_block(wb, notes); wb <- res$wb; r <- res$r
  }
  wb <- add_section(wb, "Description of units")
  res <- add_block(wb, template_entries(spec$units$units, function(x) x$id)); wb <- res$wb; r <- res$r
  wb <- wb$freeze_pane(sh, first_row = TRUE, first_col = TRUE)

  # Options, crops and products ----------------
  category_of <- stats::setNames(vapply(spec$operations$operations, function(o) o$category, ""),
                                 vapply(spec$operations$operations, function(o) o$id, ""))
  options <- do.call(rbind, lapply(spec$management_options$options, function(o) {
    do.call(rbind, lapply(o$devices, function(d) data.frame(
      category = unname(category_of[o$operation]), operation = o$operation, device = d$device,
      units = paste(unlist(d$units), collapse = ", "), note = if (is.null(d$note)) NA_character_ else d$note)))
  }))
  main_crops <- Filter(function(cr) "main_crop" %in% unlist(cr$selectable_as), spec$crops$crops)
  crops <- data.frame(
    crop = vapply(main_crops, function(cr) cr$id, ""),
    scientific_name = vapply(main_crops, function(cr) if (is.null(cr$species)) NA_character_ else cr$species$scientific_name, ""),
    legume = vapply(main_crops, function(cr) if (is.null(cr$species$legume)) NA_character_ else if (isTRUE(cr$species$legume)) "yes" else "no", ""),
    selectable_as = vapply(main_crops, function(cr) gsub("_", " ", paste(unlist(cr$selectable_as), collapse = ", ")), ""))
  fertilizers <- spec$fertilizers$fertilizers
  other <- spec$products$products
  mixtures <- spec$seed_mixtures$seed_mixtures
  products <- rbind(
    data.frame(product = vapply(fertilizers, function(f) f$id, ""),
               type = vapply(fertilizers, function(f) paste(f$type, "fertilizer"), ""),
               operations = vapply(fertilizers, function(f) paste(unlist(f$operations), collapse = ", "), ""),
               livestock_manure = vapply(fertilizers, function(f) if (isTRUE(f$livestock_manure)) "yes" else "no", ""),
               comment = vapply(fertilizers, template_comment, "")),
    data.frame(product = vapply(other, function(p) p$id, ""),
               type = vapply(other, function(p) gsub("_", " ", p$type), ""),
               operations = vapply(other, function(p) paste(unlist(p$operations), collapse = ", "), ""),
               livestock_manure = NA_character_,
               comment = vapply(other, template_comment, "")),
    data.frame(product = vapply(mixtures, function(m) m$id, ""),
               type = rep("seed mixture", length(mixtures)),
               operations = vapply(mixtures, function(m) paste(unlist(m$operations), collapse = ", "), ""),
               livestock_manure = rep(NA_character_, length(mixtures)),
               comment = vapply(mixtures, template_mixture_comment, "")))
  for (tab in list(list("options", options), list("crops", crops), list("products", products))) {
    sh <- sheet(tab[[1]])
    wb <- wb$add_worksheet(sh)
    wb <- wb$add_data(sh, tab[[2]], dims = "A1", na.strings = "")
    last_col <- openxlsx2::int2col(ncol(tab[[2]]))
    wb <- style(wb, sh, paste0("A1:", last_col, "1"), "table_header")
    wb <- style(wb, sh, paste0("A2:", last_col, nrow(tab[[2]]) + 1), "text")
    wb <- template_col_widths(wb, sh, ex$sheets[[tab[[1]]]]$col_widths)
    wb <- wb$add_filter(sh, rows = 1, cols = seq_len(ncol(tab[[2]])))
    wb <- wb$freeze_pane(sh, first_row = TRUE)
  }

  # Management sheet ----------------
  sh <- sheet("management")
  wb <- wb$add_worksheet(sh)
  names <- vapply(attributes, function(a) a$name, "")
  col <- stats::setNames(openxlsx2::int2col(seq_along(names)), names)
  wb <- wb$add_data(sh, as.data.frame(t(names)), dims = "A1", col_names = FALSE)
  for (a in attributes) wb <- style(wb, sh, paste0(col[[a$name]], "1"), ex$header_styles[[a$excel$header_group]])
  wb <- template_col_widths(wb, sh, ex$sheets$management$col_widths)
  last <- n_rows + 1
  rng <- function(a) paste0(col[[a]], "2:", col[[a]], last)
  for (i in seq_along(ex$examples)) {
    for (a in names(ex$examples[[i]])) {
      v <- ex$examples[[i]][[a]]
      if (is.null(v)) next
      if (a == "date") v <- as.Date(v)
      wb <- wb$add_data(sh, v, dims = paste0(col[[a]], i + 1), col_names = FALSE)
    }
  }
  wb <- wb$add_formula(sh, dims = rng("year"),
                                  x = sprintf('IF(%s%d="","",YEAR(%s%d))', col[["date"]], 2:last, col[["date"]], 2:last))
  wb <- style(wb, sh, rng("date"), "date")
  wb <- wb$freeze_pane(sh, first_row = TRUE)

  # Lists behind the dropdowns (hidden sheet) ----------------
  lists <- template_lists(spec, crops$crop)
  lsh <- sheet("lists")
  wb <- wb$add_worksheet(lsh, visible = FALSE)
  # names without entries point to a cell with an empty text: an empty cell is shown as "0" and a missing
  # name as "#REF" in the dropdowns of LibreOffice
  wb <- wb$add_formula(lsh, x = '""', dims = "A1")
  for (list_name in attr(lists, "empty")) {
    wb <- wb$add_named_region(lsh, dims = "A1", name = list_name)
  }
  r <- 2
  for (list_name in names(lists)) {
    v <- lists[[list_name]]
    wb <- wb$add_data(lsh, data.frame(v), dims = paste0("A", r), col_names = FALSE)
    wb <- wb$add_named_region(lsh, dims = paste0("A", r, ":A", r + length(v) - 1), name = list_name)
    r <- r + length(v)
  }

  # Checks and dropdowns of the management sheet ----------------
  # openxlsx2 writes the formulas unescaped into the XML: "&" must be escaped, otherwise Excel cannot open the file
  amp <- function(x) gsub("&", "&amp;", x, fixed = TRUE)
  first <- function(a) paste0("$", col[[a]], "2")
  dv <- function(wb, a, type, value, ...) {
    wb$add_data_validation(sh, dims = rng(a), type = type, value = value, ...)
  }
  wb <- dv(wb, "crop", "list", "list_crop")
  wb <- dv(wb, "date", "date", as.Date(c("1900-01-01", "2099-12-31")), operator = "between")
  wb <- dv(wb, "category", "list", "list_category")
  wb <- dv(wb, "operation", "list", amp(sprintf('INDIRECT("ops_"&%s)', first("category"))))
  wb <- dv(wb, "device", "list", amp(sprintf('INDIRECT("dev_"&%s)', first("operation"))))
  wb <- dv(wb, "unit", "list", amp(sprintf('INDIRECT("unit_"&%s&"__"&%s)', first("operation"), first("device"))))
  # products of the chosen operation as suggestions; other products are kept after an information message
  # (LibreOffice ignores a disabled error message for lists and would reject them)
  wb <- dv(wb, "product", "list", amp(sprintf('INDIRECT("prod_"&%s)', first("operation"))),
           error_style = "information", error_title = "Product not suggested",
           error = "This product is not among the suggestions for the chosen operation. Click OK to keep it.")
  wb <- dv(wb, "combination", "whole", c(1, 1e9), operator = "between")
  for (a in names[vapply(attributes, function(x) x$type == "number", logical(1))]) {
    wb <- dv(wb, a, "decimal", if (a == "value") c(-1e9, 1e9) else c(0, 1e9), operator = "between")
  }

  # Sheet order: management sheet is active and the only selected sheet
  wb <- wb$set_active_sheet(sh)
  wb <- wb$set_selected(sh)

  wb$save(out_file, overwrite = TRUE)
  invisible(out_file)
}

#' Named lists for the dropdowns of the template
#'
#' @param spec list of parsed specifications
#' @param crops crops offered in the crop column
#'
#' @return named list of character vectors; names are valid Excel names. Names of lists
#'  without entries (e.g. devices without units) are given in the attribute "empty".
#'
#' @md
#' @noRd
template_lists <- function(spec, crops) {
  operations <- spec$operations$operations
  lists <- list(list_crop = crops,
                list_category = vapply(spec$operations$categories, function(x) x$id, ""))
  # products suggested per operation; names without entries are returned in attribute "empty"
  empty <- character()
  products <- c(spec$fertilizers$fertilizers, spec$products$products, spec$seed_mixtures$seed_mixtures)
  for (o in operations) {
    ids <- vapply(Filter(function(p) o$id %in% unlist(p$operations), products), function(p) p$id, "")
    if (length(ids)) lists[[paste0("prod_", o$id)]] <- ids else empty <- c(empty, paste0("prod_", o$id))
  }
  for (cat in lists$list_category) {
    ops <- vapply(Filter(function(o) o$category == cat, operations), function(o) o$id, "")
    if (length(ops)) lists[[paste0("ops_", cat)]] <- ops
  }
  for (o in spec$management_options$options) {
    lists[[paste0("dev_", o$operation)]] <- vapply(o$devices, function(d) d$device, "")
    for (d in o$devices) {
      units <- unlist(d$units)
      # no entries if no unit is expected: only an empty cell is valid
      name <- paste0("unit_", o$operation, "__", d$device)
      if (length(units)) lists[[name]] <- units else empty <- c(empty, name)
    }
  }
  all_names <- c(names(lists), empty)
  bad <- all_names[!grepl("^[A-Za-z_][A-Za-z0-9_.]*$", all_names) | nchar(all_names) > 255]
  if (length(bad) || anyDuplicated(tolower(all_names))) {
    stop("Invalid or duplicated names for the template lists: ", paste(bad, collapse = ", "))
  }
  attr(lists, "empty") <- empty
  lists
}

#' Apply a named style to cells
#' @noRd
template_style <- function(wb, sheet, dims, s, default_font) {
  if (is.null(s)) return(wb)
  f <- s$font
  if (!is.null(f)) {
    wb <- wb$add_font(sheet, dims, name = default_font$name,
                                 size = if (is.null(f$size)) default_font$size else f$size,
                                 bold = if (isTRUE(f$bold)) "1" else "",
                                 italic = if (isTRUE(f$italic)) "1" else "",
                                 underline = if (isTRUE(f$underline)) "single" else "",
                                 color = openxlsx2::wb_color(hex = if (is.null(f$color)) "FF000000" else f$color))
  }
  if (!is.null(s$fill)) wb <- wb$add_fill(sheet, dims, color = openxlsx2::wb_color(hex = s$fill))
  if (!is.null(s$wrap) || !is.null(s$valign) || !is.null(s$halign)) {
    wb <- wb$add_cell_style(sheet, dims, wrap_text = if (isTRUE(s$wrap)) "1" else NULL,
                                       vertical = s$valign, horizontal = s$halign)
  }
  if (!is.null(s$numfmt)) wb <- wb$add_numfmt(sheet, dims, numfmt = s$numfmt)
  wb
}

#' Set column widths
#' @noRd
template_col_widths <- function(wb, sheet, widths) {
  if (length(widths) == 0) return(wb)
  wb$set_col_widths(sheet, cols = seq_along(widths), widths = unlist(widths))
}

#' Convert Markdown into rows of the Read_me sheet
#'
#' Supports "# " (title), "## " (heading), paragraphs separated by empty lines,
#'  "- " list items, **bold** and *italic*. HTML comments are ignored.
#'
#' @param lines lines of the Markdown file
#'
#' @return list of rows with `type` and `runs` (text with bold/italic)
#'
#' @md
#' @noRd
template_markdown_rows <- function(lines) {
  text <- paste(lines, collapse = "\n")
  text <- gsub("(?s)<!--.*?-->", "", text, perl = TRUE)   # comments may span several lines
  blocks <- strsplit(text, "\n[ \t]*\n", perl = TRUE)[[1]]
  rows <- list()
  for (block in blocks) {
    block_lines <- trimws(strsplit(block, "\n", fixed = TRUE)[[1]])
    block_lines <- block_lines[nzchar(block_lines)]
    if (length(block_lines) == 0) next
    if (all(grepl("^- ", block_lines))) {
      for (item in block_lines) {
        rows[[length(rows) + 1]] <- list(type = "item", runs = template_inline(paste0(intToUtf8(8226), " ", sub("^- ", "", item))))
      }
      next
    }
    first <- block_lines[1]
    type <- if (grepl("^# ", first)) "title" else if (grepl("^## ", first)) "heading" else "paragraph"
    content <- paste(sub("^#+ ", "", block_lines), collapse = " ")
    rows[[length(rows) + 1]] <- list(type = type, runs = template_inline(content))
  }
  rows
}

#' Split text with **bold** and *italic* into runs
#' @noRd
template_inline <- function(text) {
  tokens <- regmatches(text, gregexpr("\\*\\*[^*]+\\*\\*|\\*[^*]+\\*|[^*]+", text, perl = TRUE))[[1]]
  lapply(tokens, function(t) {
    if (grepl("^\\*\\*.*\\*\\*$", t)) list(text = gsub("^\\*\\*|\\*\\*$", "", t), bold = TRUE)
    else if (grepl("^\\*.*\\*$", t)) list(text = gsub("^\\*|\\*$", "", t), italic = TRUE)
    else list(text = t)
  })
}

#' Rich text for a cell; every run gets the size of the cell style
#' @noRd
template_rich_text <- function(runs, size, font_name) {
  if (length(runs) == 1 && is.null(runs[[1]]$bold) && is.null(runs[[1]]$italic)) return(runs[[1]]$text)
  parts <- lapply(runs, function(r) openxlsx2::fmt_txt(r$text, bold = isTRUE(r$bold), italic = isTRUE(r$italic),
                                                      size = size, font = font_name))
  Reduce(`+`, parts)
}

#' Rows (label, description, expected input, source) for the Description sheet
#' @noRd
template_entries <- function(entries, label) {
  do.call(rbind, lapply(entries, function(e) data.frame(
    label(e), e$description, "String from list", template_source(e))))
}

#' Expected input of an attribute
#' @noRd
template_expected_input <- function(a) {
  if (!is.null(a$allowed_values)) {
    return(if (isTRUE(a$suggestions_only)) "Text, suggestions from list" else "String from list")
  }
  switch(a$type, date = "Date", integer = "Integer", number = "Number", "Text")
}

#' Source text of an entry: the source note, or the source keys
#' @noRd
template_source <- function(e) {
  if (!is.null(e$source_note)) return(e$source_note)
  if (length(e$sources)) return(paste(unlist(e$sources), collapse = ", "))
  NA_character_
}

#' Comment of a product: description and source
#' @noRd
template_comment <- function(p) {
  parts <- c(p$description, template_source(p))
  parts <- parts[!is.na(parts)]
  if (length(parts)) paste(parts, collapse = " ") else NA_character_
}

#' Comment of a seed mixture in the Products sheet: components and source
#'
#' @param m seed mixture entry of seed_mixtures.json
#' @return character
#' @noRd
template_mixture_comment <- function(m) {
  unit <- c(seed_mass_percent = " %", seed_mass_g_per_kg_mixture = " g/kg", seed_mass_kg_per_ha = " kg/ha")[[m$basis]]
  components <- paste0("Components (seed mass): ",
                       paste(vapply(m$components, function(x) paste0(x$crop, " ", x$amount, unit), ""), collapse = ", "), ".")
  paste(c(components, template_comment(m)[!is.na(template_comment(m))]), collapse = " ")
}

#' Content of a generated workbook
#'
#' Reads the parts of an xlsx file (a zip archive) without the document
#'  properties, which contain the creation time. Two templates generated from
#'  the same specifications have identical content. Used by
#'  `tools/build-template.R` to decide whether the shipped template is up to date.
#'
#' @param file path of an xlsx file
#'
#' @return named list of raw vectors, one per part of the workbook
#'
#' @md
#' @noRd
template_content <- function(file) {
  dir <- tempfile()
  on.exit(unlink(dir, recursive = TRUE), add = TRUE)
  utils::unzip(file, exdir = dir)
  parts <- sort(setdiff(list.files(dir, recursive = TRUE), "docProps/core.xml"))
  stats::setNames(lapply(parts, function(p) readBin(file.path(dir, p), "raw", file.size(file.path(dir, p)))), parts)
}

Try the SoilManageR package in your browser

Any scripts or data that you put into this service are public.

SoilManageR documentation built on Nov. 17, 2026, 5:08 p.m.