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]
Maintainer: Eli Pousson <[email protected]>
License: MIT + file LICENSE
Version: 0.0.0.9000
Built: 2024-11-11 02:23:33 UTC
Source: https://github.com/elipousson/openxlsx2Extras

Help Index

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

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

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 sf input objects and labelled data.

Usage

wb_add_data_ext(
  wb,
  x,
  ...,
  start_row = 1,
  geometry = c("drop", "coords", "wkt"),
  labels = c("drop", "row_before"),
  call = caller_env()
)

Arguments

wb

A Workbook object containing a worksheet.
x

Object to be written. For classes supported look at the examples.
...

Arguments passed on to openxlsx2::wb_add_data

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"

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.

sep

Only applies to list columns. The separator used to collapse list columns to a character vector e.g. sapply(x$list_column, paste, collapse = sep).

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
start_row

A vector specifying the starting row to write x to.
geometry

String, one of "drop" (default), "coords", or "wkt". Both "coords" and "wkt" are not yet supported.
labels

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

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

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

Description

[Experimental]

wb_save_ext() is a helper function that fills in the file name when saving based on the XSLX title. 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

library(openxlsx2)

withr::with_tempdir({
  wb_workbook(
    title = "Title used for output file"
  ) |>
    wb_add_worksheet() |>
    wb_save_ext()

  fs::dir_ls()
})

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 better support for sf and labelled data. 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,
  as_table = FALSE,
  ...,
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  theme = NULL,
  keywords = NULL,
  start_row = 1,
  overwrite = TRUE,
  geometry = c("drop", "coords", "wkt"),
  labels = c("drop", "row_before"),
  call = caller_env()
)

Arguments

x

An object or a list of objects that can be handled by wb_add_data() to write to file.
file

An optional xlsx file name. If no file is passed, the object is not written to disk and only a workbook object is returned.
as_table

If TRUE, will write as a data table, instead of data.
...

Arguments passed on to wb_workbook, wb_add_worksheet, wb_add_data_table, wb_add_data, wb_freeze_pane, wb_set_col_widths, wb_save

creator

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

sheet

A name for the new worksheet

grid_lines

A logical. If FALSE, the worksheet grid lines 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.)

total_row

logical. With the default FALSE no total row is added.

start_col

A vector specifying the starting column to write x to.

start_row

A vector specifying the starting row to write x to.

col_names

If TRUE, column names of x are written.

row_names

If TRUE, the row names of x are written.

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.

first_active_row

Top row of active region

first_active_col

Furthest left column of active region

first_row

If TRUE, freezes the first row (equivalent to first_active_row = 2)

first_col

If TRUE, freezes the first column (equivalent to first_active_col = 2)

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.

overwrite

If FALSE, will not overwrite when file already exists.
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 openxlsx2::wb_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.
start_row

A vector specifying the starting row to write x to.
overwrite

If FALSE, will not overwrite when file already exists.
geometry

String, one of "drop" (default), "coords", or "wkt". Both "coords" and "wkt" are not yet supported.
labels

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