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.

report_from_sql(
  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
)

report_from_data(
  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
)

Arguments

filename	The name of the file to create
file_template	A filepath pointing to an Excel workbook to use as a template. See 'Details' for more information.
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.
sql_directory	A directory containing the SQL files to be used in the report.
sql_connection	A connection to an SQL database. See this article from RStudio for a good introduction to the topic.
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.
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.
open_on_complete	If TRUE then the file will be opened in Excel once written.
creator	This name will be used to set the 'Creator' and 'Last Modified By' fields in the workbook.
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.
datasets	A named list containing data.frames. The names will be used as worksheet names in the final report.

Value

An openxlsx workbook object

Details

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").