Package 'openxlsx2Extras'

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

Help Index


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)

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

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.

See Also

Examples

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

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

See Also

wb_add_styles()


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

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)

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

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

Use marquee::marquee_parse() to format Markdown text

Description

[Experimental]

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
)

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

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

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

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

Description

[Experimental]

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

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

[Experimental]

Usage

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

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

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

Description

[Experimental]

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

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)

Add Markdown formatted text to a Workbook

Description

[Experimental]

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

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

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)

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

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.

See Also

as_wb()

Examples

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

[Experimental]

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

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()
})

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
)

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

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)

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

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

Write data to an xlsx file with additional features

Description

[Experimental]

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

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()
})