R/output_xlsx.R
datasetsXLSX.RdFunction to export multiple datasets and/or figures from R to a workbook. The function creates an index sheet, separate worksheets for each input object, hyperlinks to each content worksheet, and an optional sheet for long-form metadata.
datasetsXLSX(
file,
datasets,
sheetname = NULL,
title = NULL,
source = NULL,
metadata = NULL,
grouplines = NULL,
group_names = NULL,
plot_width = NULL,
plot_height = NULL,
index_title = NA,
index_source = NA,
logo = NA,
contactdetails = NA,
homepage = NA,
openinghours = NA,
auftrag_id = NULL,
author = "user",
metadata_sheet = NULL,
overwrite = TRUE,
config = "default"
)file name of the xlsx-file. The extension ".xlsx" is added
A list of an arbitrary number of data.frames, ggplot objects, and file paths for images in the order in which they should appear in the output file.
Names of individual worksheets in output file. Note that these names will be truncated to 31 characters, must be unique, and cannot contain some special characters (namely the following: /, \, ?, *, :, [, ]).
Titles shown at the top of the different worksheets.
A list of sources for the different elements of `datasets`. Elements of this list can also be character vectors to insert more than one source.
A list containing metadata for each element of `datasets`. Elements of this list can also be character vectors to insert more than one source.
A list containing vectors of indices/names of columns at the beginning of a group.
A list of character vectors containing the names of the groups as defined in `grouplines`. Should not be specified unless grouplines is also specified.
Either a single numeric value denoting the width of all included plots in inches (1 inch = 2.54 cm), or a list of the same length as `datasets`
Either a single numeric value denoting the height of all included plots in inches (1 inch = 2.54 cm), or a list of the same length as `datasets`
Title to be put on the index sheet.
Source to be shown below the index title.
File path to the logo to be included in the index-sheet. Defaults to the logo of the Statistical Office of Kanton Zurich. This can either be overridden with a path to an image file, or configured in a user profile.
Character vector with contact information to be displayed
on the title sheet. By default uses inputHelperContactInfo() to
construct it based on values defined in the active user configuration.
Homepage of data publisher. Default can be adjusted via user configuration.
A character vector with office hours. Defaults to NULL ( no output).
An ID associated with the Excel file. Defaults to NULL ( no output).
defaults to the last two letters (initials) or numbers of the internal user name.
A list with named elements 'title', 'source', and 'text'. Intended for conveying long-form information. Default is NULL, not included.
Overwrites the existing excel files with the same file name. default to TRUE
which config file should be used. Default: default
The arguments datasets, sheetnames, titles, sources, and metadata
should be of equal length. For more complex workbooks, it may be more
convenient to parametrize these for each individual input using functions
like add_sheetname(), which can be chained using
All elements in datasets must be of type data.frame, ggplot, or character. In the latter case, the inputs must correspond to paths to existing image files.
library(dplyr)
library(statR)
library(ggplot2)
# Example with two datasets and one figure - legacy method
fig <- ggplot2::ggplot(mtcars, ggplot2::aes(x = disp)) +
ggplot2::geom_histogram()
if (FALSE) {
datasetsXLSX(file = tempfile(fileext = ".xlsx"),
datasets = list(mtcars, PlantGrowth, fig),
title = c("mtcars-Datensatz",
"PlantGrowth-Datensatz",
"Histogramm"),
plot_width = c(5),
plot_height = c(5),
source = list(
paste(
"Source: Henderson and Velleman (1981).",
"Building multiple regression models",
"interactively. Biometrics, 37, 391–411."),
paste(
"Source: Dobson, A. J. (1983) An Introduction",
"to Statistical Modelling.",
"London: Chapman and Hall."),
NULL),
metadata = list(
"Bemerkungen zum mtcars-Datensatz: x",
"Bemerkungen zum PlantGrowth-Datensatz: x",
NULL),
sheetname = c("Autos","Blumen", "Histogramm"),
index_title = "Autos und Pflanzen",
auftrag_id = "A2021_0000",
overwrite = TRUE)
}
# Newer method
df <- mtcars %>%
add_sheetname("cars") %>%
add_title("Cars dataset") %>%
add_source(paste("Henderson and Velleman (1981). Building multiple",
"regression models interactively.",
"Biometrics, 37, 391–411.")) %>%
add_metadata("Obtained in R by calling 'mtcars'") %>%
add_grouplines(c(2, 5, 8)) %>%
add_group_names(c("Group1", "Group2", "Group3"))
df2 <- airquality %>%
add_sheetname("airquality") %>%
add_title("Airquality") %>%
add_metadata("Obtained in R by calling 'airquality'")
plt <- (ggplot(mtcars) + geom_histogram(aes(x = cyl))) %>%
add_sheetname("Histogram") %>%
add_title("A histogram") %>%
add_source("mtcars data from R package 'datasets'") %>%
add_plot_size(c(6,3))
metadata_sheet <- list(
title = "Title of the metadata sheet",
source = "A reference to the responsible organization or similar",
text = c("The metadatasheet is intended for universally applicable",
"long-form explanations which don't fit neatly above the data.",
"",
"Each element is printed in a new row."))
if (FALSE) {
datasetsXLSX(
file = tempfile(fileext = ".xlsx"),
datasets = list(df, df2, plt),
metadata_sheet = metadata_sheet,
overwrite = TRUE)
}