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] , Jan Marvin Garbuszus [ctb]
Maintainer:	Eli Pousson <[email protected]>
License:	MIT + file LICENSE
Version:	0.0.0.9000
Built:	2025-02-10 21:28:09 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) or "row_before". If "row_before", insert column labels in the row before the column names. `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.
`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`	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


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() but allows use of a name repair argument ("unique" by default) to avoid blank "" or NA values for column names.

Usage

read_xlsx_ext(file, ..., repair = "unique")
read_xlsx_ext(file, ..., repair = "unique")

Arguments

file

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

...

Arguments passed on to openxlsx2::read_xlsx

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.
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.

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)
set_excel_fmt_class(data, cols, fmt_class = "currency", multiple = TRUE)

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.

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"),
  as_table = FALSE,
  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"),
  as_table = FALSE,
  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? `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. `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`	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.
`labels`	Method for handling column labels. "drop" (default) or "row_before". If "row_before", insert column labels in the row before the column names.
`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.
`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

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_workbook() |>
  wb_add_worksheet() |>
  wb_add_marquee_text(
    text = "
# Heading 1

Example text.

~~Strikethrough text~~

## Heading 2

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

library(openxlsx2)

wb_workbook() |>
  wb_add_worksheet() |>
  wb_add_marquee_text(
    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`	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.

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"))

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, ...)
wb_save_ext(wb, file = NULL, overwrite = TRUE, ...)

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.
`...`	Arguments passed on to `openxlsx2::wb_save` `path` Deprecated argument. Please use `file` in new code.

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",
  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",
  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`	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.
`start_row`	A vector specifying the starting row to write `x` 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).
`labels`	Method for handling column labels. "drop" (default) or "row_before". If "row_before", insert column labels in the row before the column names.
`overwrite`	If `FALSE`, will not overwrite when `file` already exists.
`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

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

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

Description

Usage

Arguments

Examples

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