Package 'openxlsx2Extras' reference manual

Title:	Extra Functions for the openxlsx2 package
Description:	Extends the openxlsx2 package with wrapper and helper functions designed to add new features and options when working with Excel workbooks.
Authors:	Eli Pousson [aut, cre, cph] (ORCID: <https://orcid.org/0000-0001-8280-1706>), Jan Marvin Garbuszus [ctb]
Maintainer:	Eli Pousson <[email protected]>
License:	MIT + file LICENSE
Version:	0.0.0.9000
Built:	2026-05-23 07:09:10 UTC
Source:	https://github.com/elipousson/openxlsx2Extras

Prepare a character vector as a data validation string

Description

as_data_validation_value() collapses a character vector as a single string for use as a validation value argument by openxlsx2::wb_add_data_validation() when type = "list".

Usage

as_data_validation_value(x, allow_blank = TRUE)
as_data_validation_value(x, allow_blank = TRUE)

Arguments

x

A character vector of options to allow in cell validation.

allow_blank

If TRUE, add a blank space " " to the returned values.

Coerce a data frame or list of data frames to a workbook

Description

as_wb() converts a data frame, a list of data frames, or an Excel file path to a wbWorkbook object.

Usage

as_wb(
  x,
  ...,
  sheet_names = NULL,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  properties = NULL,
  call = caller_env()
)
as_wb(
  x,
  ...,
  sheet_names = NULL,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  properties = NULL,
  call = caller_env()
)

Arguments

x

A data frame, a list of data frames, a file path for an Excel file, or a wbWorkbook object. A wbWorkbook is returned "as is" ignoring all other parameters. A file path is loaded to a data frame using openxlsx2::wb_to_df().

...

Arguments passed on to wb_add_data_ext

as_table: Default FALSE. If TRUE, use openxlsx2::wb_add_data_table() to add data to workbook. If FALSE, use openxlsx2::wb_add_data(). Additional parameters in ... are passed to one function or the other depending on this value.
labels: Method for handling column labels. "drop" (default), "row_before", or "comments". If "row_before", insert column labels in the row before the column names. If "comments", add column labels as columns on the column names in the start row.
list_columns: String, one of "collapse" (default), "drop", or "asis"
sep: String to use in collapsing list columns. Ignored unless list_columns = "collapse". Defaults to "; ".
geometry: String, one of "drop" (default), "coords", or "wkt". "coords" uses sf::st_centroid() to convert input to POINT geometry, transforms geometry to EPSG:4326, converts geometry to coordinates, and adds new columns with names matching coords. "wkt" converts geometry to a Well Known Text (WKT) character vector using sf::st_as_text() and replaces the existing geometry column (keeping the existing sf column name).
coords: Length 2 character vector with column names to add if geometry = "coords". Must be length 2 in longitude, latitude order.
wb: A Workbook object containing a worksheet.
sheet: The worksheet to write to. Can be the worksheet index or name.
start_row: A vector specifying the starting row to write x to.
na.strings: Value used for replacing NA values from x. Default looks if options(openxlsx2.na.strings) is set. Otherwise na_strings() uses the special ⁠#N/A⁠ value within the workbook.

sheet_names

Optional character vector of worksheet names.

creator

Creator of the workbook (your name). Defaults to login username or options("openxlsx2.creator") if set.

title, subject, category, keywords

Additional arguments passed to openxlsx2::wb_workbook().

datetime_created

The time of the workbook is created

theme

Optional theme identified by string or number. See Details for options.

properties

A named list (typically from openxlsx2::wb_get_properties()) used to set new workbook properties for any values set to NULL. datetime_created defaults to Sys.time() so must be set to NULL to inherit value from properties.

call

The execution environment of a currently running function, e.g. caller_env(). The function will be mentioned in error messages as the source of the error. See the call argument of abort() for more information.

Examples

as_wb(mtcars[1:3, ])

as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

as_wb(mtcars[1:3, ])

as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

Common dxfs styles

Description

common_dxfs_styles is a named list of dxfs styles created with openxlsx2::create_dxfs_style() with bad, good, and neutral values.

Usage

common_dxfs_styles
common_dxfs_styles

Format

An object of class list of length 3.

Source

https://stackoverflow.com/questions/27611260/what-are-the-rgb-codes-for-the-conditional-formatting-styles-in-excel#comment78058968_27611522

Convert CSV to XLSX files or XLSX to CSV

Description

A set of functions to convert between CSV and XLSX formats using flexible input and output options.

Usage

csv_to_wb(file, new_file = NULL, .f = utils::read.csv, ...)

csv_to_xlsx(file, new_file = NULL, .f = utils::read.csv, ...)

xlsx_to_csv(
  file,
  new_file = NULL,
  sheet = 1,
  .f = utils::write.csv,
  ext = "csv",
  ...
)
csv_to_wb(file, new_file = NULL, .f = utils::read.csv, ...)

csv_to_xlsx(file, new_file = NULL, .f = utils::read.csv, ...)

xlsx_to_csv(
  file,
  new_file = NULL,
  sheet = 1,
  .f = utils::write.csv,
  ext = "csv",
  ...
)

Arguments

file

Path or paths to input files. For csv_to_wb(), users can pass multiple CSV files when creating a workbook or xlsx file. If these inputs are named, the names are used as worksheet names.

new_file

Path to output file. Optional. If new_file is not supplied and file is a string, new_file is set to use the same path with a new file extension or (if file is not a string) new_file is set to a temporary file.

.f

Function used to read or write the csv file. Defaults to utils::read.csv for csv_to_wb() and csv_to_xlsx() and utils::write.csv for xlsx_to_csv(). Other functions are allowed but must use the input or output file name as the second argument.

...

Additional arguments passed to .f

sheet

A sheet in the workbook specified by file (either an index or a sheet name). Defaults to 1.

ext

File extension for output file. Defaults to "csv".

Details

These functions allow seamless conversion between CSV and XLSX formats:

csv_to_wb: Reads one or more CSV files and writes them to a workbook object.
csv_to_xlsx: Converts one or more CSV files to a XLSX file.
xlsx_to_csv: Converts an XLSX file to a CSV file.

Author(s)

Jordan Mark Barbone [email protected]

Jan Marvin Garbuszus [email protected]

Eli Pousson [email protected]

Examples

# Create example CSV file
csv <- tempfile(fileext = ".csv")
utils::write.csv(x = mtcars, file = csv)

# Convert CSV to Workbook
wb <- csv_to_wb(file = csv)

# Convert CSV to XLSX
xlsx <- openxlsx2::temp_xlsx()
csv_to_xlsx(file = csv, new_file = xlsx)

# Convert XLSX back to CSV
xlsx_to_csv(file = xlsx, new_file = csv)
# Create example CSV file
csv <- tempfile(fileext = ".csv")
utils::write.csv(x = mtcars, file = csv)

# Convert CSV to Workbook
wb <- csv_to_wb(file = csv)

# Convert CSV to XLSX
xlsx <- openxlsx2::temp_xlsx()
csv_to_xlsx(file = csv, new_file = xlsx)

# Convert XLSX back to CSV
xlsx_to_csv(file = xlsx, new_file = csv)

Format logical vector columns to use replacement values

Description

fmt_lgl_cols() uses vec_fmt_lgl() to format all (or specified) logical vector columns to use replacement values.

Usage

fmt_lgl_cols(
  .data,
  .cols = tidyselect::where(is.logical),
  values = c("Y", "N")
)
fmt_lgl_cols(
  .data,
  .cols = tidyselect::where(is.logical),
  values = c("Y", "N")
)

Arguments

.data

A data frame, data frame extension (e.g. a tibble), or a lazy data frame (e.g. from dbplyr or dtplyr). See Methods, below, for more details.

.cols

<tidy-select> Columns to transform. You can't select grouping columns because they are already automatically handled by the verb (i.e. summarise() or mutate()).

values

Default to c("Y", "N") Length 2 vector where first element replaces TRUE and the second element replaces FALSE.

Examples

fmt_lgl_cols(data.frame(x = c(TRUE, FALSE, TRUE)))
fmt_lgl_cols(data.frame(x = c(TRUE, FALSE, TRUE)))

Use `marquee::marquee_parse()` to format Markdown text

Description

fmt_marquee_txt() uses marquee::marquee_parse() and openxlsx2::fmt_txt() to format Markdown text.

Usage

fmt_marquee_txt(
  text,
  ...,
  style = marquee::classic_style(),
  ignore_html = TRUE
)
fmt_marquee_txt(
  text,
  ...,
  style = marquee::classic_style(),
  ignore_html = TRUE
)

Arguments

text

A character string. The core quality of markdown is that any text is valid markdown so there is no restrictions on the content

...

Ignored at this time. For future use.

style

A style set such as classic_style() that defines how the text should be rendered

ignore_html

Should HTML code be removed from the output

Examples


fmt_marquee_txt(
  "# ABC

  abc"
)

fmt_marquee_txt(
  "# ABC

  abc"
)

Prepare data for adding to a workbook

Description

prep_wb_data() prepares a data frame for addition to a workbook by handling list columns and geometry columns (for sf data frames).

Usage

prep_wb_data(
  x,
  list_columns = c("collapse", "drop", "asis"),
  sep = "; ",
  geometry = c("drop", "coords", "wkt"),
  coords = c("lon", "lat"),
  call = caller_env()
)
prep_wb_data(
  x,
  list_columns = c("collapse", "drop", "asis"),
  sep = "; ",
  geometry = c("drop", "coords", "wkt"),
  coords = c("lon", "lat"),
  call = caller_env()
)

Arguments

x

Required. A data frame or an object coercible to a data frame with base::as.data.frame().

list_columns

String, one of "collapse" (default), "drop", or "asis"

sep

String to use in collapsing list columns. Ignored unless list_columns = "collapse". Defaults to "; ".

geometry

String, one of "drop" (default), "coords", or "wkt". "coords" uses sf::st_centroid() to convert input to POINT geometry, transforms geometry to EPSG:4326, converts geometry to coordinates, and adds new columns with names matching coords. "wkt" converts geometry to a Well Known Text (WKT) character vector using sf::st_as_text() and replaces the existing geometry column (keeping the existing sf column name).

coords

Length 2 character vector with column names to add if geometry = "coords". Must be length 2 in longitude, latitude order.

call

Examples


list_df <- vctrs::data_frame(
  num = 1,
  alpha = list(list("A", "B", "C"))
)

prep_wb_data(list_df)

prep_wb_data(list_df, list_columns = "drop")

prep_wb_data(list_df, list_columns = "asis")

if (rlang::is_installed("sf")) {
  nc <- sf::read_sf(system.file("shape/nc.shp", package = "sf"))

  prep_wb_data(nc, geometry = "coords")

  prep_wb_data(nc, geometry = "wkt")
}

list_df <- vctrs::data_frame(
  num = 1,
  alpha = list(list("A", "B", "C"))
)

prep_wb_data(list_df)

prep_wb_data(list_df, list_columns = "drop")

prep_wb_data(list_df, list_columns = "asis")

if (rlang::is_installed("sf")) {
  nc <- sf::read_sf(system.file("shape/nc.shp", package = "sf"))

  prep_wb_data(nc, geometry = "coords")

  prep_wb_data(nc, geometry = "wkt")
}

Create a data frame from a Workbook (with extra features)

Description

read_xlsx_ext() uses openxlsx2::read_xlsx() with a few added features:

allows use of a name repair argument ("unique_quite" by default) to avoid blank "" or NA values for column names.
allows vector inputs for the file or sheet argument. These parameters are recycled to a common length and result in the return of a data frame list unless the combine = TRUE is set. If combine = TRUE, set names_to (passed to purrr::list_rbind) to combined the file basename values (default) or full path values as a column (depending on the names_from argument). names_from can also be a length > 1 character vector that can be recycled to match the length of file.

Usage

read_xlsx_ext(
  file,
  sheet = 1,
  ...,
  names_from = "basename",
  names_to = rlang::zap(),
  combine = TRUE,
  repair = "unique_quiet"
)
read_xlsx_ext(
  file,
  sheet = 1,
  ...,
  names_from = "basename",
  names_to = rlang::zap(),
  combine = TRUE,
  repair = "unique_quiet"
)

Arguments

file

An xlsx file, wbWorkbook object or URL to xlsx file.

sheet

Defaults to 1.

...

Arguments passed on to openxlsx2::read_xlsx

start_row: first row to begin looking for data.
start_col: first column to begin looking for data.
row_names: If TRUE, the first col of data will be used as row names.
col_names: If TRUE, the first row of data will be used as column names.
skip_empty_rows: If TRUE, empty rows are skipped.
skip_empty_cols: If TRUE, empty columns are skipped.
rows: A numeric vector specifying which rows in the xlsx file to read. If NULL, all rows are read.
cols: A numeric vector specifying which columns in the xlsx file to read. If NULL, all columns are read.
detect_dates: If TRUE, attempt to recognize dates and perform conversion.
na.strings: A character vector of strings which are to be interpreted as NA. Blank cells will be returned as NA.
na.numbers: A numeric vector of digits which are to be interpreted as NA. Blank cells will be returned as NA.
fill_merged_cells: If TRUE, the value in a merged cell is given to all cells within the merge.
named_region: Character string with a named_region (defined name or table). If no sheet is selected, the first appearance will be selected. See wb_get_named_regions()
check_names: If TRUE then the names of the variables in the data frame are checked to ensure that they are syntactically valid variable names.
show_hyperlinks: If TRUE instead of the displayed text, hyperlink targets are shown.

names_to

By default, names(x) are lost. To keep them, supply a string to names_to and the names will be saved into a column with that name. If names_to is supplied and x is not named, the position of the elements will be used instead of the names.

combine

If TRUE, always return a data frames. If FALSE, return a list of data frames.

repair

Either a string or a function. If a string, it must be one of "check_unique", "minimal", "unique", "universal", "unique_quiet", or "universal_quiet". If a function, it is invoked with a vector of minimal names and must return minimal names, otherwise an error is thrown.

Minimal names are never NULL or NA. When an element doesn't have a name, its minimal name is an empty string.
Unique names are unique. A suffix is appended to duplicate names to make them unique.
Universal names are unique and syntactic, meaning that you can safely use the names as variables without causing a syntax error.

The "check_unique" option doesn't perform any name repair. Instead, an error is raised if the names don't suit the "unique" criteria.

The options "unique_quiet" and "universal_quiet" are here to help the user who calls this function indirectly, via another function which exposes repair but not quiet. Specifying repair = "unique_quiet" is like specifying ⁠repair = "unique", quiet = TRUE⁠. When the "*_quiet" options are used, any setting of quiet is silently overridden.

Prepare a data frame with Excel style class values for formatting by openxlsx2

Description

Usage

set_excel_fmt_class(
  data,
  cols,
  fmt_class = "currency",
  multiple = TRUE,
  strict = FALSE
)
set_excel_fmt_class(
  data,
  cols,
  fmt_class = "currency",
  multiple = TRUE,
  strict = FALSE
)

Arguments

data

A data frame with columns to format.

cols

Column names or numbers to modify.

fmt_class

Excel style class, one of: c("currency", "accounting", "hyperlink", "percentage", "scientific", "formula"). Length is recycled to match length of cols using vctrs::vec_recycle().

multiple

Whether arg may contain zero or several values.

strict

If TRUE, error if any character values in cols are not included in the names of data. Default FALSE.

Details

set_excel_fmt_class() applies a style to each specified column. See the openxlsx2 documentation for more information on this feature: https://janmarvin.github.io/openxlsx2/articles/openxlsx2_style_manual.html#numfmts2

Format a logical vector to specified values

Description

Format a logical vector to specified values

Usage

vec_fmt_lgl(x, values = c("Y", "N"))
vec_fmt_lgl(x, values = c("Y", "N"))

Arguments

x

A logical vector.

values

Default to c("Y", "N") Length 2 vector where first element replaces TRUE and the second element replaces FALSE.

Examples

vec_fmt_lgl(c(TRUE, FALSE))

vec_fmt_lgl(c(TRUE, FALSE), c("Yes", "No"))
vec_fmt_lgl(c(TRUE, FALSE))

vec_fmt_lgl(c(TRUE, FALSE), c("Yes", "No"))

Add data using `openxlsx2::wb_add_data()` with extra features

Description

wb_add_data_ext() extends openxlsx2::wb_add_data() to add data to a workbook with special handling for input data with geometry or list columns (using prep_wb_data()) and labelled data.

Usage

wb_add_data_ext(
  wb,
  x,
  sheet = current_sheet(),
  ...,
  start_row = 1,
  list_columns = c("collapse", "drop", "asis"),
  sep = "; ",
  geometry = c("drop", "coords", "wkt"),
  coords = c("lon", "lat"),
  labels = c("drop", "row_before", "comments"),
  as_table = FALSE,
  na.strings = openxlsx2::na_strings(),
  call = caller_env()
)
wb_add_data_ext(
  wb,
  x,
  sheet = current_sheet(),
  ...,
  start_row = 1,
  list_columns = c("collapse", "drop", "asis"),
  sep = "; ",
  geometry = c("drop", "coords", "wkt"),
  coords = c("lon", "lat"),
  labels = c("drop", "row_before", "comments"),
  as_table = FALSE,
  na.strings = openxlsx2::na_strings(),
  call = caller_env()
)

Arguments

wb

A Workbook object containing a worksheet.

x

Required. A data frame or an object coercible to a data frame with base::as.data.frame().

sheet

The worksheet to write to. Can be the worksheet index or name.

...

Arguments passed on to openxlsx2::wb_add_data, openxlsx2::wb_add_data_table

dims: Spreadsheet cell range that will determine start_col and start_row: "A1", "A1:B2", "A:B"
start_col: A vector specifying the starting column to write x to.
array: A bool if the function written is of type array
col_names: If TRUE, column names of x are written.
row_names: If TRUE, the row names of x are written.
with_filter: If TRUE, add filters to the column name row. NOTE: can only have one filter per worksheet.
name: The name of a named region if specified.
apply_cell_style: Should we write cell styles to the workbook
remove_cell_style: keep the cell style?
inline_strings: write characters as inline strings
enforce: enforce that selected dims is filled. For this to work, dims must match x
table_style: Any table style name or "none" (see vignette("openxlsx2_style_manual"))
table_name: Name of table in workbook. The table name must be unique.
first_column: logical. If TRUE, the first column is bold.
last_column: logical. If TRUE, the last column is bold.
banded_rows: logical. If TRUE, rows are color banded.
banded_cols: logical. If TRUE, the columns are color banded.
total_row: logical. With the default FALSE no total row is added.

start_row

A vector specifying the starting row to write x to.

list_columns

String, one of "collapse" (default), "drop", or "asis"

sep

String to use in collapsing list columns. Ignored unless list_columns = "collapse". Defaults to "; ".

geometry

coords

Length 2 character vector with column names to add if geometry = "coords". Must be length 2 in longitude, latitude order.

labels

Method for handling column labels. "drop" (default), "row_before", or "comments". If "row_before", insert column labels in the row before the column names. If "comments", add column labels as columns on the column names in the start row.

as_table

Default FALSE. If TRUE, use openxlsx2::wb_add_data_table() to add data to workbook. If FALSE, use openxlsx2::wb_add_data(). Additional parameters in ... are passed to one function or the other depending on this value.

na.strings

Value used for replacing NA values from x. Default looks if options(openxlsx2.na.strings) is set. Otherwise na_strings() uses the special ⁠#N/A⁠ value within the workbook.

call

Examples

wb <- wb_new_workbook("mtcars")

wb_add_data_ext(wb, mtcars)

wb_add_data_ext(wb, mtcars, as_table = TRUE)

wb <- wb_new_workbook("mtcars")

wb_add_data_ext(wb, mtcars)

wb_add_data_ext(wb, mtcars, as_table = TRUE)

Add Markdown formatted text to a Workbook

Description

wb_add_marquee_text() uses fmt_marquee_txt() to add Markdown formatted text to a Workbook. openxlsx2::wb_set_col_widths() is applied to the workbook to adjust widths for the specified columns. Set widths = NULL to disable this functionality.

Usage

wb_add_marquee_text(
  wb = NULL,
  text,
  sheet = current_sheet(),
  dims = NULL,
  ...,
  cols = 1,
  widths = "auto"
)
wb_add_marquee_text(
  wb = NULL,
  text,
  sheet = current_sheet(),
  dims = NULL,
  ...,
  cols = 1,
  widths = "auto"
)

Arguments

wb

A Workbook object containing a worksheet.

text

A character string. The core quality of markdown is that any text is valid markdown so there is no restrictions on the content

sheet

The worksheet to write to. Can be the worksheet index or name.

dims

Spreadsheet cell range that will determine start_col and start_row: "A1", "A1:B2", "A:B"

...

Arguments passed on to fmt_marquee_txt

style: A style set such as classic_style() that defines how the text should be rendered
ignore_html: Should HTML code be removed from the output

cols

Indices of cols to set/remove column widths.

widths

Width to set cols to specified column width or "auto" for automatic sizing. widths is recycled to the length of cols. openxlsx2 sets the default width is 8.43, as this is the standard in some spreadsheet software. See Details for general information on column widths.

Examples


library(openxlsx2)

wb <- wb_workbook()
wb <- wb_add_worksheet(wb)

wb <- wb_add_marquee_text(
  wb,
  text = "
# Heading 1

Example text.

~~Strikethrough text~~

## Heading 2

- Bulleted list item 1
  - Nested bullet
- Bulleted list item 2"
)

library(openxlsx2)

wb <- wb_workbook()
wb <- wb_add_worksheet(wb)

wb <- wb_add_marquee_text(
  wb,
  text = "
# Heading 1

Example text.

~~Strikethrough text~~

## Heading 2

- Bulleted list item 1
  - Nested bullet
- Bulleted list item 2"
)

Add a named list of styles to a workbook

Description

wb_add_styles() extends openxlsx2::wb_add_style() by allowing the addition of multiple styles as a named list.

Usage

wb_add_styles(wb, styles, style_names = NULL)
wb_add_styles(wb, styles, style_names = NULL)

Arguments

wb

A workbook

styles

Required. List or style xml character, created by a ⁠create_*()⁠ function. Passed to style argument of openxlsx2::wb_add_style().

style_names

Optional if styles is a named list.

Create a new workbook and add named work sheets

Description

wb_new_workbook() is a convenience function wrapping openxlsx2::wb_workbook() and openxlsx2::wb_add_worksheet() to offer quick and easy setup for new workbooks.

Usage

wb_new_workbook(
  sheet_names = NULL,
  ...,
  default = "Sheet",
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  properties = NULL,
  call = caller_env()
)
wb_new_workbook(
  sheet_names = NULL,
  ...,
  default = "Sheet",
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  properties = NULL,
  call = caller_env()
)

Arguments

sheet_names

Optional character vector of worksheet names.

...

Arguments passed on to openxlsx2::wb_add_worksheet

grid_lines: A logical. If FALSE, the worksheet grid lines will be hidden.
row_col_headers: A logical. If FALSE, the worksheet colname and rowname will be hidden.
tab_color: Color of the sheet tab. A wb_color(), a valid color (belonging to grDevices::colors()) or a valid hex color beginning with "#".
zoom: The sheet zoom level, a numeric between 10 and 400 as a percentage. (A zoom value smaller than 10 will default to 10.)
header,odd_header,even_header,first_header,footer,odd_footer,even_footer,first_footer: Character vector of length 3 corresponding to positions left, center, right. header and footer are used to default additional arguments. Setting even, odd, or first, overrides header/footer. Use NA to skip a position.
visible: If FALSE, sheet is hidden else visible.
has_drawing: If TRUE prepare a drawing output (TODO does this work?)
paper_size: An integer corresponding to a paper size. See wb_page_setup() for details.
orientation: One of "portrait" or "landscape"
hdpi,vdpi: Horizontal and vertical DPI. Can be set with options("openxlsx2.dpi" = X), options("openxlsx2.hdpi" = X) or options("openxlsx2.vdpi" = X)

default

Default prefix to use for numbered sheets. Default values are used if sheet_names = NULL or if n_sheets is greater than the length of sheet_names. Defaults to "Sheet".

creator

Creator of the workbook (your name). Defaults to login username or options("openxlsx2.creator") if set.

title, subject, category, keywords

Additional arguments passed to openxlsx2::wb_workbook().

datetime_created

The time of the workbook is created

theme

Optional theme identified by string or number. See Details for options.

properties

call

Value

A wbWorkbook object.

Examples

wb_new_workbook()

wb_new_workbook("Sheet 1")

wb_new_workbook(c("Data", "Analysis"))

wb_new_workbook()

wb_new_workbook("Sheet 1")

wb_new_workbook(c("Data", "Analysis"))

Protect one or more sheets in a workbook

Description

Protect one or more sheets in a workbook

Usage

wb_protect_worksheets(
  wb,
  sheet = NULL,
  protect = TRUE,
  password = NULL,
  properties = c("insertColumns", "insertRows", "deleteColumns", "deleteRows")
)
wb_protect_worksheets(
  wb,
  sheet = NULL,
  protect = TRUE,
  password = NULL,
  properties = c("insertColumns", "insertRows", "deleteColumns", "deleteRows")
)

Arguments

wb

A workbook object

sheet

A name or index of a worksheet

protect

Whether to protect or unprotect the sheet (default=TRUE)

password

(optional) password required to unprotect the worksheet

properties

Defaults to c("insertColumns", "insertRows", "deleteColumns", "deleteRows")

Rename column names in workbook data

Description

Use tidyselect::eval_rename() to rename columns in workbook data.

Usage

wb_rename_data(wb, ..., sheet = 1, start_row = 1, start_col = 1)
wb_rename_data(wb, ..., sheet = 1, start_row = 1, start_col = 1)

Rename workbook sheets using a tidyselect syntax

Description

wb_rename_sheets() and wb_rename_sheets_with() use the tidyselect package to rename sheets.

Usage

wb_rename_sheets(wb, ...)

wb_rename_sheets_with(wb, .fn, .sheets = tidyselect::everything())
wb_rename_sheets(wb, ...)

wb_rename_sheets_with(wb, .fn, .sheets = tidyselect::everything())

Save a workboook object to file while filling file name from assigned workbook title

Description

wb_save_ext() is a helper function extending openxlsx2::wb_save() by filling a missing file name with the workbook title and validating the file extension. This function is not stable and may change in the future.

Usage

wb_save_ext(wb, file = NULL, overwrite = TRUE, flush = FALSE)
wb_save_ext(wb, file = NULL, overwrite = TRUE, flush = FALSE)

Arguments

wb

A wbWorkbook object to write to file

file

A path to save the workbook to

overwrite

If FALSE, will not overwrite when file already exists.

flush

Experimental, streams the worksheet file to disk

Examples


withr::with_tempdir({
  wb <- wb_new_workbook(
    title = "Title used for output file",
    sheet_name = "Sheet 1"
  )

  wb_save_ext(wb)

  fs::dir_ls()
})

withr::with_tempdir({
  wb <- wb_new_workbook(
    title = "Title used for output file",
    sheet_name = "Sheet 1"
  )

  wb_save_ext(wb)

  fs::dir_ls()
})

Set workbook properties

Description

wb_set_properties_ext() extends openxlsx2::wb_set_properties() by adding a properties argument that uses a named list to default default values for existing properties.

Usage

wb_set_properties_ext(
  wb,
  ...,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = NULL,
  datetime_modified = NULL,
  modifier = NULL,
  keywords = NULL,
  comments = NULL,
  manager = NULL,
  company = NULL,
  custom = NULL,
  properties = NULL
)
wb_set_properties_ext(
  wb,
  ...,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = NULL,
  datetime_modified = NULL,
  modifier = NULL,
  keywords = NULL,
  comments = NULL,
  manager = NULL,
  company = NULL,
  custom = NULL,
  properties = NULL
)

Arguments

wb

A Workbook object

...

Must be empty.

creator

Creator of the workbook (your name). Defaults to login username or options("openxlsx2.creator") if set.

title, subject, category, keywords, comments, manager, company

Workbook property, a string.

datetime_created

The time of the workbook is created

datetime_modified

The time of the workbook was last modified

modifier

A character string indicating who was the last person to modify the workbook

custom

A named vector of custom properties added to the workbook

properties

A named character vector or named list of properties used as default values for any other parameter that is not explicitly set.

Value

A workbook with modified properties.

Use `dplyr::group_split` to split a workbook into a list of workbooks

Description

wb_split() uses wb_to_df_list() to extract the data frames from each sheet of a workbook and then split the data frames by a .key argument then convert each new list of data frames back into a wbWorkbook object.

Usage

wb_split(
  file,
  .by,
  ...,
  .keep = TRUE,
  properties = "inherit",
  wb_params = list()
)
wb_split(
  file,
  .by,
  ...,
  .keep = TRUE,
  properties = "inherit",
  wb_params = list()
)

Arguments

file

An xlsx file, wbWorkbook object or URL to xlsx file.

.by

Passed to dplyr::group_split().

...

Arguments passed on to wb_to_df_list

sheet_names: Character vector of sheet names. If not supplied, all sheet names from the supplied workbook are used.

.keep

Should the grouping columns be kept?

properties

If "inherit" (default) and file is a workbook, inherit the workbook list element properties from the existing workbook. properties can also be NULL or a named character vector, a named list, or a bare list of the same length as the number of groups defined using the .by argument.

wb_params

List of additional parameters to pass to map_wb() and as_wb().

Value

A list of wbWorkbook objects.

Examples


wb <- as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

wb_split(wb, .by = carb)

wb <- as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

wb_split(wb, .by = carb)

Convert a workbook to a list of data frames

Description

wb_to_df_list() uses openxlsx2::wb_to_df() to extract each sheet of a workbook object into a data frame. Additional parameters ... are recycled to match the length of sheet names.

Usage

wb_to_df_list(file, sheet_names = NULL, ...)
wb_to_df_list(file, sheet_names = NULL, ...)

Arguments

file

An xlsx file, wbWorkbook object or URL to xlsx file.

sheet_names

Character vector of sheet names. If not supplied, all sheet names from the supplied workbook are used.

...

Arguments passed on to openxlsx2::wb_to_df

sheet: Either sheet name or index. When missing the first sheet in the workbook is selected.
start_row: first row to begin looking for data.
start_col: first column to begin looking for data.
row_names: If TRUE, the first col of data will be used as row names.
col_names: If TRUE, the first row of data will be used as column names.
skip_empty_rows: If TRUE, empty rows are skipped.
skip_empty_cols: If TRUE, empty columns are skipped.
skip_hidden_rows: If TRUE, hidden rows are skipped.
skip_hidden_cols: If TRUE, hidden columns are skipped.
rows: A numeric vector specifying which rows in the xlsx file to read. If NULL, all rows are read.
cols: A numeric vector specifying which columns in the xlsx file to read. If NULL, all columns are read.
detect_dates: If TRUE, attempt to recognize dates and perform conversion.
na.strings: A character vector of strings which are to be interpreted as NA. Blank cells will be returned as NA.
na.numbers: A numeric vector of digits which are to be interpreted as NA. Blank cells will be returned as NA.
fill_merged_cells: If TRUE, the value in a merged cell is given to all cells within the merge.
dims: Character string of type "A1:B2" as optional dimensions to be imported.
show_formula: If TRUE, the underlying Excel formulas are shown.
convert: If TRUE, a conversion to dates and numerics is attempted.
types: A named numeric indicating, the type of the data. Names must match the returned data. See Details for more.
named_region: Character string with a named_region (defined name or table). If no sheet is selected, the first appearance will be selected. See wb_get_named_regions()
keep_attributes: If TRUE additional attributes are returned. (These are used internally to define a cell type.)
check_names: If TRUE then the names of the variables in the data frame are checked to ensure that they are syntactically valid variable names.
show_hyperlinks: If TRUE instead of the displayed text, hyperlink targets are shown.

Value

A list of data frame lists.

Examples

wb <- as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

wb_to_df_list(wb)

wb_to_df_list(wb, "Sheet 1")

wb <- as_wb(list(mtcars[1:3, ], mtcars[4:6, ]))

wb_to_df_list(wb)

wb_to_df_list(wb, "Sheet 1")

Write data to an xlsx file with additional features

Description

write_xlsx_ext() wraps wb_add_data_ext() to provide an equivalent to openxlsx2::write_xlsx() with additional features. Arguments passed to openxlsx2::wb_workbook() are ignored if x is a workbook instead of a data frame.

Usage

write_xlsx_ext(
  x,
  file = NULL,
  ...,
  sheet_names = NULL,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  as_table = FALSE,
  start_row = 1,
  geometry = "drop",
  labels = "drop",
  na.strings = openxlsx2::na_strings(),
  overwrite = TRUE,
  call = caller_env()
)
write_xlsx_ext(
  x,
  file = NULL,
  ...,
  sheet_names = NULL,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  as_table = FALSE,
  start_row = 1,
  geometry = "drop",
  labels = "drop",
  na.strings = openxlsx2::na_strings(),
  overwrite = TRUE,
  call = caller_env()
)

Arguments

x

Required. A wbWorkbook object, a data frame, or a bare list of data frames. x can also be any object coercible to a data frame (other than a bare list) by base::as.data.frame(). If x is a named list and sheet_names is supplied, the existing names for x are ignored.

file

A path to save the workbook to

...

additional arguments

sheet_names

Optional character vector of worksheet names.

creator

Creator of the workbook (your name). Defaults to login username or options("openxlsx2.creator") if set.

title, subject, category, keywords

Additional workbook properties passed to wb_new_workbook(). Ignored (with creator and title) if x is a workbook instead of a data frame.

datetime_created

The time of the workbook is created

theme

Optional theme identified by string or number. See Details for options.

as_table

start_row

A vector specifying the starting row to write x to.

geometry

labels

na.strings

Value used for replacing NA values from x. Default looks if options(openxlsx2.na.strings) is set. Otherwise na_strings() uses the special ⁠#N/A⁠ value within the workbook.

overwrite

If FALSE, will not overwrite when file already exists.

call

Examples

withr::with_tempdir({
  # Write data frame to XLSX file
  write_xlsx_ext(mtcars, "mtcars.xlsx")

  # Write data frame to XLSX file with workbook title
  write_xlsx_ext(mtcars, title = "mtcars data")

  # Write list of data frames to XLSX file with named sheets
  write_xlsx_ext(
    list(mtcars = mtcars, anscombe = anscombe),
    "datasets-list.xlsx"
  )

  # List output files
  fs::dir_ls()
})

withr::with_tempdir({
  # Write data frame to XLSX file
  write_xlsx_ext(mtcars, "mtcars.xlsx")

  # Write data frame to XLSX file with workbook title
  write_xlsx_ext(mtcars, title = "mtcars data")

  # Write list of data frames to XLSX file with named sheets
  write_xlsx_ext(
    list(mtcars = mtcars, anscombe = anscombe),
    "datasets-list.xlsx"
  )

  # List output files
  fs::dir_ls()
})

Package 'openxlsx2Extras'

Help Index

Prepare a character vector as a data validation string

Description

Usage

Arguments

Coerce a data frame or list of data frames to a workbook

Description

Usage

Arguments

See Also

Examples

Common dxfs styles

Description

Usage

Format

Source

See Also

Convert CSV to XLSX files or XLSX to CSV

Description

Usage

Arguments

Details

Author(s)

Examples

Format logical vector columns to use replacement values

Description

Usage

Arguments

Examples

Use marquee::marquee_parse() to format Markdown text

Description

Usage

Arguments

Examples

Prepare data for adding to a workbook

Description

Usage

Arguments

Examples

Create a data frame from a Workbook (with extra features)

Description

Usage

Arguments

Prepare a data frame with Excel style class values for formatting by openxlsx2

Description

Usage

Arguments

Details

Format a logical vector to specified values

Description

Usage

Arguments

Examples

Add data using openxlsx2::wb_add_data() with extra features

Description

Usage

Arguments

Examples

Add Markdown formatted text to a Workbook

Description

Usage

Arguments

Examples

Add a named list of styles to a workbook

Description

Usage

Arguments

Create a new workbook and add named work sheets

Description

Usage

Arguments

Value

See Also

Examples

Protect one or more sheets in a workbook

Description

Usage

Arguments

Rename column names in workbook data

Use `marquee::marquee_parse()` to format Markdown text

Add data using `openxlsx2::wb_add_data()` with extra features

Use `dplyr::group_split` to split a workbook into a list of workbooks