In epicentre-msf/qxl: Quick Customized Excel Files

options(digits = 4, width = 120)

qxl: Write quick, customized .xlsx files

A wrapper to the openxlsx package optimized for writing flat data structures. 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

Installation

Install from GitHub with:

# install.packages("remotes")
remotes::install_github("epicentre-msf/qxl")

# if using an older version of remotes pkg, may need to specify branch 'main'
# remotes::install_github("epicentre-msf/qxl@main")

Usage

library(qxl)

# dataset and output path for examples
mtcars_tbl <- tibble::rownames_to_column(mtcars, "model") 
path_write <- tempdir()

Default output

qxl(
  mtcars_tbl,
  file = file.path(path_write, "mtcars_default.xlsx")
)

Conditional formatting

qxl(
  mtcars_tbl,
  file =  file.path(path_write, "mtcars_cond.xlsx"),
  style1 = qstyle(
    rows = cyl >= 6 & mpg > 20,
    # cols = c(cyl, mpg), # can optionally limit to specific columns
    bgFill = "#fddbc7"
  )
)

Data validation

mtcars_tbl$drive <- NA_character_
mtcars_tbl$color <- NA_character_

qxl(
  mtcars_tbl,
  file =  file.path(path_write, "mtcars_valid.xlsx"),
  validate = list(
    drive = c("FWD", "RWD", "AWD", "4WD"),
    color = c("red", "blue", "grey", "black")
  )
)

Instead of passing a list to argument validate, as above, could alternatively use a data frame with variable names in the first column and corresponding options in the second column.

Cell protection

qxl(
  mtcars_tbl,
  file =  file.path(path_write, "mtcars_protect.xlsx"),
  protect = qprotect("my_password", cols = model:carb)
)

Customized header

header_names <- c(
  model = "Model",
  mpg = "Miles per US gallon",
  cyl = "Number of cylinders",
  disp = "Displacement (cu. in.)",
  hp = "Gross horsepower",
  drat = "Rear axle ratio",
  wt = "Weight (1000s of lbs)",
  qsec = "1/4 mile time",
  vs = "Engine type (0 = V-shaped, 1 = straight)",
  am = "Transmission (0 = automatic, 1 = manual)",
  gear = "Number of forward gears",
  carb = "Number of carburators",
  drive = "Drivetrain",
  color = "Color (exterior)"
)

qxl(
  mtcars_tbl,
  file = file.path(path_write, "mtcars_header.xlsx"),
  header = header_names,
  style_head = qstyle(
    rows = 1,
    fontSize = 15,
    fontColour = "#2b8cbe",
    wrapText = TRUE
  ),
  col_widths = 16
)

Note that the second row, which contains the original variable names, is hidden in the output above. To read the file back in we can use the skip argument of readxl::read_xlsx to get the original variable names instead of the human-readable version.

readxl::read_xlsx(file.path(path_write, "mtcars_header.xlsx"), skip = 1)

Alternating row groupings

library(dplyr, warn.conflicts = FALSE)
library(tidyr, warn.conflicts = FALSE)

mtcars_long <- mtcars_tbl %>% 
  dplyr::select(model, mpg, cyl, hp) %>% 
  tidyr::pivot_longer(cols = -model, names_to = "variable")

qxl(
  mtcars_long,
  file = file.path(path_write, "mtcars_groups.xlsx"),
  group = "model"
)

Writing a workbook with multiple sheets

To write a multi-sheet workbook where all sheets have the same (if any) styling, validation, and protection, we can simply pass a list of data frames to qxl.

mtcars_split <- split(mtcars_tbl, mtcars_tbl$am)
names(mtcars_split) <- c("automatic", "manual")

qxl(
  mtcars_split,
  file = file.path(path_write, "mtcars_split.xlsx")
)

If the different worksheets need to vary in their styling, validation, or protection, then we will need multiple calls to qxl, with the workbook argument wb carried forward at each step, which can be done for example with piping.

library(dplyr, warn.conflict = FALSE)

wb_mtcars <- qxl(
  mtcars_split$automatic,
  sheet = "automatic"
) %>% 
  qxl(
    mtcars_split$manual,
    wb = .,
    sheet = "manual",
    # style only for sheet 'manual'
    style1 = qstyle(hp > 200, bgFill = "#9ecae1")
  )

qwrite(
  wb_mtcars,
  file = file.path(path_write, "mtcars_split_custom.xlsx")
)