Nothing
#' 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("&", "&", 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)
}
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.