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: | 2025-02-10 21:28:09 UTC |
Source: | https://github.com/elipousson/openxlsx2Extras |
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"
.
as_data_validation_value(x, allow_blank = TRUE)
as_data_validation_value(x, allow_blank = TRUE)
x |
A character vector of options to allow in cell validation. |
allow_blank |
If |
as_wb()
converts a data frame, a list of data frames, or an Excel file path
to a wbWorkbook object.
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() )
x |
A data frame, a list of data frames, a file path for an Excel file,
or a |
... |
Arguments passed on to
|
sheet_names |
Optional character vector of worksheet names. |
creator |
Creator of the workbook (your name). Defaults to login username or |
title , subject , category , keywords
|
Additional arguments passed to
|
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
|
call |
The execution environment of a currently
running function, e.g. |
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
is a named list of dxfs styles created with
openxlsx2::create_dxfs_style()
with bad, good, and neutral values.
common_dxfs_styles
common_dxfs_styles
An object of class list
of length 3.
https://stackoverflow.com/questions/27611260/what-are-the-rgb-codes-for-the-conditional-formatting-styles-in-excel#comment78058968_27611522
A set of functions to convert between CSV and XLSX formats using flexible input and output options.
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", ... )
file |
Path or paths to input files. For |
new_file |
Path to output file. Optional. If |
.f |
Function used to read or write the csv file. Defaults to
|
... |
Additional arguments passed to |
sheet |
A sheet in the workbook specified by |
ext |
File extension for output file. Defaults to "csv". |
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.
Jordan Mark Barbone [email protected]
Jan Marvin Garbuszus [email protected]
Eli Pousson [email protected]
# 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)
fmt_lgl_cols()
uses vec_fmt_lgl()
to format all (or specified) logical
vector columns to use replacement values.
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") )
.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 |
< |
values |
Default to |
fmt_lgl_cols(data.frame(x = c(TRUE, FALSE, TRUE)))
fmt_lgl_cols(data.frame(x = c(TRUE, FALSE, TRUE)))
marquee::marquee_parse()
to format Markdown textfmt_marquee_txt()
uses marquee::marquee_parse()
and
openxlsx2::fmt_txt()
to format Markdown text.
fmt_marquee_txt( text, ..., style = marquee::classic_style(), ignore_html = TRUE )
fmt_marquee_txt( text, ..., style = marquee::classic_style(), ignore_html = TRUE )
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 |
ignore_html |
Should HTML code be removed from the output |
fmt_marquee_txt( "# ABC abc" )
fmt_marquee_txt( "# ABC abc" )
prep_wb_data()
prepares a data frame for addition to a workbook by handling
list columns and geometry columns (for sf data frames).
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() )
x |
Required. A data frame or an object coercible to a data frame with
|
list_columns |
String, one of "collapse" (default), "drop", or "asis" |
sep |
String to use in collapsing list columns. Ignored unless
|
geometry |
String, one of "drop" (default), "coords", or "wkt". "coords"
uses |
coords |
Length 2 character vector with column names to add if |
call |
The execution environment of a currently
running function, e.g. |
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") }
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.
read_xlsx_ext(file, ..., repair = "unique")
read_xlsx_ext(file, ..., repair = "unique")
file |
An xlsx file, wbWorkbook object or URL to xlsx file. |
... |
Arguments passed on to
|
repair |
Either a string or a function. If a string, it must be one of
The The options |
set_excel_fmt_class(data, cols, fmt_class = "currency", multiple = TRUE)
set_excel_fmt_class(data, cols, fmt_class = "currency", multiple = TRUE)
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 |
multiple |
Whether |
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
vec_fmt_lgl(x, values = c("Y", "N"))
vec_fmt_lgl(x, values = c("Y", "N"))
x |
A logical vector. |
values |
Default to |
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"))
openxlsx2::wb_add_data()
with extra featureswb_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.
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() )
wb |
A Workbook object containing a worksheet. |
x |
Required. A data frame or an object coercible to a data frame with
|
sheet |
The worksheet to write to. Can be the worksheet index or name. |
... |
Arguments passed on to
|
start_row |
A vector specifying the starting row to write |
list_columns |
String, one of "collapse" (default), "drop", or "asis" |
sep |
String to use in collapsing list columns. Ignored unless
|
geometry |
String, one of "drop" (default), "coords", or "wkt". "coords"
uses |
coords |
Length 2 character vector with column names to add if |
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 |
call |
The execution environment of a currently
running function, e.g. |
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)
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.
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" )
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 |
... |
Arguments passed on to
|
cols |
Indices of cols to set/remove column widths. |
widths |
Width to set |
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" )
wb_add_styles()
extends openxlsx2::wb_add_style()
by allowing the
addition of multiple styles as a named list.
wb_add_styles(wb, styles, style_names = NULL)
wb_add_styles(wb, styles, style_names = NULL)
wb |
A workbook |
styles |
Required. List or style xml character, created by a
create_*() function. Passed to |
style_names |
Optional if styles is a named list. |
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.
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() )
sheet_names |
Optional character vector of worksheet names. |
... |
Arguments passed on to
|
default |
Default prefix to use for numbered sheets. Default values are
used if |
creator |
Creator of the workbook (your name). Defaults to login username or |
title , subject , category , keywords
|
Additional arguments passed to
|
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
|
call |
The execution environment of a currently
running function, e.g. |
A wbWorkbook
object.
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"))
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.
wb_save_ext(wb, file = NULL, overwrite = TRUE, ...)
wb_save_ext(wb, file = NULL, overwrite = TRUE, ...)
wb |
A |
file |
A path to save the workbook to |
overwrite |
If |
... |
Arguments passed on to
|
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() })
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.
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 )
wb |
A Workbook object |
... |
Must be empty. |
creator |
Creator of the workbook (your name). Defaults to login username or |
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. |
A workbook with modified properties.
dplyr::group_split
to split a workbook into a list of workbookswb_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.
wb_split( file, .by, ..., .keep = TRUE, properties = "inherit", wb_params = list() )
wb_split( file, .by, ..., .keep = TRUE, properties = "inherit", wb_params = list() )
file |
An xlsx file, wbWorkbook object or URL to xlsx file. |
.by |
Passed to |
... |
Arguments passed on to
|
.keep |
Should the grouping columns be kept? |
properties |
If "inherit" (default) and |
wb_params |
List of additional parameters to pass to |
A list of wbWorkbook objects.
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)
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.
wb_to_df_list(file, sheet_names = NULL, ...)
wb_to_df_list(file, sheet_names = NULL, ...)
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
|
A list of data frame lists.
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_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.
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() )
x |
Required. A |
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 |
title , subject , category , keywords
|
Additional workbook properties passed
to |
datetime_created |
The time of the workbook is created |
theme |
Optional theme identified by string or number. See Details for options. |
as_table |
Default |
start_row |
A vector specifying the starting row to write |
geometry |
String, one of "drop" (default), "coords", or "wkt". "coords"
uses |
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 |
call |
The execution environment of a currently
running function, e.g. |
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() })