Type:	Package
Title:	Read, Write and Edit 'xlsx' Files
Version:	1.24
Language:	en-US
Description:	Simplifies the creation of 'xlsx' files by providing a high level interface to writing, styling and editing worksheets.
License:	MIT + file LICENSE
URL:	https://janmarvin.github.io/openxlsx2/, https://github.com/JanMarvin/openxlsx2
BugReports:	https://github.com/JanMarvin/openxlsx2/issues
Depends:	R (≥ 3.5.0)
Imports:	R6, Rcpp, grDevices, stringi, utils
LinkingTo:	Rcpp
Suggests:	ggplot2, knitr, mschart (≥ 0.4), openssl, rmarkdown, rvg, testthat (≥ 3.0.0), zip
VignetteBuilder:	knitr
Encoding:	UTF-8
RoxygenNote:	7.3.3
Config/testthat/edition:	3
NeedsCompilation:	yes
Packaged:	2026-02-20 19:47:01 UTC; jmg
Author:	Jordan Mark Barbone [aut], Jan Marvin Garbuszus [aut, cre], Olivier Roy [ctb], openxlsx authors [cph] (openxlsx package), Arseny Kapoulkine [ctb, cph] (Author of included pugixml code)
Maintainer:	Jan Marvin Garbuszus <jan.garbuszus@ruhr-uni-bochum.de>
Repository:	CRAN
Date/Publication:	2026-02-21 06:10:15 UTC

xlsx reading, writing and editing.

Description

This R package is a modern reinterpretation of the widely used popular openxlsx package. Similar to its predecessor, it simplifies the creation of xlsx files by providing a clean interface for writing, designing and editing worksheets. Based on a powerful XML library and focusing on modern programming flows in pipes or chains, openxlsx2 allows to break many new ground.

Details

The openxlsx2 package provides comprehensive functionality for interacting with Office Open XML spreadsheet files. Users can read data using read_xlsx() and write data to spreadsheets via write_xlsx(), with options to specify sheet names and cell ranges for targeted operations. Beyond basic read/write capabilities, openxlsx2 facilitates extensive workbook (wb_workbook()) manipulations, including:

• Loading a workbook into R with wb_load() and saving it with wb_save()

• Adding/removing and modifying worksheets and data with wb_add_worksheet(), wb_remove_worksheet(), and wb_add_data().

• Enhancing spreadsheets with comments (wb_add_comment()), images (wb_add_image()), plots (wb_add_plot()), charts (wb_add_mschart()), and pivot tables (wb_add_pivot_table()). Customizing cell styles using fonts (wb_add_font()), number formats (wb_add_numfmt()), backgrounds (wb_add_fill()), and alignments (wb_add_cell_style()). Inserting custom text strings with fmt_txt() and creating comprehensive table styles with create_tablestyle().

Interaction

Interaction with openxlsx2 objects can occur through two primary methods:

Wrapper Function Method: Utilizes the wb family of functions that support piping to streamline operations.

wb <- wb_workbook(creator = "My name here") |>
  wb_add_worksheet(sheet = "Expenditure", grid_lines = FALSE) |>
  wb_add_data(x = USPersonalExpenditure, row_names = TRUE)

Chaining Method: Directly modifies the object through a series of chained function calls.

wb <- wb_workbook(creator = "My name here")$
  add_worksheet(sheet = "Expenditure", grid_lines = FALSE)$
  add_data(x = USPersonalExpenditure, row_names = TRUE)

While wrapper functions require explicit assignment of their output to reflect changes, chained functions inherently modify the input object. Both approaches are equally supported, offering flexibility to suit user preferences. The documentation mainly highlights the use of wrapper functions. To find information, users should look up the wb function name e.g. ?wb_add_data_table rather than searching for ?wbWorkbook.

Function arguments follow the snake_case convention, but for backward compatibility, camelCase is also supported at the moment. The API aims to maintain consistency in its arguments, with a special focus on sheet (wb_get_sheet_names()) and dims (wb_dims), which are of particular importance to users.

Locale

By default, openxlsx2 uses the American English word for color (written with 'o' instead of the British English 'ou'). However, both spellings are supported. So where the documentation uses a 'color', the function should also accept a 'colour'. However, this is not indicated by the autocompletion.

Numeric Precision

R typically uses IEEE 754 double-precision floating-point numbers. When openxlsx2 writes these R numeric values to a spreadsheet, it stores them using double precision (and shortens if possible) within the file structure. However, spreadsheet software operates with, displays, and performs calculations using only about 15 significant decimal digits. If spreadsheet software encounters a number with higher precision (like one written by openxlsx2), it will typically round that number to 15 significant digits for its internal use and display. Conversely, when reading numeric data from an xlsx/xlsm file, openxlsx2 reads the stored double-precision value. However, potential discrepancies can still arise. If the number was originally created or calculated within spreadsheet software and exceeded 15 digits, it may have already rounded before saving. Additionally, subtle rounding differences can sometimes occur during the XML-to-numeric conversion when reading the file into R. Expect minor variations, especially in the least significant digits, and avoid direct equality (==) comparisons; check for differences within a small tolerance instead.

Supported files

Supported input files include xlsx, xlsm, and xlsb. The xlsx and xlsm formats are fully supported. The key difference between these two formats is that xlsm files may contain a binary blob that stores VBA code, while xlsx files do not.

Support for the xlsb format is more limited. A custom parser is used to convert the binary format into a pseudo-XML structure that can be loaded into a wbWorkbook. This allows xlsb files to be handled similarly to other workbook formats. However, the parser does not fully implement the entire xlsb specification. It provides functionality to read worksheets into data frames and to save the content as an xlsx or xlsm file for comparison of values and formulas. Some components, such as pivot tables and conditional formatting, are not currently parsed. Writing xlsb files is not supported, and big endian support is very limited. At present, there are no plans to extend support for xlsb files further, due to the complexity of the format. It is recommended to use spreadsheet software to convert files to or from the xlsb format when necessary.

The XML-based formats (xlsx and xlsm) are fully supported. Loading, modifying, and saving these files with minimal unintended changes is a core project goal. Nevertheless, the XML format can be fragile. Modifying cells that interact with formulas, named objects such as tables or regions, slicers, or pivot tables can lead to unexpected behavior. It is therefore strongly advised to keep backups of important files and to regularly verify the output using appropriate spreadsheet software.

Authors and contributions

For a full list of all authors that have made this package possible and for whom we are grateful, please see:

system.file("AUTHORS", package = "openxlsx2")

If you feel like you should be included on this list, please let us know. If you have something to contribute, you are welcome. If something is not working as expected, open issues or if you have solved an issue, open a pull request. Please be respectful and be aware that we are volunteers doing this for fun in our unpaid free time. We will work on problems when we have time or need.

License

This package is licensed under the MIT license and is based on openxlsx (by Alexander Walker and Philipp Schauberger; COPYRIGHT 2014-2022) and pugixml (by Arseny Kapoulkine; COPYRIGHT 2006-2025). Both released under the MIT license.

Author(s)

Maintainer: Jan Marvin Garbuszus jan.garbuszus@ruhr-uni-bochum.de

Authors:

• Jordan Mark Barbone jmbarbone@gmail.com (ORCID)

Other contributors:

• Olivier Roy [contributor]

• openxlsx authors (openxlsx package) [copyright holder]

• Arseny Kapoulkine (Author of included pugixml code) [contributor, copyright holder]

See Also

• browseVignettes("openxlsx2")

• https://janmarvin.github.io/openxlsx2/

• https://janmarvin.github.io/ox2-book/ for examples

Examples

# read xlsx or xlsm files
path <- system.file("extdata/openxlsx2_example.xlsx", package = "openxlsx2")
read_xlsx(path)

# or import workbooks
wb <- wb_load(path)

# read a data frame
wb_to_df(wb)

# and save
temp <- temp_xlsx()
if (interactive()) wb_save(wb, temp)

## or create one yourself
wb <- wb_workbook()
# add a worksheet
wb$add_worksheet("sheet")
# add some data
wb$add_data("sheet", cars)
# open it in your default spreadsheet software
if (interactive()) wb$open()

Modify the state of active and selected sheets in a workbook

Description

Get and set table of sheets and their state as selected and active in a workbook

Multiple sheets can be selected, but only a single one can be active (visible). The visible sheet, must not necessarily be a selected sheet.

Usage

wb_get_active_sheet(wb)

wb_set_active_sheet(wb, sheet)

wb_get_selected(wb)

wb_set_selected(wb, sheet)

Arguments

Value

a data frame with tabSelected and names

Examples

wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))
# testing is the selected sheet
wb_get_selected(wb)
# change the selected sheet to Sheet2
wb <- wb_set_selected(wb, "Sheet2")
# get the active sheet
wb_get_active_sheet(wb)
# change the selected sheet to Sheet2
wb <- wb_set_active_sheet(wb, sheet = "Sheet2")

Format Values using OOXML (Spreadsheet) Number Format Codes

Description

This function emulates a spreadsheet formatting engine. It takes numeric, date, or character values and applies an OOXML format code to produce a formatted string. It supports standard number formatting, date/time, elapsed durations, fractions, and conditional formatting sections.

Usage

apply_numfmt(value, format_code)

Arguments

Details

The function splits the format_code into up to four sections separated by semicolons (Positive; Negative; Zero; Text).

• Date and Time: Supports standard tokens (yyyy, mm, dd, hh, ss) and AM/PM toggles.

• Durations: Supports elapsed time tokens in square brackets, such as ⁠[h]⁠, ⁠[m]⁠, and ⁠[s]⁠, calculating total units.

• HMS Handling: Character strings in "HH:MM:SS" format are automatically coerced to a date-time object using a base date to allow clock and duration formatting.

• Numbers: Supports thousands separators (⁠,⁠), precision, percentage conversion, and scientific notation (⁠E+⁠).

• Fractions: Uses the Farey algorithm to approximate decimals as fractions (e.g., ⁠?/?⁠, ⁠# ?/?⁠).

• Conditionals: Evaluates bracketed conditions like ⁠[<1000]⁠ to select the appropriate formatting section.

• Literals: Handles escaped characters (⁠\\⁠), quoted text ("text"), and the text placeholder (@).

Rounding is based on the R function round() and does not match spreadsheet software behavior.

Value

A character vector of formatted strings.

Examples

# Numeric formatting
apply_numfmt(1234.5678, "#,##0.00") # "1,234.57"

# Date and Time
apply_numfmt("2025-01-05", "dddd, mmm dd") # "Sunday, Jan 05"
apply_numfmt("13:45:30", "hh:mm AM/PM")    # "01:45 PM"

# Durations
apply_numfmt("1900-01-12 08:17:47", "[h]:mm:ss") # "296:17:47"

# Fractions
apply_numfmt(1.75, "# ?/?") # "1 3/4"

loads character string to pugixml and returns an externalptr

Description

loads character string to pugixml and returns an externalptr

Usage

as_xml(x, ...)

Arguments

Details

might be useful for larger documents where single nodes are shortened and otherwise the full tree has to be reimported. unsure where we have such a case. is useful, for printing nodes from a larger tree, that have been exported as characters (at some point in time we have to convert the xml to R)

Examples

tmp_xlsx <- tempfile()
xlsxFile <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
unzip(xlsxFile, exdir = tmp_xlsx)

wb <- wb_load(xlsxFile)
styles_xml <- sprintf("%s/xl/styles.xml", tmp_xlsx)

# is external pointer
sxml <- read_xml(styles_xml)

# is character
font <- xml_node(sxml, "styleSheet", "fonts", "font")

# is again external pointer
as_xml(font)

Set the default font in a workbook

Description

Modify / get the default font for the workbook. This will alter the latin major and minor font in the workbooks theme.

Usage

wb_set_base_font(
  wb,
  font_size = 11,
  font_color = wb_color(theme = "1"),
  font_name = "Aptos Narrow",
  ...
)

wb_get_base_font(wb)

Arguments

Details

The font name is not validated in anyway. Spreadsheet software replaces unknown font names with system defaults.

The default base font is Aptos Narrow, black, size 11. If font_name differs from the name in wb_get_base_font(), the theme is updated to use the newly selected font name.

See Also

Other workbook styling functions: wb_add_dxfs_style(), wb_add_style(), wb_base_colors

Other workbook wrappers: col_widths-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Examples

## create a workbook
wb <- wb_workbook(theme = "Office 2013 - 2022 Theme")
wb$add_worksheet("S1")
## modify base font to size 10 Aptos Narrow in red
wb$set_base_font(font_size = 10, font_color = wb_color("red"), font_name = "Aptos Narrow")

wb$add_data(x = iris)

## font color does not affect tables
wb$add_data_table(x = iris, dims = wb_dims(from_col = 10))

## get the base font
wb_get_base_font(wb)

Clean worksheet name

Description

Cleans a worksheet name by removing legal characters.

Usage

clean_worksheet_name(x, replacement = " ")

Arguments

Details

Illegal characters are considered ⁠\⁠, /, ⁠?⁠, *, :, [, and ⁠]⁠. These must be intentionally removed from worksheet names prior to creating a new worksheet.

Value

x with bad characters removed

Convert spreadsheet column notation to integers

Description

col2int() transforms spreadsheet-style column identifiers (e.g., "A", "B", "AA") into their corresponding integer indices. This utility is fundamental for programmatic data manipulation, where "A" is mapped to 1, "B" to 2, and "ZZ" to 702.

Usage

col2int(x)

Arguments

Details

The function is designed to handle various input formats encountered during spreadsheet data processing. In addition to single column labels, it supports range notation using the colon operator (e.g., "A:C"). When a range is detected, the function internally expands the notation into a complete sequence of integers (e.g., 1, 2, 3). This behavior is particularly useful when passing column selections to functions like wb_to_df() or wb_read().

Input validation ensures that only atomic vectors are processed. If the input is already numeric or a factor, the function ensures the values fall within the valid spreadsheet column range before coercion to integers. Note that the presence of NA values in the input will trigger an error to maintain data integrity during index calculation.

Value

An integer vector representing the column indices. Returns NULL if the input x is NULL, or an empty integer vector if the length of x is zero.

Notes

• Range expansion via : is performed iteratively until all sequences are resolved into individual integer components.

• In compliance with spreadsheet software standards, the function validates that indices do not exceed the maximum allowable column limit.

See Also

int2col()

Examples

# Convert standard labels
col2int(c("A", "B", "Z"))

# Convert ranges to integer sequences
col2int("A:C")

# Mix individual columns and ranges
col2int(c("A", "C:E", "G"))

# Handle numeric inputs
col2int(c(1, 2, 26))

Modify column widths of a worksheet

Description

Remove / set worksheet column widths to specified width or "auto".

Usage

wb_set_col_widths(
  wb,
  sheet = current_sheet(),
  cols,
  widths = 8.43,
  hidden = FALSE
)

wb_remove_col_widths(wb, sheet = current_sheet(), cols)

Arguments

Details

The global minimum and maximum column width for "auto" columns are controlled by:

• options("openxlsx2.minWidth" = 3)

• options("openxlsx2.maxWidth" = 250) (the maximum width allowed in OOXML)

Automatic column width calculation is a heuristic that may not be accurate in all scenarios. Known limitations include issues with wrapped text, merged cells, and font styles with variable kerning. The underlying logic primarily assumes a monospace font and provides limited support for specific number formats. As a safeguard to avoid very narrow columns, widths calculated below the openxlsx2.minWidth (or if unset, below 4) threshold are slightly increased.

Be aware that calculating widths can be computationally slow for large worksheets. Additionally, the hidden parameter is linked with settings in wb_group_cols(), so changing one will update the other. Because default column widths are influenced by the specific spreadsheet software, operating system, and DPI settings, even providing specific values for widths does not guarantee perfectly consistent output across all environments.

For automatic text wrapping of columns use wb_add_cell_style(wrap_text = TRUE)

See Also

Other workbook wrappers: base_font-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Other worksheet content functions: filter-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

## Create a new workbook
wb <- wb_workbook()

## Add a worksheet
wb$add_worksheet("Sheet 1")

## set col widths
wb$set_col_widths(cols = c(1, 4, 6, 7, 9), widths = c(16, 15, 12, 18, 33))

## auto columns
wb$add_worksheet("Sheet 2")
wb$add_data(sheet = 2, x = iris)
wb$set_col_widths(sheet = 2, cols = 1:5, widths = "auto")

## removing column widths
## Create a new workbook
wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))

## remove column widths in columns 1 to 20
wb_remove_col_widths(wb, 1, cols = 1:20)

Internal comment functions

Description

Users are advised to use wb_add_comment() and wb_remove_comment(). write_comment() and remove_comment() are now deprecated. openxlsx2 will stop exporting it at some point in the future. Use the replacement functions.

Usage

write_comment(
  wb,
  sheet,
  col = NULL,
  row = NULL,
  comment,
  dims = rowcol_to_dim(row, col),
  color = NULL,
  file = NULL
)

remove_comment(
  wb,
  sheet,
  col = NULL,
  row = NULL,
  gridExpand = TRUE,
  dims = NULL
)

Arguments

Examples

wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
# add a comment without author
c1 <- wb_comment(text = "this is a comment", author = "")
wb$add_comment(dims = "B10", comment = c1)
#' # Remove comment
wb$remove_comment(sheet = "Sheet 1", dims = "B10")
# Write another comment with author information
c2 <- wb_comment(text = "this is another comment", author = "Marco Polo", visible = TRUE)
wb$add_comment(sheet = 1, dims = "C10", comment = c2)
# Works with formatted text also.
formatted_text <- fmt_txt("bar", underline = TRUE)
wb$add_comment(dims = "B5", comment = formatted_text)
# With background color
wb$add_comment(dims = "B7", comment = formatted_text, color = wb_color("green"))
# With background image. File extension must be png or jpeg, not jpg?
tmp <- tempfile(fileext = ".png")
png(file = tmp, bg = "transparent")
plot(1:10)
rect(1, 5, 3, 7, col = "white")
dev.off()

c1 <- wb_comment(text = "this is a comment", author = "", visible = TRUE)
wb$add_comment(dims = "B12", comment = c1, file = tmp)

create consecutive dims from column and row vector

Description

create consecutive dims from column and row vector

Usage

con_dims(cols, rows)

Arguments

consecutive range in vector

Description

consecutive range in vector

Usage

con_rng(x)

Arguments

Defunct: Convert to spreadsheet data

Description

Use convert_to_excel_date().

Arguments

Convert from spreadsheet date, datetime or hms number to R Date type

Description

Convert from spreadsheet date number to R Date type

Usage

convert_date(x, origin = "1900-01-01")

convert_datetime(x, origin = "1900-01-01", tz = "UTC")

convert_hms(x)

Arguments

Details

Spreadsheet software stores dates as number of days from some origin day

Setting the timezone in convert_datetime() will alter the value. If users expect a datetime value in a specific timezone, they should try e.g. lubridate::force_tz.

Value

A date, datetime, or hms.

See Also

wb_add_data()

Examples

# date --
## 2014 April 21st to 25th
convert_date(c(41750, 41751, 41752, 41753, 41754, NA))
convert_date(c(41750.2, 41751.99, NA, 41753))

# datetime --
##  2014-07-01, 2014-06-30, 2014-06-29
x <- c(41821.8127314815, 41820.8127314815, NA, 41819, NaN)
convert_datetime(x)
convert_datetime(x, tz = "Australia/Perth")
convert_datetime(x, tz = "UTC")

# hms ---
## 12:13:14
x <- 0.50918982
convert_hms(x)

convert back to an Excel Date

Description

convert back to an Excel Date

Usage

convert_to_excel_date(df, date1904 = FALSE)

Arguments

Examples

 xlsxFile <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
 wb1 <- wb_load(xlsxFile)
 df <- wb_to_df(wb1)
 # conversion is done on dataframes only
 convert_to_excel_date(df = df["Var5"], date1904 = FALSE)

Create border format

Description

This function creates border styles for a cell in a spreadsheet. Border styles can be any of the following: "none", "thin", "medium", "dashed", "dotted", "thick", "double", "hair", "mediumDashed", "dashDot", "mediumDashDot", "dashDotDot", "mediumDashDotDot", "slantDashDot". Border colors can be created with wb_color().

Usage

create_border(
  diagonal_down = "",
  diagonal_up = "",
  outline = "",
  bottom = NULL,
  bottom_color = NULL,
  diagonal = NULL,
  diagonal_color = NULL,
  end = "",
  horizontal = "",
  left = NULL,
  left_color = NULL,
  right = NULL,
  right_color = NULL,
  start = "",
  top = NULL,
  top_color = NULL,
  vertical = "",
  start_color = NULL,
  end_color = NULL,
  horizontal_color = NULL,
  vertical_color = NULL,
  ...
)

Arguments

Value

A formatted border object to be used in a spreadsheet.

See Also

wb_add_border()

Other style creating functions: create_cell_style(), create_colors_xml(), create_dxfs_style(), create_fill(), create_font(), create_numfmt(), create_tablestyle()

Examples

# Create a border with a thick bottom and thin top
border <- create_border(
  bottom = "thick",
  bottom_color = wb_color("FF0000"),
  top = "thin",
  top_color = wb_color("00FF00")
)

Create cell style

Description

This function creates a cell style for a spreadsheet, including attributes such as borders, fills, fonts, and number formats.

Usage

create_cell_style(
  border_id = "",
  fill_id = "",
  font_id = "",
  num_fmt_id = "",
  pivot_button = "",
  quote_prefix = "",
  xf_id = "",
  horizontal = "",
  indent = "",
  justify_last_line = "",
  reading_order = "",
  relative_indent = "",
  shrink_to_fit = "",
  text_rotation = "",
  vertical = "",
  wrap_text = "",
  ext_lst = "",
  hidden = "",
  locked = "",
  ...
)

Arguments

Details

A single cell style can make use of various other styles like border, fill, and font. These styles are independent of the cell style and must be registered with the style manager separately. This allows multiple cell styles to share a common font type, for instance. The used style elements are passed to the cell style via their IDs. An example of this can be seen below. The number format can be a custom one created by create_numfmt(), or a built-in style from the formats table below.

Value

A formatted cell style object to be used in a spreadsheet.

See Also

wb_add_cell_style()

Other style creating functions: create_border(), create_colors_xml(), create_dxfs_style(), create_fill(), create_font(), create_numfmt(), create_tablestyle()

Examples

foo_fill <- create_fill(pattern_type = "lightHorizontal",
                        fg_color = wb_color("blue"),
                        bg_color = wb_color("orange"))
foo_font <- create_font(sz = 36, b = TRUE, color = wb_color("yellow"))

wb <- wb_workbook()
wb$styles_mgr$add(foo_fill, "foo")
wb$styles_mgr$add(foo_font, "foo")

foo_style <- create_cell_style(
  fill_id = wb$styles_mgr$get_fill_id("foo"),
  font_id = wb$styles_mgr$get_font_id("foo")
)

Create custom color xml schemes

Description

Create custom color themes that can be used with wb_set_base_colors(). The color input will be checked with wb_color(), so it must be either a color R from grDevices::colors() or a hex value. Default values for the dark argument are: black, white, darkblue and lightgray. For the accent argument, the six inner values of grDevices::palette(). The link argument uses blue and purple by default for active and visited links.

Usage

create_colors_xml(name = "Base R", dark = NULL, accent = NULL, link = NULL)

Arguments

See Also

Other style creating functions: create_border(), create_cell_style(), create_dxfs_style(), create_fill(), create_font(), create_numfmt(), create_tablestyle()

Examples

colors <- create_colors_xml()
wb <- wb_workbook()$add_worksheet()$set_base_colors(xml = colors)

Create a comment

Description

Use wb_comment() in new code. See openxlsx2-deprecated

Usage

create_comment(
  text,
  author = Sys.info()[["user"]],
  style = NULL,
  visible = TRUE,
  width = 2,
  height = 4
)

Arguments

Value

a wbComment object

Create a custom formatting style

Description

Create a new style to apply to worksheet cells. These styles are used in conditional formatting and in (pivot) table styles.

Usage

create_dxfs_style(
  font_name = NULL,
  font_size = NULL,
  font_color = NULL,
  num_fmt = NULL,
  border = NULL,
  border_color = wb_color(getOption("openxlsx2.borderColor", "black")),
  border_style = getOption("openxlsx2.borderStyle", "thin"),
  bg_fill = NULL,
  fg_color = NULL,
  gradient_fill = NULL,
  text_bold = NULL,
  text_strike = NULL,
  text_italic = NULL,
  text_underline = NULL,
  ...
)

Arguments

Details

It is possible to override border_color and border_style with {left, right, top, bottom}_color, {left, right, top, bottom}_style.

Value

A dxfs style node

See Also

wb_add_style() wb_add_dxfs_style()

Other style creating functions: create_border(), create_cell_style(), create_colors_xml(), create_fill(), create_font(), create_numfmt(), create_tablestyle()

Examples

# do not apply anything
style1 <- create_dxfs_style()

# change font color and background color
style2 <- create_dxfs_style(
  font_color = wb_color(hex = "FF9C0006"),
  bg_fill = wb_color(hex = "FFFFC7CE")
)

# change font (type, size and color) and background
# the old default in openxlsx and openxlsx2 <= 0.3
style3 <- create_dxfs_style(
  font_name = "Aptos Narrow",
  font_size = 11,
  font_color = wb_color(hex = "FF9C0006"),
  bg_fill = wb_color(hex = "FFFFC7CE")
)

## See package vignettes for further examples

Create fill pattern

Description

This function creates fill patterns for a cell in a spreadsheet. Fill patterns can be simple solid colors or more complex gradient fills. For certain pattern types, two colors are needed.

Usage

create_fill(
  gradient_fill = "",
  pattern_type = "",
  bg_color = NULL,
  fg_color = NULL,
  ...
)

Arguments

Value

A formatted fill pattern object to be used in a spreadsheet.

See Also

wb_add_fill()

Other style creating functions: create_border(), create_cell_style(), create_colors_xml(), create_dxfs_style(), create_font(), create_numfmt(), create_tablestyle()

Examples

# Create a solid fill pattern with foreground color
fill <- create_fill(
  pattern_type = "solid",
  fg_color = wb_color(hex = "FFFF0000")
)

Create font format

Description

This function creates font styles for a cell in a spreadsheet. It allows customization of various font properties including bold, italic, color, size, underline, and more.

Usage

create_font(
  b = "",
  charset = "",
  color = wb_color(hex = "FF000000"),
  condense = "",
  extend = "",
  family = "2",
  i = "",
  name = "Aptos Narrow",
  outline = "",
  scheme = "minor",
  shadow = "",
  strike = "",
  sz = "11",
  u = "",
  vert_align = "",
  ...
)

Arguments

Value

A formatted font object to be used in a spreadsheet.

See Also

wb_add_font()

Other style creating functions: create_border(), create_cell_style(), create_colors_xml(), create_dxfs_style(), create_fill(), create_numfmt(), create_tablestyle()

Examples

# Create a font with bold and italic styles
font <- create_font(
  b = TRUE,
  i = TRUE,
  color = wb_color(hex = "FF00FF00"),
  name = "Arial",
  sz = "12"
)

# openxml has the alpha value leading
hex8 <- unlist(xml_attr(read_xml(font), "font", "color"))
hex8 <- paste0("#", substr(hex8, 3, 8), substr(hex8, 1, 2))

# # write test color
# col <- crayon::make_style(col2rgb(hex8, alpha = TRUE))
# cat(col("Test"))

Create spreadsheet hyperlink string

Description

Wrapper to create internal hyperlink string to pass to wb_add_formula(). Either link to external URLs or local files or straight to cells of local xlsx sheets.

Note that for an external URL, only file and text should be supplied. You can supply dims to wb_add_formula() to control the location of the link.

Usage

create_hyperlink(sheet, row = 1, col = 1, text = NULL, file = NULL)

Arguments

See Also

wb_add_hyperlink()

Examples

wb <- wb_workbook()$
  add_worksheet("Sheet1")$add_worksheet("Sheet2")$add_worksheet("Sheet3")

## Internal Hyperlink - create hyperlink formula manually
x <- '=HYPERLINK(\"#Sheet2!B3\", "Text to Display - Link to Sheet2")'
wb$add_formula(sheet = "Sheet1", x = x, dims = "A1")

## Internal - No text to display using create_hyperlink() function
x <- create_hyperlink(sheet = "Sheet3", row = 1, col = 2)
wb$add_formula(sheet = "Sheet1", x = x, dims = "A2")

## Internal - Text to display
x <- create_hyperlink(sheet = "Sheet3", row = 1, col = 2,text = "Link to Sheet 3")
wb$add_formula(sheet = "Sheet1", x = x, dims = "A3")

## Link to file - No text to display
fl <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
x <- create_hyperlink(sheet = "Sheet1", row = 3, col = 10, file = fl)
wb$add_formula(sheet = "Sheet1", x = x, dims = "A4")

## Link to file - Text to display
fl <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
x <- create_hyperlink(sheet = "Sheet2", row = 3, col = 10, file = fl, text = "Link to File.")
wb$add_formula(sheet = "Sheet1", x = x, dims = "A5")

## Link to external file - Text to display
x <- '=HYPERLINK("[C:/Users]", "Link to an external file")'
wb$add_formula(sheet = "Sheet1", x = x, dims = "A6")

x <- create_hyperlink(text = "test.png", file = "D:/somepath/somepicture.png")
wb$add_formula(x = x, dims = "A7")


## Link to an URL.
x <- create_hyperlink(text = "openxlsx2 website", file = "https://janmarvin.github.io/openxlsx2/")

wb$add_formula(x = x, dims = "A8")
# if (interactive()) wb$open()

Create number format

Description

This function creates a number format for a cell in a spreadsheet. Number formats define how numeric values are displayed, including dates, times, currencies, percentages, and more.

Usage

create_numfmt(numFmtId = 164, formatCode = "#,##0.00")

Arguments

Value

A formatted number format object to be used in a spreadsheet.

See Also

wb_add_numfmt()

Other style creating functions: create_border(), create_cell_style(), create_colors_xml(), create_dxfs_style(), create_fill(), create_font(), create_tablestyle()

Examples

# Create a number format for currency
numfmt <- create_numfmt(
  numFmtId = 164,
  formatCode = "$#,##0.00"
)

Helper to create a shape

Description

Helper to create a shape

Usage

create_shape(
  shape = "rect",
  name = "shape 1",
  text = "",
  fill_color = NULL,
  fill_transparency = 0,
  text_color = NULL,
  text_transparency = 0,
  line_color = fill_color,
  line_transparency = 0,
  text_align = "left",
  rotation = 0,
  id = 1,
  ...
)

Arguments

Details

Possible shapes are (from ST_ShapeType - Preset Shape Types): "line", "lineInv", "triangle", "rtTriangle", "rect", "diamond", "parallelogram", "trapezoid", "nonIsoscelesTrapezoid", "pentagon", "hexagon", "heptagon", "octagon", "decagon", "dodecagon", "star4", "star5", "star6", "star7", "star8", "star10", "star12", "star16", "star24", "star32", "roundRect", "round1Rect", "round2SameRect", "round2DiagRect", "snipRoundRect", "snip1Rect", "snip2SameRect", "snip2DiagRect", "plaque", "ellipse", "teardrop", "homePlate", "chevron", "pieWedge", "pie", "blockArc", "donut", "noSmoking", "rightArrow", "leftArrow", "upArrow", "downArrow", "stripedRightArrow", "notchedRightArrow", "bentUpArrow", "leftRightArrow", "upDownArrow", "leftUpArrow", "leftRightUpArrow", "quadArrow", "leftArrowCallout", "rightArrowCallout", "upArrowCallout", "downArrowCallout", "leftRightArrowCallout", "upDownArrowCallout", "quadArrowCallout", "bentArrow", "uturnArrow", "circularArrow", "leftCircularArrow", "leftRightCircularArrow", "curvedRightArrow", "curvedLeftArrow", "curvedUpArrow", "curvedDownArrow", "swooshArrow", "cube", "can", "lightningBolt", "heart", "sun", "moon", "smileyFace", "irregularSeal1", "irregularSeal2", "foldedCorner", "bevel", "frame", "halfFrame", "corner", "diagStripe", "chord", "arc", "leftBracket", "rightBracket", "leftBrace", "rightBrace", "bracketPair", "bracePair", "straightConnector1", "bentConnector2", "bentConnector3", "bentConnector4", "bentConnector5", "curvedConnector2", "curvedConnector3", "curvedConnector4", "curvedConnector5", "callout1", "callout2", "callout3", "accentCallout1", "accentCallout2", "accentCallout3", "borderCallout1", "borderCallout2", "borderCallout3", "accentBorderCallout1", "accentBorderCallout2", "accentBorderCallout3", "wedgeRectCallout", "wedgeRoundRectCallout", "wedgeEllipseCallout", "cloudCallout", "cloud", "ribbon", "ribbon2", "ellipseRibbon", "ellipseRibbon2", "leftRightRibbon", "verticalScroll", "horizontalScroll", "wave", "doubleWave", "plus", "flowChartProcess", "flowChartDecision", "flowChartInputOutput", "flowChartPredefinedProcess", "flowChartInternalStorage", "flowChartDocument", "flowChartMultidocument", "flowChartTerminator", "flowChartPreparation", "flowChartManualInput", "flowChartManualOperation", "flowChartConnector", "flowChartPunchedCard", "flowChartPunchedTape", "flowChartSummingJunction", "flowChartOr", "flowChartCollate", "flowChartSort", "flowChartExtract", "flowChartMerge", "flowChartOfflineStorage", "flowChartOnlineStorage", "flowChartMagneticTape", "flowChartMagneticDisk", "flowChartMagneticDrum", "flowChartDisplay", "flowChartDelay", "flowChartAlternateProcess", "flowChartOffpageConnector", "actionButtonBlank", "actionButtonHome", "actionButtonHelp", "actionButtonInformation", "actionButtonForwardNext", "actionButtonBackPrevious", "actionButtonEnd", "actionButtonBeginning", "actionButtonReturn", "actionButtonDocument", "actionButtonSound", "actionButtonMovie", "gear6", "gear9", "funnel", "mathPlus", "mathMinus", "mathMultiply", "mathDivide", "mathEqual", "mathNotEqual", "cornerTabs", "squareTabs", "plaqueTabs", "chartX", "chartStar", "chartPlus"

Value

a character containing the XML

See Also

wb_add_drawing()

Examples

 wb <- wb_workbook()$add_worksheet()$
   add_drawing(xml = create_shape())

Create a sparklines object

Description

create_sparklines() defines a set of sparklines. Compact, word-sized graphics that reside within a single cell. These are ideal for showing trends in a series of values, such as seasonal increases or decreases, or economic cycles, directly alongside the data.

Usage

create_sparklines(
  sheet = current_sheet(),
  dims,
  sqref,
  type = NULL,
  negative = NULL,
  display_empty_cells_as = "gap",
  markers = NULL,
  high = NULL,
  low = NULL,
  first = NULL,
  last = NULL,
  color_series = wb_color(hex = "FF376092"),
  color_negative = wb_color(hex = "FFD00000"),
  color_axis = wb_color(hex = "FFD00000"),
  color_markers = wb_color(hex = "FFD00000"),
  color_first = wb_color(hex = "FFD00000"),
  color_last = wb_color(hex = "FFD00000"),
  color_high = wb_color(hex = "FFD00000"),
  color_low = wb_color(hex = "FFD00000"),
  manual_max = NULL,
  manual_min = NULL,
  line_weight = NULL,
  date_axis = NULL,
  display_x_axis = NULL,
  display_hidden = NULL,
  min_axis_type = NULL,
  max_axis_type = NULL,
  right_to_left = NULL,
  direction = NULL,
  ...
)

Arguments

Details

Sparklines are added to a workbook in "groups." A group shares the same visual properties (type, colors, line weight, and axis settings). Within a group, multiple individual sparklines are defined by pairing a data range (dims) with a destination cell (sqref).

Types of Sparklines:

• NULL (Default): A standard line chart.

• "column": A small column chart.

• "stacked": Often referred to as a "Win/Loss" chart, where each data point is represented by a block indicating a positive or negative value.

Directionality: The direction argument determines how the dims range is parsed. If you provide a multi-cell range like "A1:E10" as data for 10 sparklines, direction = "row" will treat each row as a separate data series, while direction = "col" will treat each column as a series.

Value

A character string containing the XML structure for the sparkline group.

Examples

# create multiple sparklines
sparklines <- c(
  create_sparklines("Sheet 1", "A3:L3", "M3", type = "column", first = "1"),
  create_sparklines("Sheet 1", "A2:L2", "M2", markers = "1"),
  create_sparklines("Sheet 1", "A4:L4", "M4", type = "stacked", negative = "1"),
  create_sparklines("Sheet 1", "A5:L5;A7:L7", "M5;M7", markers = "1")
)

t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))

wb <- wb_workbook()$
  add_worksheet("Sheet 1")$
  add_data(x = t2)$
  add_sparklines(sparklines = sparklines)

# create sparkline groups
sparklines <- c(
  create_sparklines("Sheet 2", "A2:L6;", "M2:M6", markers = "1"),
  create_sparklines(
    "Sheet 2", "A7:L7;A9:L9", "M7;M9", type = "stacked", negative = "1"
  ),
  create_sparklines(
    "Sheet 2", "A8:L8;A10:L13", "M8;M10:M13",
    type = "column", first = "1"
   ),
  create_sparklines(
    "Sheet 2", "A2:L13", "A14:L14", type = "column", first = "1",
    direction = "col"
  )
)

wb <- wb$
  add_worksheet("Sheet 2")$
  add_data(x = t2)$
  add_sparklines(sparklines = sparklines)

Create custom (pivot) table styles

Description

Create a custom (pivot) table style. These functions are for expert use only. Use other styling functions instead.

Usage

create_tablestyle(
  name,
  whole_table = NULL,
  header_row = NULL,
  total_row = NULL,
  first_column = NULL,
  last_column = NULL,
  first_row_stripe = NULL,
  second_row_stripe = NULL,
  first_column_stripe = NULL,
  second_column_stripe = NULL,
  first_header_cell = NULL,
  last_header_cell = NULL,
  first_total_cell = NULL,
  last_total_cell = NULL,
  ...
)

create_pivottablestyle(
  name,
  whole_table = NULL,
  header_row = NULL,
  grand_total_row = NULL,
  first_column = NULL,
  grand_total_column = NULL,
  first_row_stripe = NULL,
  second_row_stripe = NULL,
  first_column_stripe = NULL,
  second_column_stripe = NULL,
  first_header_cell = NULL,
  first_subtotal_column = NULL,
  second_subtotal_column = NULL,
  third_subtotal_column = NULL,
  first_subtotal_row = NULL,
  second_subtotal_row = NULL,
  third_subtotal_row = NULL,
  blank_row = NULL,
  first_column_subheading = NULL,
  second_column_subheading = NULL,
  third_column_subheading = NULL,
  first_row_subheading = NULL,
  second_row_subheading = NULL,
  third_row_subheading = NULL,
  page_field_labels = NULL,
  page_field_values = NULL,
  ...
)

Arguments

See Also

Other style creating functions: create_border(), create_cell_style(), create_colors_xml(), create_dxfs_style(), create_fill(), create_font(), create_numfmt()

Modify creators of a workbook

Description

Modify and get workbook creators

Usage

wb_add_creators(wb, creators)

wb_set_creators(wb, creators)

wb_remove_creators(wb, creators)

wb_get_creators(wb)

Arguments

Value

• wb_set_creators(), wb_add_creators(), and wb_remove_creators() return the wbWorkbook object

• wb_get_creators() returns a character vector of creators

See Also

Other workbook wrappers: base_font-wb, col_widths-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Examples

# workbook made with default creator (see [wbWorkbook])
wb <- wb_workbook()
wb_get_creators(wb)

# add a new creator (assuming "test" isn't your default creator)
wb <- wb_add_creators(wb, "test")
wb_get_creators(wb)

# remove the creator (should be the same as before)
wb <- wb_remove_creators(wb, "test")
wb_get_creators(wb)

Create dimensions from dataframe

Description

Use wb_dims()

Usage

dataframe_to_dims(df, dim_break = FALSE)

Arguments

Examples

 df <- dims_to_dataframe("A1:D5;F1:F6;D8", fill = TRUE)
 dataframe_to_dims(df)

Defunct: Delete data

Description

This function is deprecated. Use wb_clean_sheet().

Usage

delete_data(wb, sheet, cols, rows)

Arguments

Helper functions to work with dims

Description

Internal helpers to (de)construct a dims argument from/to a row and column vector. Exported for user convenience.

Usage

dims_to_rowcol(x, as_integer = FALSE)

validate_dims(x)

rowcol_to_dims(row, col, single = TRUE, fix = NULL)

Arguments

Value

• A dims string for ⁠_to_dim⁠ i.e "A1:A1"

• A named list of rows and columns for to_rowcol

See Also

wb_dims()

Examples

dims_to_rowcol("A1:J10")
wb_dims(1:10, 1:10)

Create dataframe from dimensions

Description

Non consecutive decreasing dims will return an increasing data frame.

Usage

dims_to_dataframe(dims, fill = FALSE, empty_rm = FALSE, cc = NULL)

Arguments

Examples

dims_to_dataframe("A1:B2")

Add/remove column filters in a worksheet

Description

Add or remove spreadsheet column filters to a worksheet

Usage

wb_add_filter(wb, sheet = current_sheet(), rows, cols)

wb_remove_filter(wb, sheet = current_sheet())

Arguments

Details

Adds filters to worksheet columns, same as with_filter = TRUE in wb_add_data() wb_add_data_table() automatically adds filters to first row of a table.

NOTE Can only have a single filter per worksheet unless using tables.

See Also

wb_add_data(), wb_add_data_table()

Other worksheet content functions: col_widths-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
wb$add_worksheet("Sheet 2")
wb$add_worksheet("Sheet 3")

wb$add_data(1, iris)
wb$add_filter(1, row = 1, cols = seq_along(iris))

## Equivalently
wb$add_data(2, x = iris, with_filter = TRUE)

## Similarly
wb$add_data_table(3, iris)
wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
wb$add_worksheet("Sheet 2")
wb$add_worksheet("Sheet 3")

wb$add_data(1, iris)
wb_add_filter(wb, 1, row = 1, cols = seq_along(iris))

## Equivalently
wb$add_data(2, x = iris, with_filter = TRUE)

## Similarly
wb$add_data_table(3, iris)

## remove filters
wb_remove_filter(wb, 1:2) ## remove filters
wb_remove_filter(wb, 3) ## Does not affect tables!

format strings independent of the cell style.

Description

format strings independent of the cell style.

Usage

fmt_txt(
  x,
  bold = FALSE,
  italic = FALSE,
  underline = FALSE,
  strike = FALSE,
  size = NULL,
  color = NULL,
  font = NULL,
  charset = NULL,
  outline = NULL,
  vert_align = NULL,
  family = NULL,
  shadow = NULL,
  condense = NULL,
  extend = NULL,
  ...
)

## S3 method for class 'fmt_txt'
x + y

## S3 method for class 'fmt_txt'
as.character(x, ...)

## S3 method for class 'fmt_txt'
print(x, ...)

Arguments

Details

The result is an xml string. It is possible to paste multiple fmt_txt() strings together to create a string with differing styles. It is possible to supply different underline styles to underline.

Using fmt_txt(charset = 161) will give the Greek Character Set

You can join additional objects into fmt_txt() objects using "+". Though be aware that fmt_txt("sum:") + (2 + 2) is different to fmt_txt("sum:") + 2 + 2.

See Also

create_font()

Examples

fmt_txt("bar", underline = TRUE)
fmt_txt("foo ", bold = TRUE) + fmt_txt("bar")
as.character(fmt_txt(2))

Group rows and columns in a worksheet

Description

Group a selection of rows or cols

Usage

wb_group_cols(
  wb,
  sheet = current_sheet(),
  cols,
  collapsed = FALSE,
  levels = NULL
)

wb_ungroup_cols(wb, sheet = current_sheet(), cols)

wb_group_rows(
  wb,
  sheet = current_sheet(),
  rows,
  collapsed = FALSE,
  levels = NULL
)

wb_ungroup_rows(wb, sheet = current_sheet(), rows)

Arguments

Details

If row was previously hidden, it will now be shown. Columns can be added using A1 notion, so cols = 2:3 is similar to cols = "B:C". It is possible to add nested groups, so ⁠cols = list("3" = list(1:2, 3:4)⁠ is also possible. Depending on the selected summary column either left or right will be selected for grouping, this can be changed in wb_set_page_setup().

See Also

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Other worksheet content functions: col_widths-wb, filter-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Examples

# create matrix
t1 <- AirPassengers
t2 <- do.call(cbind, split(t1, cycle(t1)))
dimnames(t2) <- dimnames(.preformat.ts(t1))

wb <- wb_workbook()
wb$add_worksheet("AirPass")
wb$add_data("AirPass", t2, row_names = TRUE)

# groups will always end on/show the last row. in the example 1950, 1955, and 1960
wb <- wb_group_rows(wb, "AirPass", 2:3, collapsed = TRUE) # group years < 1950
wb <- wb_group_rows(wb, "AirPass", 4:8, collapsed = TRUE) # group years 1951-1955
wb <- wb_group_rows(wb, "AirPass", 9:13)                  # group years 1956-1960

wb <- wb_group_cols(wb, "AirPass", 2:4, collapsed = TRUE)
wb <- wb_group_cols(wb, "AirPass", 5:7, collapsed = TRUE)
wb <- wb_group_cols(wb, "AirPass", 8:10, collapsed = TRUE)
wb <- wb_group_cols(wb, "AirPass", 11:13)

### create grouping levels
grp_rows <- list(
  "1" = seq(2, 3),
  "2" = seq(4, 8),
  "3" = seq(9, 13)
)

grp_cols <- list(
  "1" = seq(2, 4),
  "2" = seq(5, 7),
  "3" = seq(8, 10),
  "4" = seq(11, 13)
)

wb <- wb_workbook()
wb$add_worksheet("AirPass")
wb$add_data("AirPass", t2, row_names = TRUE)

wb$group_cols("AirPass", cols = grp_cols)
wb$group_rows("AirPass", rows = grp_rows)

Convert integers to spreadsheet column notation

Description

int2col() performs the inverse operation of col2int(), transforming numeric column indices into their corresponding spreadsheet-style character labels (e.g., 1 to "A", 28 to "AB"). This is essential for converting calculated indices back into a format compatible with spreadsheet cell referencing.

Usage

int2col(x)

Arguments

Details

The function accepts a numeric vector and maps each integer to its positional representation in a base-26 derived system. This mapping follows standard spreadsheet conventions where the sequence progresses from "A" through "Z", followed by "AA", "AB", and so forth.

Validation is performed to ensure the input is numeric and finite. In accordance with the Office Open XML specification used by most spreadsheet software, the maximum supported column index is 16,384, which corresponds to the column label "XFD". Inputs exceeding this range may result in coordinates that are incompatible with standard spreadsheet applications.

Value

A character vector of spreadsheet column labels. Returns NULL if the input x is NULL.

Notes

• Non-integer numeric values will typically be coerced or truncated; however, infinite values will trigger an error to prevent invalid coordinate generation.

See Also

col2int()

Examples

# Convert a single index
int2col(27)

# Convert a sequence of indices
int2col(1:10)

# Handle large column indices
int2col(c(702, 703, 16384))

helper to check if a string looks like a cell

Description

helper to check if a string looks like a cell

Usage

is_dims(x)

Arguments

Modify named regions in a worksheet

Description

Create / delete a named region. You can also specify a named region by using the name argument in wb_add_data(x = iris, name = "my-region"). It is important to note that named regions are not case-sensitive and must be unique.

Usage

wb_add_named_region(
  wb,
  sheet = current_sheet(),
  dims = "A1",
  name,
  local_sheet = FALSE,
  overwrite = FALSE,
  comment = NULL,
  hidden = NULL,
  custom_menu = NULL,
  description = NULL,
  is_function = NULL,
  function_group_id = NULL,
  help = NULL,
  local_name = NULL,
  publish_to_server = NULL,
  status_bar = NULL,
  vb_procedure = NULL,
  workbook_parameter = NULL,
  xml = NULL,
  ...
)

wb_remove_named_region(wb, sheet = current_sheet(), name = NULL)

wb_get_named_regions(wb, tables = FALSE, x = NULL)

Arguments

Details

You can use the wb_dims() helper to specify the cell range of the named region

Value

A workbook, invisibly.

A data frame with the all named regions in wb. Or NULL, if none are found.

See Also

wb_get_tables()

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

## create named regions
wb <- wb_workbook()
wb$add_worksheet("Sheet 1")

## specify region
wb$add_data(x = iris, start_col = 1, start_row = 1)
wb$add_named_region(
  name = "iris",
  dims = wb_dims(x = iris)
)

## using add_data 'name' argument
wb$add_data(sheet = 1, x = iris, name = "iris2", start_col = 10)

## delete one
wb$remove_named_region(name = "iris2")
wb$get_named_regions()
## read named regions
df <- wb_to_df(wb, named_region = "iris")
head(df)


# Extract named regions from a file
out_file <- temp_xlsx()
wb_save(wb, out_file, overwrite = TRUE)

# Load the file as a workbook first, then get named regions.
wb1 <- wb_load(out_file)
wb1$get_named_regions()

Deprecated functions in package openxlsx2

Description

These functions are provided for compatibility with older versions of openxlsx2, and may be defunct as soon as the next release. This guide helps you update your code to the latest standards.

As of openxlsx2 v1.0, API change should be minimal.

Internal functions

These functions are used internally by openxlsx2. It is no longer advertised to use them in scripts. They originate from openxlsx, but do not fit openxlsx2's API.

You should be able to modify

• delete_data() -> wb_clean_sheet()

• write_data() -> wb_add_data()

• write_datatable() -> wb_add_data_table()

• write_comment() -> wb_add_comment()

• remove_comment() -> wb_remove_comment()

• write_formula() -> wb_add_formula()

You should be able to change those with minimal changes

Deprecated functions

First of all, you can set an option that will add warnings when using deprecated functions.

options("openxlsx2.soon_deprecated" = TRUE)

Argument changes

For consistency, arguments were renamed to snake_case for the 0.8 release. It is now recommended to use dims (the cell range) in favor of row, col, start_row, start_col

See wb_dims() as it provides many options on how to provide cell range

Functions with a new name

These functions were renamed for consistency.

• convertToExcelDate() -> convert_to_excel_date()

• wb_grid_lines() -> wb_set_grid_lines()

• create_comment() -> wb_comment()

Deprecated usage

• wb_get_named_regions() will no longer allow providing a file.

## Before
wb_get_named_regions(file)

## Now
wb <- wb_load(file)
wb_get_named_regions(wb)
# also possible
wb_load(file)$get_named_regions()`

See Also

.Deprecated

Options consulted by openxlsx2

Description

The openxlsx2 package allows the user to set global options to simplify formatting:

If the built-in defaults don't suit you, set one or more of these options. Typically, this is done in the .Rprofile startup file

• options("openxlsx2.borderColor" = "black")

• options("openxlsx2.borderStyle" = "thin")

• options("openxlsx2.dateFormat" = "mm/dd/yyyy")

• options("openxlsx2.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")

• options("openxlsx2.maxWidth" = NULL) (Maximum width allowed in OOXML is 250)

• options("openxlsx2.minWidth" = NULL)

• options("openxlsx2.numFmt" = NULL)

• options("openxlsx2.paperSize" = 9) corresponds to a A4 paper size

• options("openxlsx2.orientation" = "portrait") page orientation

• options("openxlsx2.sheet.default_name" = "Sheet")

• options("openxlsx2.rightToLeft" = NULL)

• options("openxlsx2.soon_deprecated" = FALSE) Set to TRUE if you want a warning if using some functions deprecated recently in openxlsx2

• options("openxlsx2.creator") A default name for the creator of new wbWorkbook object with wb_workbook() or new comments with wb_add_comment()

• options("openxlsx2.thread_id") the default person id when adding a threaded comment to a cell with wb_add_thread()

• options("openxlsx2.accountingFormat" = 4)

• options("openxlsx2.currencyFormat" = 4)

• options("openxlsx2.commaFormat" = 3)

• options("openxlsx2.percentageFormat" = 10)

• options("openxlsx2.scientificFormat" = 48)

• options("openxlsx2.string_nums" = TRUE) numerics in character columns will be converted. "1" will be written as 1

• options("openxlsx2.na" = "#N/A") consulted by write_xlsx(), wb_add_data() and wb_add_data_table().

• options("openxlsx2.zip_flags") custom zip flags passed to utils::zip() required when using a custom zip tool

• options("openxlsx2.compression_level" = 6) compression level for the output file. Increasing compression and time consumed from 1-9.

Helper for adding threaded comments

Description

Adds a person to a workbook, so that they can be the author of threaded comments in a workbook with wb_add_thread()

Usage

wb_add_person(wb, name = NULL, id = NULL, user_id = NULL, provider_id = "None")

wb_get_person(wb, name = NULL)

Arguments

See Also

wb_add_thread()

print pugi_xml

Description

print pugi_xml

Usage

## S3 method for class 'pugi_xml'
print(x, indent = " ", raw = FALSE, attr_indent = FALSE, ...)

Arguments

Examples

  # a pointer
  x <- read_xml("<a><b/></a>")
  print(x)
  print(x, raw = TRUE)

Modify workbook properties

Description

This function is useful for workbooks that are loaded. It can be used to set the workbook title, subject and category field. Use wb_workbook() to easily set these properties with a new workbook.

Usage

wb_get_properties(wb)

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

Arguments

Details

To set properties, the following XML core properties are used.

• title = dc:title

• subject = dc:subject

• creator = dc:creator

• keywords = cp:keywords

• comments = dc:description

• modifier = cp:lastModifiedBy

• datetime_created = dcterms:created

• datetime_modified = dcterms:modified

• category = cp:category

In addition, manager and company are used.

Value

A wbWorkbook object, invisibly.

See Also

wb_workbook()

Examples

file <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
wb <- wb_load(file)
wb$get_properties()

# Add a title to properties
wb$set_properties(title = "my title")
wb$get_properties()

xml_node

Description

returns xml values as character

Usage

xml_node(xml, level1 = NULL, level2 = NULL, level3 = NULL, ...)

xml_node_name(xml, level1 = NULL, level2 = NULL, ...)

xml_value(xml, level1 = NULL, level2 = NULL, level3 = NULL, ...)

xml_attr(xml, level1 = NULL, level2 = NULL, level3 = NULL, ...)

Arguments

Details

This function returns XML nodes as used in openxlsx2. In theory they could be returned as pointers as well, but this has not yet been implemented. If no level is provided, the nodes on level1 are returned

Examples

  x <- read_xml("<a><b/></a>")
  # return a
  xml_node(x, "a")
  # return b. requires the path to the node
  xml_node(x, "a", "b")
  xml_node_name("<a/>")
  xml_node_name("<a><b/></a>", "a")
  x <- read_xml("<a>1</a>")
  xml_value(x, "a")

  x <- read_xml("<a><b r=\"1\">2</b></a>")
  xml_value(x, "a", "b")

  x <- read_xml("<a a=\"1\" b=\"2\">1</a>")
  xml_attr(x, "a")

  x <- read_xml("<a><b r=\"1\">2</b></a>")
  xml_attr(x, "a", "b")
  x <- read_xml("<a a=\"1\" b=\"2\">1</a>")
  xml_attr(x, "a")

  x <- read_xml("<b><a a=\"1\" b=\"2\"/></b>")
  xml_attr(x, "b", "a")

read xml file

Description

read xml file

Usage

read_xml(
  xml,
  pointer = TRUE,
  escapes = FALSE,
  declaration = FALSE,
  whitespace = TRUE,
  empty_tags = FALSE,
  skip_control = TRUE
)

Arguments

Details

Read xml files or strings to pointer and checks if the input is valid XML. If the input is read into a character object, it will be reevaluated every time it is called. A pointer is evaluated once, but lives only for the lifetime of the R session or once it is gc().

Examples

  # a pointer
  x <- read_xml("<a><b/></a>")
  print(x)
  print(x, raw = TRUE)
  str(x)

  # a character
  y <- read_xml("<a><b/></a>", pointer = FALSE)
  print(y)
  print(y, raw = TRUE)
  str(y)

  # Errors if the import was unsuccessful
  try(z <- read_xml("<a><b/>"))

  xml <- '<?xml test="yay" ?><a>A & B</a>'
  # difference in escapes
  read_xml(xml, escapes = TRUE, pointer = FALSE)
  read_xml(xml, escapes = FALSE, pointer = FALSE)
  read_xml(xml, escapes = TRUE)
  read_xml(xml, escapes = FALSE)

  # read declaration
  read_xml(xml, declaration = TRUE)

Modify row heights of a worksheet

Description

Set / remove custom worksheet row heights

Usage

wb_set_row_heights(
  wb,
  sheet = current_sheet(),
  rows,
  heights = NULL,
  hidden = FALSE,
  hide_blanks = NULL
)

wb_remove_row_heights(wb, sheet = current_sheet(), rows)

Arguments

See Also

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, named_region-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_hyperlink(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

## Create a new workbook
wb <- wb_workbook()

## Add a worksheet
wb$add_worksheet("Sheet 1")

## set row heights
wb <- wb_set_row_heights(
  wb, 1,
  rows = c(1, 4, 22, 2, 19),
  heights = c(24, 28, 32, 42, 33)
)

## overwrite row 1 height
wb <- wb_set_row_heights(wb, 1, rows = 1, heights = 40)
## remove any custom row heights in row 1
wb$remove_row_heights(sheet = 1, rows = 1)

Get / Set worksheet names for a workbook

Description

Gets / Sets the worksheet names for a wbWorkbook object.

Usage

wb_set_sheet_names(wb, old = NULL, new)

wb_get_sheet_names(wb, escape = FALSE)

Arguments

Details

This only changes the sheet name as shown in spreadsheet software and will not alter it elsewhere. Not in formulas, chart references, named regions, pivot tables or anywhere else.

Value

• set_: The wbWorkbook object.

• get_: A named character vector of sheet names in order. The names represent the original value of the worksheet prior to any character substitutions.

Get/set worksheet visible state in a workbook

Description

Get and set worksheet visible state. This allows to hide worksheets from the workbook. The visibility of a worksheet can either be "visible", "hidden", or "veryHidden". You can set this when creating a worksheet with wb_add_worksheet(visible = FALSE)

Usage

wb_get_sheet_visibility(wb)

wb_set_sheet_visibility(wb, sheet = current_sheet(), value)

Arguments

Value

• wb_set_sheet_visibility: The Workbook object, invisibly.

• wb_get_sheet_visibility(): A character vector of the worksheet visibility value

Examples

wb <- wb_workbook()
wb$add_worksheet(sheet = "S1", visible = FALSE)
wb$add_worksheet(sheet = "S2", visible = TRUE)
wb$add_worksheet(sheet = "S3", visible = FALSE)

wb$get_sheet_visibility()
wb$set_sheet_visibility(1, TRUE)         ## show sheet 1
wb$set_sheet_visibility(2, FALSE)        ## hide sheet 2
wb$set_sheet_visibility(3, "hidden")     ## hide sheet 3
wb$set_sheet_visibility(3, "veryHidden") ## hide sheet 3 from UI

Get all styles on a sheet

Description

Get all styles on a sheet

Usage

styles_on_sheet(wb, sheet)

Arguments

helper function to create temporary directory for testing purpose

Description

helper function to create temporary directory for testing purpose

Usage

temp_xlsx(name = "temp_xlsx", macros = FALSE)

Arguments

openxlsx2 waivers

Description

Waiver functions for openxlsx2 functions.

• current_sheet() uses wb_get_active_sheet() by default if performing actions on a worksheet, for example when you add data.

• next_sheet() is used when you add a new worksheet, a new chartsheet or when you add a pivot table. It is defined as available sheets + 1L.

Usage

current_sheet()

next_sheet()

na_strings()

Value

An object of class openxlsx2_waiver

Workbook class

Description

This is the class used by openxlsx2 to modify workbooks from R. You can load an existing workbook with wb_load() and create a new one with wb_workbook().

After that, you can modify the wbWorkbook object through two primary methods:

Wrapper Function Method: Utilizes the wb family of functions that support piping to streamline operations.

wb <- wb_workbook(creator = "My name here")
wb <- wb_add_worksheet(wb, sheet = "Expenditure", grid_lines = FALSE)
wb <- wb_add_data(wb, x = USPersonalExpenditure, row_names = TRUE)

Chaining Method: Directly modifies the object through a series of chained function calls.

wb <- wb_workbook(creator = "My name here")$
  add_worksheet(sheet = "Expenditure", grid_lines = FALSE)$
  add_data(x = USPersonalExpenditure, row_names = TRUE)

While wrapper functions require explicit assignment of their output to reflect changes, chained functions inherently modify the input object. Both approaches are equally supported, offering flexibility to suit user preferences. The documentation mainly highlights the use of wrapper functions.

# Import workbooks
path <- system.file("extdata/openxlsx2_example.xlsx", package = "openxlsx2")
wb <- wb_load(path)

## or create one yourself
wb <- wb_workbook()
# add a worksheet
wb$add_worksheet("sheet")
# add some data
wb$add_data("sheet", cars)
# Add data in a different location
wb <- wb_add_data(wb, x = cars, dims = wb_dims(from_dims = "D4"))
# open it in your default spreadsheet software
if (interactive()) wb$open()

Note that the documentation is more complete in each of the wrapper functions. (i.e. ?wb_add_data rather than ?wbWorkbook).

Public fields

Methods

Public methods

• wbWorkbook$new()

• wbWorkbook$append()

• wbWorkbook$append_sheets()

• wbWorkbook$validate_sheet()

• wbWorkbook$add_chartsheet()

• wbWorkbook$add_worksheet()

• wbWorkbook$clone_worksheet()

• wbWorkbook$add_data()

• wbWorkbook$add_data_table()

• wbWorkbook$add_pivot_table()

• wbWorkbook$add_slicer()

• wbWorkbook$remove_slicer()

• wbWorkbook$add_timeline()

• wbWorkbook$remove_timeline()

• wbWorkbook$add_formula()

• wbWorkbook$add_hyperlink()

• wbWorkbook$remove_hyperlink()

• wbWorkbook$add_style()

• wbWorkbook$to_df()

• wbWorkbook$load()

• wbWorkbook$save()

• wbWorkbook$open()

• wbWorkbook$buildTable()

• wbWorkbook$update_table()

• wbWorkbook$copy_cells()

• wbWorkbook$get_base_font()

• wbWorkbook$set_base_font()

• wbWorkbook$get_base_colors()

• wbWorkbook$get_base_colours()

• wbWorkbook$set_base_colors()

• wbWorkbook$set_base_colours()

• wbWorkbook$get_bookview()

• wbWorkbook$remove_bookview()

• wbWorkbook$set_bookview()

• wbWorkbook$get_sheet_names()

• wbWorkbook$set_sheet_names()

• wbWorkbook$set_row_heights()

• wbWorkbook$remove_row_heights()

• wbWorkbook$createCols()

• wbWorkbook$group_cols()

• wbWorkbook$ungroup_cols()

• wbWorkbook$remove_col_widths()

• wbWorkbook$set_col_widths()

• wbWorkbook$group_rows()

• wbWorkbook$ungroup_rows()

• wbWorkbook$remove_worksheet()

• wbWorkbook$add_data_validation()

• wbWorkbook$merge_cells()

• wbWorkbook$unmerge_cells()

• wbWorkbook$freeze_pane()

• wbWorkbook$add_comment()

• wbWorkbook$get_comment()

• wbWorkbook$remove_comment()

• wbWorkbook$add_thread()

• wbWorkbook$get_thread()

• wbWorkbook$add_conditional_formatting()

• wbWorkbook$remove_conditional_formatting()

• wbWorkbook$add_image()

• wbWorkbook$add_plot()

• wbWorkbook$add_drawing()

• wbWorkbook$add_chart_xml()

• wbWorkbook$add_mschart()

• wbWorkbook$add_form_control()

• wbWorkbook$print()

• wbWorkbook$protect()

• wbWorkbook$protect_worksheet()

• wbWorkbook$get_properties()

• wbWorkbook$set_properties()

• wbWorkbook$add_mips()

• wbWorkbook$get_mips()

• wbWorkbook$set_creators()

• wbWorkbook$add_creators()

• wbWorkbook$remove_creators()

• wbWorkbook$set_last_modified_by()

• wbWorkbook$set_page_setup()

• wbWorkbook$page_setup()

• wbWorkbook$set_header_footer()

• wbWorkbook$get_tables()

• wbWorkbook$remove_tables()

• wbWorkbook$add_filter()

• wbWorkbook$remove_filter()

• wbWorkbook$set_grid_lines()

• wbWorkbook$grid_lines()

• wbWorkbook$add_named_region()

• wbWorkbook$get_named_regions()

• wbWorkbook$remove_named_region()

• wbWorkbook$set_order()

• wbWorkbook$get_sheet_visibility()

• wbWorkbook$set_sheet_visibility()

• wbWorkbook$add_page_break()

• wbWorkbook$clean_sheet()

• wbWorkbook$add_border()

• wbWorkbook$add_fill()

• wbWorkbook$add_font()

• wbWorkbook$add_numfmt()

• wbWorkbook$add_cell_style()

• wbWorkbook$get_cell_style()

• wbWorkbook$set_cell_style()

• wbWorkbook$set_cell_style_across()

• wbWorkbook$add_named_style()

• wbWorkbook$add_dxfs_style()

• wbWorkbook$clone_sheet_style()

• wbWorkbook$add_sparklines()

• wbWorkbook$add_ignore_error()

• wbWorkbook$set_sheetview()

• wbWorkbook$add_person()

• wbWorkbook$get_person()

• wbWorkbook$get_active_sheet()

• wbWorkbook$set_active_sheet()

• wbWorkbook$get_selected()

• wbWorkbook$set_selected()

• wbWorkbook$clone()

Method new()

Creates a new wbWorkbook object

Usage

wbWorkbook$new(
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetime_created = Sys.time(),
  datetime_modified = NULL,
  theme = NULL,
  keywords = NULL,
  comments = NULL,
  manager = NULL,
  company = NULL,
  ...
)

Arguments

Returns

a wbWorkbook object

Method append()

Append a field. This method is intended for internal use

Usage

wbWorkbook$append(field, value)

Arguments

Method append_sheets()

Append to self$workbook$sheets This method is intended for internal use

Usage

wbWorkbook$append_sheets(value)

Arguments

Method validate_sheet()

validate sheet

Usage

wbWorkbook$validate_sheet(sheet)

Arguments

Returns

The integer position of the sheet

Method add_chartsheet()

Add a chart sheet to the workbook

Usage

wbWorkbook$add_chartsheet(
  sheet = next_sheet(),
  tab_color = NULL,
  zoom = 100,
  visible = c("true", "false", "hidden", "visible", "veryhidden"),
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method add_worksheet()

Add worksheet to the wbWorkbook object

Usage

wbWorkbook$add_worksheet(
  sheet = next_sheet(),
  grid_lines = TRUE,
  row_col_headers = TRUE,
  tab_color = NULL,
  zoom = 100,
  header = NULL,
  footer = NULL,
  odd_header = header,
  odd_footer = footer,
  even_header = header,
  even_footer = footer,
  first_header = header,
  first_footer = footer,
  visible = c("true", "false", "hidden", "visible", "veryhidden"),
  has_drawing = FALSE,
  paper_size = getOption("openxlsx2.paperSize", default = 9),
  orientation = getOption("openxlsx2.orientation", default = "portrait"),
  hdpi = getOption("openxlsx2.hdpi", default = getOption("openxlsx2.dpi", default = 300)),
  vdpi = getOption("openxlsx2.vdpi", default = getOption("openxlsx2.dpi", default = 300)),
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method clone_worksheet()

Clone a workbooksheet to another workbook

Usage

wbWorkbook$clone_worksheet(
  old = current_sheet(),
  new = next_sheet(),
  from = NULL
)

Arguments

Method add_data()

add data

Usage

wbWorkbook$add_data(
  sheet = current_sheet(),
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  array = FALSE,
  col_names = TRUE,
  row_names = FALSE,
  with_filter = FALSE,
  name = NULL,
  sep = ", ",
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  na = na_strings(),
  inline_strings = TRUE,
  enforce = FALSE,
  ...
)

Arguments

Method add_data_table()

add a data table

Usage

wbWorkbook$add_data_table(
  sheet = current_sheet(),
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  col_names = TRUE,
  row_names = FALSE,
  table_style = "TableStyleLight9",
  table_name = NULL,
  with_filter = TRUE,
  sep = ", ",
  first_column = FALSE,
  last_column = FALSE,
  banded_rows = TRUE,
  banded_cols = FALSE,
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  na = na_strings(),
  inline_strings = TRUE,
  total_row = FALSE,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_pivot_table()

add pivot table

Usage

wbWorkbook$add_pivot_table(
  x,
  sheet = next_sheet(),
  dims = "A3",
  filter,
  rows,
  cols,
  data,
  fun,
  params,
  pivot_table,
  slicer,
  timeline
)

Arguments

Details

fun can be either of AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP

Returns

The wbWorkbook object

Method add_slicer()

add pivot table

Usage

wbWorkbook$add_slicer(
  x,
  dims = "A1",
  sheet = current_sheet(),
  pivot_table,
  slicer,
  params
)

Arguments

Returns

The wbWorkbook object

Method remove_slicer()

add pivot table

Usage

wbWorkbook$remove_slicer(sheet = current_sheet())

Arguments

Returns

The wbWorkbook object

Method add_timeline()

add pivot table

Usage

wbWorkbook$add_timeline(
  x,
  dims = "A1",
  sheet = current_sheet(),
  pivot_table,
  timeline,
  params
)

Arguments

Returns

The wbWorkbook object

Method remove_timeline()

add pivot table

Usage

wbWorkbook$remove_timeline(sheet = current_sheet())

Arguments

Returns

The wbWorkbook object

Method add_formula()

Add formula

Usage

wbWorkbook$add_formula(
  sheet = current_sheet(),
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  array = FALSE,
  cm = FALSE,
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  enforce = FALSE,
  shared = FALSE,
  name = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_hyperlink()

Add hyperlink

Usage

wbWorkbook$add_hyperlink(
  sheet = current_sheet(),
  dims = "A1",
  target = NULL,
  tooltip = NULL,
  is_external = TRUE,
  col_names = FALSE
)

Arguments

Returns

The wbWorkbook object

Method remove_hyperlink()

remove hyperlink

Usage

wbWorkbook$remove_hyperlink(sheet = current_sheet(), dims = NULL)

Arguments

Returns

The wbWorkbook object

Method add_style()

add style

Usage

wbWorkbook$add_style(style = NULL, style_name = NULL)

Arguments

Returns

The wbWorkbook object

Method to_df()

to_df

Usage

wbWorkbook$to_df(
  sheet,
  start_row = NULL,
  start_col = NULL,
  row_names = FALSE,
  col_names = TRUE,
  skip_empty_rows = FALSE,
  skip_empty_cols = FALSE,
  skip_hidden_rows = FALSE,
  skip_hidden_cols = FALSE,
  rows = NULL,
  cols = NULL,
  detect_dates = TRUE,
  na = "#N/A",
  fill_merged_cells = FALSE,
  dims,
  show_formula = FALSE,
  convert = TRUE,
  types,
  named_region,
  keep_attributes = FALSE,
  check_names = FALSE,
  show_hyperlinks = FALSE,
  apply_numfmts = FALSE,
  ...
)

Arguments

Returns

a data frame

Method load()

load workbook

Usage

wbWorkbook$load(file, sheet, data_only = FALSE, ...)

Arguments

Returns

The wbWorkbook object invisibly

Method save()

Save the workbook

Usage

wbWorkbook$save(file = self$path, overwrite = TRUE, path = NULL, flush = FALSE)

Arguments

Returns

The wbWorkbook object invisibly

Method open()

open wbWorkbook in spreadsheet software

Usage

wbWorkbook$open(interactive = NA, flush = FALSE)

Arguments

Details

minor helper wrapping xl_open which does the entire same thing

Returns

The wbWorkbook, invisibly

Method buildTable()

Build table

Usage

wbWorkbook$buildTable(
  sheet = current_sheet(),
  colNames,
  ref,
  showColNames,
  tableStyle,
  tableName,
  withFilter = TRUE,
  totalsRowCount = 0,
  totalLabel = FALSE,
  showFirstColumn = 0,
  showLastColumn = 0,
  showRowStripes = 1,
  showColumnStripes = 0
)

Arguments

Returns

The wbWorksheet object, invisibly

Method update_table()

update a data_table

Usage

wbWorkbook$update_table(sheet = current_sheet(), dims = "A1", tabname)

Arguments

Returns

The wbWorksheet object, invisibly

Method copy_cells()

copy cells around in a workbook

Usage

wbWorkbook$copy_cells(
  sheet = current_sheet(),
  dims = "A1",
  data,
  as_value = FALSE,
  as_ref = FALSE,
  transpose = FALSE,
  ...
)

Arguments

Returns

The wbWorksheet object, invisibly

Method get_base_font()

Get the base font

Usage

wbWorkbook$get_base_font()

Returns

A list of of the font

Method set_base_font()

Set the base font

Usage

wbWorkbook$set_base_font(
  font_size = 11,
  font_color = wb_color(theme = "1"),
  font_name = "Aptos Narrow",
  ...
)

Arguments

Returns

The wbWorkbook object

Method get_base_colors()

Get the base color

Usage

wbWorkbook$get_base_colors(xml = FALSE, plot = TRUE)

Arguments

Method get_base_colours()

Get the base colour

Usage

wbWorkbook$get_base_colours(xml = FALSE, plot = TRUE)

Arguments

Method set_base_colors()

Set the base color

Usage

wbWorkbook$set_base_colors(theme = "Office", ...)

Arguments

Returns

The wbWorkbook object

Method set_base_colours()

Set the base colour

Usage

wbWorkbook$set_base_colours(theme = "Office", ...)

Arguments

Returns

The wbWorkbook object

Method get_bookview()

Get the book views

Usage

wbWorkbook$get_bookview()

Returns

A dataframe with the bookview properties

Method remove_bookview()

Get the book views

Usage

wbWorkbook$remove_bookview(view = NULL)

Arguments

Returns

The wbWorkbook object

Method set_bookview()

Usage

wbWorkbook$set_bookview(
  active_tab = NULL,
  auto_filter_date_grouping = NULL,
  first_sheet = NULL,
  minimized = NULL,
  show_horizontal_scroll = NULL,
  show_sheet_tabs = NULL,
  show_vertical_scroll = NULL,
  tab_ratio = NULL,
  visibility = NULL,
  window_height = NULL,
  window_width = NULL,
  x_window = NULL,
  y_window = NULL,
  view = 1L,
  ...
)

Arguments

Returns

The wbWorkbook object

Method get_sheet_names()

Get sheet names

Usage

wbWorkbook$get_sheet_names(escape = FALSE)

Arguments

Returns

A named character vector of sheet names in their order. The names represent the original value of the worksheet prior to any character substitutions.

Method set_sheet_names()

Sets a sheet name

Usage

wbWorkbook$set_sheet_names(old = NULL, new)

Arguments

Returns

The wbWorkbook object, invisibly

Method set_row_heights()

Sets a row height for a sheet

Usage

wbWorkbook$set_row_heights(
  sheet = current_sheet(),
  rows,
  heights = NULL,
  hidden = FALSE,
  hide_blanks = NULL
)

Arguments

Returns

The wbWorkbook object, invisibly

Method remove_row_heights()

Removes a row height for a sheet

Usage

wbWorkbook$remove_row_heights(sheet = current_sheet(), rows)

Arguments

Returns

The wbWorkbook object, invisibly

Method createCols()

creates column object for worksheet

Usage

wbWorkbook$createCols(sheet = current_sheet(), n, beg, end)

Arguments

Method group_cols()

Group cols

Usage

wbWorkbook$group_cols(
  sheet = current_sheet(),
  cols,
  collapsed = FALSE,
  levels = NULL
)

Arguments

Returns

The wbWorkbook object, invisibly

Method ungroup_cols()

ungroup cols

Usage

wbWorkbook$ungroup_cols(sheet = current_sheet(), cols)

Arguments

Returns

The wbWorkbook object

Method remove_col_widths()

Remove row heights from a worksheet

Usage

wbWorkbook$remove_col_widths(sheet = current_sheet(), cols)

Arguments

Returns

The wbWorkbook object, invisibly

Method set_col_widths()

Set column widths

Usage

wbWorkbook$set_col_widths(
  sheet = current_sheet(),
  cols,
  widths = 8.43,
  hidden = FALSE
)

Arguments

Returns

The wbWorkbook object, invisibly

Method group_rows()

Group rows

Usage

wbWorkbook$group_rows(
  sheet = current_sheet(),
  rows,
  collapsed = FALSE,
  levels = NULL
)

Arguments

Returns

The wbWorkbook object, invisibly

Method ungroup_rows()

ungroup rows

Usage

wbWorkbook$ungroup_rows(sheet = current_sheet(), rows)

Arguments

Returns

The wbWorkbook object

Method remove_worksheet()

Remove a worksheet

Usage

wbWorkbook$remove_worksheet(sheet = current_sheet())

Arguments

Returns

The wbWorkbook object, invisibly

Method add_data_validation()

Adds data validation

Usage

wbWorkbook$add_data_validation(
  sheet = current_sheet(),
  dims = "A1",
  type,
  operator,
  value,
  allow_blank = TRUE,
  show_input_msg = TRUE,
  show_error_msg = TRUE,
  error_style = NULL,
  error_title = NULL,
  error = NULL,
  prompt_title = NULL,
  prompt = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object

Method merge_cells()

Set cell merging for a sheet

Usage

wbWorkbook$merge_cells(
  sheet = current_sheet(),
  dims = NULL,
  solve = FALSE,
  direction = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method unmerge_cells()

Removes cell merging for a sheet

Usage

wbWorkbook$unmerge_cells(sheet = current_sheet(), dims = NULL, ...)

Arguments

Returns

The wbWorkbook object, invisibly

Method freeze_pane()

Set freeze panes for a sheet

Usage

wbWorkbook$freeze_pane(
  sheet = current_sheet(),
  first_active_row = NULL,
  first_active_col = NULL,
  first_row = FALSE,
  first_col = FALSE,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method add_comment()

Add comment

Usage

wbWorkbook$add_comment(sheet = current_sheet(), dims = "A1", comment, ...)

Arguments

Returns

The wbWorkbook object

Method get_comment()

Get comments

Usage

wbWorkbook$get_comment(sheet = current_sheet(), dims = NULL)

Arguments

Returns

A data frame containing comments

Method remove_comment()

Remove comment

Usage

wbWorkbook$remove_comment(sheet = current_sheet(), dims = "A1", ...)

Arguments

Returns

The wbWorkbook object

Method add_thread()

add threaded comment to worksheet

Usage

wbWorkbook$add_thread(
  sheet = current_sheet(),
  dims = "A1",
  comment = NULL,
  person_id,
  reply = FALSE,
  resolve = FALSE
)

Arguments

Method get_thread()

Get threads

Usage

wbWorkbook$get_thread(sheet = current_sheet(), dims = NULL)

Arguments

Returns

A data frame containing threads

Method add_conditional_formatting()

Add conditional formatting

Usage

wbWorkbook$add_conditional_formatting(
  sheet = current_sheet(),
  dims = NULL,
  rule = NULL,
  style = NULL,
  type = c("expression", "colorScale", "dataBar", "iconSet", "duplicatedValues",
    "uniqueValues", "containsErrors", "notContainsErrors", "containsBlanks",
    "notContainsBlanks", "containsText", "notContainsText", "beginsWith", "endsWith",
    "between", "topN", "bottomN"),
  params = list(showValue = TRUE, gradient = TRUE, border = TRUE, percent = FALSE, rank =
    5L, axisPosition = "automatic"),
  ...
)

Arguments

Returns

The wbWorkbook object

Method remove_conditional_formatting()

Remove conditional formatting

Usage

wbWorkbook$remove_conditional_formatting(
  sheet = current_sheet(),
  dims = NULL,
  first = FALSE,
  last = FALSE
)

Arguments

Returns

The wbWorkbook object

Method add_image()

Insert an image into a sheet

Usage

wbWorkbook$add_image(
  sheet = current_sheet(),
  dims = "A1",
  file,
  width = 6,
  height = 3,
  row_offset = 0,
  col_offset = 0,
  units = "in",
  dpi = 300,
  address = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method add_plot()

Add plot. A wrapper for add_image()

Usage

wbWorkbook$add_plot(
  sheet = current_sheet(),
  dims = "A1",
  width = 6,
  height = 4,
  row_offset = 0,
  col_offset = 0,
  file_type = "png",
  units = "in",
  dpi = 300,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_drawing()

Add xml drawing

Usage

wbWorkbook$add_drawing(
  sheet = current_sheet(),
  dims = "A1",
  xml,
  col_offset = 0,
  row_offset = 0,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_chart_xml()

Add xml chart

Usage

wbWorkbook$add_chart_xml(
  sheet = current_sheet(),
  dims = NULL,
  xml,
  col_offset = 0,
  row_offset = 0,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_mschart()

Add mschart chart to the workbook

Usage

wbWorkbook$add_mschart(
  sheet = current_sheet(),
  dims = NULL,
  graph,
  col_offset = 0,
  row_offset = 0,
  ...
)

Arguments

Returns

The wbWorkbook object

Method add_form_control()

Add form control to workbook

Usage

wbWorkbook$add_form_control(
  sheet = current_sheet(),
  dims = "A1",
  type = c("Checkbox", "Radio", "Drop"),
  text = NULL,
  link = NULL,
  range = NULL,
  checked = FALSE
)

Arguments

Returns

The wbWorkbook object, invisibly

Method print()

Prints the wbWorkbook object

Usage

wbWorkbook$print()

Returns

The wbWorkbook object, invisibly; called for its side-effects

Method protect()

Protect a workbook

Usage

wbWorkbook$protect(
  protect = TRUE,
  password = NULL,
  lock_structure = FALSE,
  lock_windows = FALSE,
  type = 1,
  file_sharing = FALSE,
  username = unname(Sys.info()["user"]),
  read_only_recommended = FALSE,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method protect_worksheet()

protect worksheet

Usage

wbWorkbook$protect_worksheet(
  sheet = current_sheet(),
  protect = TRUE,
  password = NULL,
  properties = NULL
)

Arguments

Returns

The wbWorkbook object

Method get_properties()

Get properties of a workbook

Usage

wbWorkbook$get_properties()

Method set_properties()

Set a property of a workbook

Usage

wbWorkbook$set_properties(
  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
)

Arguments

Method add_mips()

add mips string

Usage

wbWorkbook$add_mips(xml = NULL)

Arguments

Method get_mips()

get mips string

Usage

wbWorkbook$get_mips(single_xml = TRUE, quiet = TRUE)

Arguments

Method set_creators()

Set creator(s)

Usage

wbWorkbook$set_creators(creators)

Arguments

Method add_creators()

Add creator(s)

Usage

wbWorkbook$add_creators(creators)

Arguments

Method remove_creators()

Remove creator(s)

Usage

wbWorkbook$remove_creators(creators)

Arguments

Method set_last_modified_by()

Change the last modified by

Usage

wbWorkbook$set_last_modified_by(name, ...)

Arguments

Returns

The wbWorkbook object, invisibly

Method set_page_setup()

set_page_setup() this function is intended to supersede page_setup(), but is not yet stable

Usage

wbWorkbook$set_page_setup(
  sheet = current_sheet(),
  black_and_white = NULL,
  cell_comments = NULL,
  copies = NULL,
  draft = NULL,
  errors = NULL,
  first_page_number = NULL,
  id = NULL,
  page_order = NULL,
  paper_height = NULL,
  paper_width = NULL,
  hdpi = NULL,
  vdpi = NULL,
  use_first_page_number = NULL,
  use_printer_defaults = NULL,
  orientation = NULL,
  scale = NULL,
  left = 0.7,
  right = 0.7,
  top = 0.75,
  bottom = 0.75,
  header = 0.3,
  footer = 0.3,
  fit_to_width = FALSE,
  fit_to_height = FALSE,
  paper_size = NULL,
  print_title_rows = NULL,
  print_title_cols = NULL,
  summary_row = NULL,
  summary_col = NULL,
  tab_color = NULL,
  horizontal_centered = NULL,
  vertical_centered = NULL,
  print_headings = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method page_setup()

page_setup()

Usage

wbWorkbook$page_setup(
  sheet = current_sheet(),
  orientation = NULL,
  scale = 100,
  left = 0.7,
  right = 0.7,
  top = 0.75,
  bottom = 0.75,
  header = 0.3,
  footer = 0.3,
  fit_to_width = FALSE,
  fit_to_height = FALSE,
  paper_size = NULL,
  print_title_rows = NULL,
  print_title_cols = NULL,
  summary_row = NULL,
  summary_col = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method set_header_footer()

Sets headers and footers

Usage

wbWorkbook$set_header_footer(
  sheet = current_sheet(),
  header = NULL,
  footer = NULL,
  even_header = NULL,
  even_footer = NULL,
  first_header = NULL,
  first_footer = NULL,
  align_with_margins = NULL,
  scale_with_doc = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method get_tables()

get tables

Usage

wbWorkbook$get_tables(sheet = current_sheet())

Arguments

Returns

The sheet tables. character() if empty

Method remove_tables()

remove tables

Usage

wbWorkbook$remove_tables(sheet = current_sheet(), table, remove_data = TRUE)

Arguments

Returns

The wbWorkbook object

Method add_filter()

add filters

Usage

wbWorkbook$add_filter(sheet = current_sheet(), rows, cols)

Arguments

Returns

The wbWorkbook object

Method remove_filter()

remove filters

Usage

wbWorkbook$remove_filter(sheet = current_sheet())

Arguments

Returns

The wbWorkbook object

Method set_grid_lines()

grid lines

Usage

wbWorkbook$set_grid_lines(sheet = current_sheet(), show = FALSE, print = show)

Arguments

Returns

The wbWorkbook object

Method grid_lines()

grid lines

Usage

wbWorkbook$grid_lines(sheet = current_sheet(), show = FALSE, print = show)

Arguments

Returns

The wbWorkbook object

Method add_named_region()

add a named region

Usage

wbWorkbook$add_named_region(
  sheet = current_sheet(),
  dims = "A1",
  name,
  local_sheet = FALSE,
  overwrite = FALSE,
  comment = NULL,
  hidden = NULL,
  custom_menu = NULL,
  description = NULL,
  is_function = NULL,
  function_group_id = NULL,
  help = NULL,
  local_name = NULL,
  publish_to_server = NULL,
  status_bar = NULL,
  vb_procedure = NULL,
  workbook_parameter = NULL,
  xml = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object

Method get_named_regions()

get named regions in a workbook

Usage

wbWorkbook$get_named_regions(tables = FALSE, x = NULL)

Arguments

Returns

A data.frame of named regions

Method remove_named_region()

remove a named region

Usage

wbWorkbook$remove_named_region(sheet = current_sheet(), name = NULL)

Arguments

Returns

The wbWorkbook object

Method set_order()

set worksheet order

Usage

wbWorkbook$set_order(sheets)

Arguments

Returns

The wbWorkbook object

Method get_sheet_visibility()

Get sheet visibility

Usage

wbWorkbook$get_sheet_visibility()

Returns

Returns sheet visibility

Method set_sheet_visibility()

Set sheet visibility

Usage

wbWorkbook$set_sheet_visibility(sheet = current_sheet(), value)

Arguments

Returns

The wbWorkbook object

Method add_page_break()

Add a page break

Usage

wbWorkbook$add_page_break(sheet = current_sheet(), row = NULL, col = NULL)

Arguments

Returns

The wbWorkbook object

Method clean_sheet()

clean sheet (remove all values)

Usage

wbWorkbook$clean_sheet(
  sheet = current_sheet(),
  dims = NULL,
  numbers = TRUE,
  characters = TRUE,
  styles = TRUE,
  merged_cells = TRUE,
  hyperlinks = TRUE
)

Arguments

Returns

The wbWorksheetObject, invisibly

Method add_border()

create borders for cell region

Usage

wbWorkbook$add_border(
  sheet = current_sheet(),
  dims = "A1",
  bottom_color = wb_color(hex = "FF000000"),
  left_color = wb_color(hex = "FF000000"),
  right_color = wb_color(hex = "FF000000"),
  top_color = wb_color(hex = "FF000000"),
  bottom_border = "thin",
  left_border = "thin",
  right_border = "thin",
  top_border = "thin",
  inner_hgrid = NULL,
  inner_hcolor = NULL,
  inner_vgrid = NULL,
  inner_vcolor = NULL,
  update = FALSE,
  diagonal_up = NULL,
  diagonal_down = NULL,
  diagonal_color = NULL,
  ...
)

Arguments

Returns

The wbWorkbook, invisibly

Method add_fill()

provide simple fill function

Usage

wbWorkbook$add_fill(
  sheet = current_sheet(),
  dims = "A1",
  color = wb_color(hex = "FFFFFF00"),
  pattern = "solid",
  gradient_fill = "",
  every_nth_col = 1,
  every_nth_row = 1,
  bg_color = NULL,
  ...
)

Arguments

Returns

The wbWorksheetObject, invisibly

Method add_font()

provide simple font function

Usage

wbWorkbook$add_font(
  sheet = current_sheet(),
  dims = "A1",
  name = "Aptos Narrow",
  color = wb_color(hex = "FF000000"),
  size = "11",
  bold = "",
  italic = "",
  outline = "",
  strike = "",
  underline = "",
  charset = "",
  condense = "",
  extend = "",
  family = "",
  scheme = "",
  shadow = "",
  vert_align = "",
  update = FALSE,
  ...
)

Arguments

Returns

The wbWorkbook, invisibly

Method add_numfmt()

provide simple number format function

Usage

wbWorkbook$add_numfmt(sheet = current_sheet(), dims = "A1", numfmt)

Arguments

Returns

The wbWorksheetObject, invisibly

Method add_cell_style()

provide simple cell style format function

Usage

wbWorkbook$add_cell_style(
  sheet = current_sheet(),
  dims = "A1",
  apply_alignment = NULL,
  apply_border = NULL,
  apply_fill = NULL,
  apply_font = NULL,
  apply_number_format = NULL,
  apply_protection = NULL,
  border_id = NULL,
  ext_lst = NULL,
  fill_id = NULL,
  font_id = NULL,
  hidden = NULL,
  horizontal = NULL,
  indent = NULL,
  justify_last_line = NULL,
  locked = NULL,
  num_fmt_id = NULL,
  pivot_button = NULL,
  quote_prefix = NULL,
  reading_order = NULL,
  relative_indent = NULL,
  shrink_to_fit = NULL,
  text_rotation = NULL,
  vertical = NULL,
  wrap_text = NULL,
  xf_id = NULL,
  ...
)

Arguments

Returns

The wbWorkbook object, invisibly

Method get_cell_style()

get sheet style

Usage

wbWorkbook$get_cell_style(sheet = current_sheet(), dims)

Arguments

Returns

a character vector of cell styles

Method set_cell_style()

set sheet style

Usage

wbWorkbook$set_cell_style(sheet = current_sheet(), dims, style)

Arguments

Returns

The wbWorksheetObject, invisibly

Method set_cell_style_across()

set style across columns and/or rows

Usage

wbWorkbook$set_cell_style_across(
  sheet = current_sheet(),
  style,
  cols = NULL,
  rows = NULL
)

Arguments

Returns

The wbWorkbook object

Method add_named_style()

set sheet style

Usage

wbWorkbook$add_named_style(
  sheet = current_sheet(),
  dims = "A1",
  name = "Normal",
  font_name = NULL,
  font_size = NULL
)

Arguments

Returns

The wbWorkbook, invisibly

Method add_dxfs_style()

create dxfs style These styles are used with conditional formatting and custom table styles

Usage

wbWorkbook$add_dxfs_style(
  name,
  font_name = NULL,
  font_size = NULL,
  font_color = NULL,
  num_fmt = NULL,
  border = NULL,
  border_color = wb_color(getOption("openxlsx2.borderColor", "black")),
  border_style = getOption("openxlsx2.borderStyle", "thin"),
  bg_fill = NULL,
  gradient_fill = NULL,
  text_bold = NULL,
  text_italic = NULL,
  text_underline = NULL,
  ...
)

Arguments