qxl: Quickly write a tidy data frame to xlsx, with options for...

In epicentre-msf/qxl: Quick Customized Excel Files

Quickly write a tidy data frame to xlsx, with options for customization

Description

A wrapper to the openxlsx package optimized for writing tidy data frames. Includes arguments to quickly add customization like:

• conditional formatting written as R expressions

• data validation rules based on a tidy dictionary structure

• column-specific worksheet protection

• custom column names with original variable-names hidden in the row below

Usage

qxl(
  x,
  file = NULL,
  wb = openxlsx::createWorkbook(),
  sheet = NULL,
  header = NULL,
  style_head = qstyle(rows = 1, textDecoration = "bold"),
  hide_subhead = TRUE,
  style1 = NULL,
  style2 = NULL,
  style3 = NULL,
  style4 = NULL,
  style5 = NULL,
  group,
  group_style = qstyle(bgFill = "#ffcccb"),
  row_heights = NULL,
  col_widths = "auto",
  freeze_row = 1L,
  freeze_col = NULL,
  protect,
  validate = NULL,
  validate_cond = NULL,
  validate_cond_all = NULL,
  filter = FALSE,
  filter_cols = everything(),
  zoom = 120L,
  date_format = "yyyy-mm-dd",
  overwrite = TRUE
)

Arguments

x	A data frame, or list of data frames
file	Filename to write to. If NULL the resulting workbook is returned as an openxlsx object of class "Workbook" rather than written to a file.
wb	An openxlsx workbook object to write to. Defaults to a fresh workbook created with openxlsx::createWorkbook. Only need to update when repeatedly calling qxl() to add worksheets to an existing workbook.
sheet	Optional character vector of worksheet names. If NULL (the default) and x is a named list of data frames, worksheet names are taken from names(x). Otherwise, names default to "Sheet1", "Sheet2", ...
header	Optional column header. Defaults to NULL in which case column names are taken directly from the data frame(s) in x, to create normal single-row headers. Can alternatively pass a named character vector to set custom names as the first row and a subheader with variable names as a hidden second row. header = c( mpg = "Miles per US gallon", cyl = "Number of cylinders", disp = "Engine displacement (cubic in.) )
style_head	Style for the header row. Set with qstyle(), or set to NULL for no header styling. Defaults to bold text.
hide_subhead	Logical indicating whether to hide the subheader (if present). Defaults to TRUE.
style1, style2, style3, style4, style5	Optional style to set using qstyle()
group	Optional vector of one or more column names used to create alternating groupings of rows, with every other row grouping styled as per argument group_style. See section Grouping rows.
group_style	Optional style to apply to alternating groupings of rows, as specified using argument groups. Set using qstyle()
row_heights	Numeric vector of row heights (in Excel units). The vector is recycled if shorter than the number of rows in x. Defaults to NULL to use default row heights.
col_widths	Vector of column widths (in Excel units). Can be numeric or character, and may include keyword "auto" for automatic column sizing. The vector is recycled if shorter than the number of columns in x. Defaults to "auto". Use named vector to give column widths for specific columns, where names represent column names of x or the keyword ".default" to set a default column width for all columns not otherwise specified. E.g. # specify widths for cols mpg and cyl, all others default to "auto" col_widths <- c(mpg = 5, cyl = 10) # specify widths for cols mpg and cyl, and explicit default for all others col_widths <- c(mpg = 5, cyl = 10, .default = 7)
freeze_row	Integer specifying a row to freeze at. Defaults to 1 to add a freeze below the header row. Set to 0 or NULL to omit freezing.
freeze_col	Integer specifying a column to freeze at. Defaults to NULL. Set to 0 or NULL to omit freezing.
protect	Optional function specifying how to protect worksheet components from user modification. See function qprotect.
validate	Optional specification of list-style data validation for one or more columns. Can specify either as a list of vectors giving options for one or more column in x, e.g.: list( var_x = c("Yes", "No"), var_y = c("Small", "Medium", "Large") ) or as a data.frame where the first column gives column names and the second column gives corresponding options, e.g.: data.frame( col = c("var_x", "var_x", "var_y", "var_y", "var_y"), val = c("Yes", "No", "Small", "Medium", "Large") ) Validation options are written/appended to a hidden worksheet named "valid_options".
validate_cond	Optional specification of conditional list-style validation, where the set of values to be allowed in a given column depends on the corresponding value within one or more other columns (e.g. the allowed values in column 'city' depend on the corresponding value in columns 'country' and 'province'). Must be a data.frame with at least two columns, where the first column(s) give the conditional entries (e.g. 'country', 'province') and the last column gives the corresponding allowed entries (e.g. 'city') to be implemented as data validation. The column names in validate_cond should match the relevant columns within x. Note that in the current implementation validation is based on values in the conditional column(s) of x at the time the workbook is written, and will not update in real time if those values are later changed.
validate_cond_all	Optional vector of value(s) to always allow, independent of the value in the conditional column (e.g. "Unknown").
filter	Logical indicating whether to add column filters.
filter_cols	Tidy-selection specifying which columns to filter. Only used if filter is TRUE. Defaults to everything() to select all columns.
zoom	Integer specifying initial zoom percentage. Defaults to 130.
date_format	Excel format for date columns. Defaults to "yyyy-mm-dd".
overwrite	Logical indicating whether to overwrite existing file. Defaults to TRUE

Value

If argument file is not specified, returns an openxlsx workbook object. Otherwise writes workbook to file with no return.

Grouping rows

Given a dataset with multiple rows per group (e.g. repeated observations on a given individual), it can sometimes be useful to uniquely stylize alternating groups to allow for quick visual distinction of the rows belonging to any given group.

Given one or more grouping columns specified using argument groups, the qxl function arranges the rows of the resulting worksheet by group and then applies the style group_style to the rows in every other group, to create an alternating pattern. The alternating pattern is achieved by first creating a group index variable called g which is assigned a value of either 1 or 0: 1 for the 1st group, 0 for the 2nd, 1 for the 3rd, 0 for the 4th, etc. The style specified by group_style is then applied conditionally to rows where g == 0. The grouping variable is written in column A, which is hidden.

Examples

library(datasets)
qxl(mtcars, file = tempfile(fileext = ".xlsx"))

epicentre-msf/qxl documentation built on March 26, 2024, 6:33 p.m.