R/report_from_.R

In wurli/quickReport: Generate Excel Reports from SQL using R

initialise_report <- function(template = "Excel/template.xlsx", creator = get_user(), ...) {

  notify("Initialising workbook using template %s", template %||% "default")

  if (!is.null(template) && length(template) != 1) {
    stop("`template` should have length 1")
  }

  if (is.null(template)) {

    out <- openxlsx::copyWorkbook(default_template, ...)

  } else {

    if (!file.exists(template)) {
      stop(paste0(
        "File '", template, "' does not exist. `template` should be either ",
        "`NULL` or a filepath"
      ))
    }

    out <- openxlsx::loadWorkbook(template, ...)

  }

  out$addCreator(creator)

  out

}


#' Populate the report coversheet
#'
#' @param wb A workbook generated by `initialise_report()`
#' @param data A named list of data to write. Names give cell entries
#' @param sheet The name of the sheet to write to
#' @param ... Extra options passed to `openxlsx::writeData()`
populate_coversheet <- function(wb = initialise_report(),
                                data = list(),
                                sheet = "Cover Sheet",
                                ...) {

  notify("Populating %s with report information", sheet)

  opts <- list(...)

  allowed_opts <- setdiff(
    methods::formalArgs(openxlsx::writeData),
    c("wb", "sheet", "x", "startCol", "startRow", "colNames")
  )

  if (!all(names(opts) %in% allowed_opts)) {
    stop(sprintf(
      'Unrecognised options. Allowed options are c("%s")',
      paste(allowed_opts, collapse = '", "')
    ))
  }

  cellrefs <- parse_cellref(names(data))

  for (i in seq_along(data)) {

    openxlsx::writeData(
      wb = wb,
      sheet = sheet,
      x = data[[i]],
      startCol = cellrefs$cols[[i]],
      startRow = cellrefs$rows[[i]],
      colNames = FALSE,
      ...
    )

  }

  wb

}

#' Save an Excel export
#'
#' @param wb The workbook to save
#' @param replace If `TRUE` then the function will fail if such a file already
#'   exists.
#' @param filepath The filepath to save to
save_excel_export <- function(wb, filepath, replace = FALSE) {

  notify("Saving workbook as %s", filepath, .type = "li")

  openxlsx::saveWorkbook(wb, filepath, overwrite = replace)

  invisible(TRUE)

}

#' Create an Excel Report
#'
#' These functions can be used to quickly generate a fairly decent Excel
#' workbook containing a collection of datasets. `report_from_data()` allows you
#' to export a list of data.frames (where worksheet names are taken from the
#' list names), while `report_from_sql()` will read the data using a directory
#' containing SQL scripts (where worksheet names are taken from the SQL script
#' names). A main advantage of these functions is the ability to supply an
#' existing Excel template - see 'Details' for more information.
#'
#' Reports generated with {quickReport} consist of a coversheet containing
#' report information and separate sheets for each dataset included. You can
#' provide a template for a report by supplying a path to an existing workbook
#' in the `template` field. By default, the first sheet will be used as a
#' template for the report coversheet, while the second will be used as a
#' template for each data sheet added to the report. If you wish to use a
#' different template for a dataset included in the report, you can do
#' this by including a worksheet in the template workbook with the same name as
#' the dataset in question. Within the template worksheets you can specify where
#' the data should be placed by entering the marker `'!data'` in the first cell
#' where the data should appear. You can change the marker value using
#' `options(qr.marker = "new-value")`.
#'
#' @param filename The name of the file to create
#' @param file_template A filepath pointing to an Excel workbook to use as a
#'   template. See 'Details' for more information.
#' @param coversheet_content Content used to populate the report coversheet.
#'   This should be a named list of data.frames where the names are Excel-style
#'   cell references giving the upper-left corner of each data.frame.
#' @param datasets A named list containing data.frames. The names will be used
#'   as worksheet names in the final report.
#' @param sql_directory A directory containing the SQL files to be used in the
#'   report.
#' @param sql_connection A connection to an SQL database. See [this article from
#'   RStudio](https://db.rstudio.com/getting-started/connect-to-database/) for a
#'   good introduction to the topic.
#' @param transformers Can be either a single function that will be applied to
#'   each dataset or a named list of functions. If the latter:
#'   - Names should correspond to the names of the sql to indicate the tables
#'   the functions should be applied to
#'   - If you supply an unnamed function, this will be applied to each table
#'   that doesn't have a corresponding named function. You can think of this as
#'   a 'default' transformation.
#' @param save_on_complete Set `FALSE` to not save the workbook on completion.
#'   This may be useful, e.g. if you want to apply further manipulations to the
#'   report before saving.
#' @param open_on_complete If `TRUE` then the file will be opened in Excel once
#'   written.
#' @param creator This name will be used to set the 'Creator' and 'Last Modified
#'   By' fields in the workbook.
#' @param cover_template,data_template The names/indices of the worksheets to
#'   use as templates for the coversheet/data sheets. By default these will
#'   respectively be the first and second sheets of the template workbook.
#'
#' @return An `openxlsx` workbook object
#'
#' @rdname report
#'
#' @export
report_from_sql <- function(filename = nice_filename(dir = "Outputs"),
                            file_template = "Excel/template.xlsx",
                            coversheet_content = list(C5 = default_info_table()),
                            sql_directory = "SQL",
                            sql_connection = connect_to_database(),
                            transformers = NULL,
                            save_on_complete = TRUE,
                            open_on_complete = interactive(),
                            creator = get_user(),
                            cover_template = 1,
                            data_template = 2) {

  notify(
    "Creating new Excel report %s", str_extract(filename, "[^/]+$"),
    .type = "h1"
  )

  scripts <- read_sql_scripts(sql_directory)
  datasets <- collect_data(scripts, sql_connection)

  make_quickreport(
    filename           = filename,
    file_template      = file_template,
    coversheet_content = coversheet_content,
    datasets           = datasets,
    transformers       = transformers,
    save_on_complete   = save_on_complete,
    open_on_complete   = open_on_complete,
    creator            = creator,
    cover_template     = cover_template,
    data_template      = data_template
  )

}

#' @rdname report
#' @export
report_from_data <- function(filename = nice_filename(dir = "Outputs"),
                             file_template = "Excel/template.xlsx",
                             coversheet_content = list(C5 = default_info_table()),
                             datasets = NULL,
                             transformers = NULL,
                             save_on_complete = TRUE,
                             open_on_complete = interactive(),
                             creator = get_user(),
                             cover_template = 1,
                             data_template = 2) {

  notify(
    "Creating new Excel report %s", str_extract(filename, "[^/]+$"),
    .type = "h1"
  )

  make_quickreport(
    filename           = filename,
    file_template      = file_template,
    coversheet_content = coversheet_content,
    datasets           = datasets,
    transformers       = transformers,
    save_on_complete   = save_on_complete,
    open_on_complete   = open_on_complete,
    creator            = creator,
    cover_template     = cover_template,
    data_template      = data_template
  )

}

make_quickreport <- function(filename = nice_filename(dir = "Outputs"),
                             file_template = "Excel/template.xlsx",
                             cover_template = 1,
                             data_template = 2,
                             coversheet_content = list(
                               C5 = default_info_table()
                             ),
                             datasets = NULL,
                             transformers = NULL,
                             save_on_complete = TRUE,
                             open_on_complete = interactive(),
                             creator = get_user()) {

  tables <- transform_tables(datasets, transformers)

  wb <- initialise_report(file_template, creator = creator)
  wb <- populate_coversheet(wb, coversheet_content, cover_template)
  wb <- write_data_sheets(wb, tables, data_template)

  if (!save_on_complete) return(wb)

  save_excel_export(wb, filename, replace = TRUE)
  notify("File saved successfully!", .type = "alert_success")

  if (open_on_complete) {
    notify("\nAttempting to open file in Excel...", .type = "verbatim")
    openxlsx::openXL(filename)
  }

  openxlsx::setLastModifiedBy(wb, creator)

  invisible(wb)

}