options(digits = 4, width = 120)
A wrapper to the openxlsx package optimized for writing flat data structures. Includes arguments to quickly add customization like:
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")
library(qxl) # dataset and output path for examples mtcars_tbl <- tibble::rownames_to_column(mtcars, "model") path_write <- tempdir()
qxl( mtcars_tbl, file = file.path(path_write, "mtcars_default.xlsx") )
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" ) )
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.
qxl( mtcars_tbl, file = file.path(path_write, "mtcars_protect.xlsx"), protect = qprotect("my_password", cols = model:carb) )
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)
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" )
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") )
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.