Type:	Package
Title:	Read, Write and Edit 'xlsx' Files
Version:	1.17
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, magrittr, stringi, utils, zip
LinkingTo:	Rcpp
Suggests:	ggplot2, knitr, mschart (≥ 0.4), rmarkdown, rvg, testthat (≥ 3.0.0), waldo
VignetteBuilder:	knitr
Encoding:	UTF-8
RoxygenNote:	7.3.2
Config/testthat/edition:	3
Config/testthat/parallel:	true
NeedsCompilation:	yes
Packaged:	2025-07-04 06:09:03 UTC; janmarvingarbuszus
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:	2025-07-04 07:00:01 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 Excel and exceeded 15 digits, Excel may have already rounded it 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()

Pipe operator

Description

See magrittr::%>% for details.

Usage

lhs %>% rhs

Arguments

Value

The result of calling rhs(lhs).

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

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 Excel column to integer

Description

Converts an Excel column label to an integer.

Usage

col2int(x)

Arguments

Value

An integer column label (or NULL if x is NULL)

Examples

col2int(LETTERS)

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 min and max column width for "auto" columns is set by (default values show):

• options("openxlsx2.minWidth" = 3)

• options("openxlsx2.maxWidth" = 250) Maximum width allowed in Excel

NOTE: The calculation of column widths can be slow for large worksheets.

NOTE: The hidden parameter may conflict with the one set in wb_group_cols(); changing one will update the other.

NOTE: The default column width varies by spreadsheet software, operating system, and DPI settings used. Setting widths to specific value also is no guarantee that the output will have consistent column widths.

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

Convert to Excel data

Description

Use convert_to_excel_date().

Arguments

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

Description

Convert from Excel date number to R Date type

Usage

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

convert_datetime(x, origin = "1900-01-01", ...)

convert_hms(x)

Arguments

Details

Excel stores dates as number of days from some origin day

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 Excel 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 Excel 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 sparklines object

Description

Create a sparkline to be added a workbook with wb_add_sparklines()

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

Colors are all predefined to be rgb. Maybe theme colors can be used too.

Value

A string containing XML code

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)

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)

rowcol_to_dims(row, col, single = TRUE)

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)

Arguments

Examples

dims_to_dataframe("A1:B2")

Add/remove column filters in a worksheet

Description

Add or remove excel 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 integer to Excel column

Description

Converts an integer to an Excel column label.

Usage

int2col(x)

Arguments

Examples

int2col(1:10)

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 Excel 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.strings" = "#N/A") consulted by write_xlsx(), wb_add_data() and wb_add_data_table().

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

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

# 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 with piping in a different location
wb <- wb %>% wb_add_data(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.strings = 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.strings = 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 = 1,
  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.strings = "#N/A",
  na.numbers = NA,
  fill_merged_cells = FALSE,
  dims,
  show_formula = FALSE,
  convert = TRUE,
  types,
  named_region,
  keep_attributes = FALSE,
  check_names = FALSE,
  show_hyperlinks = 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 Excel.

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
)

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

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

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

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

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

Returns

The wbWorksheetObject, invisibly

Method clone_sheet_style()

clone style from one sheet to another

Usage

wbWorkbook$clone_sheet_style(from = current_sheet(), to)

Arguments

Method add_sparklines()

apply sparkline to worksheet

Usage

wbWorkbook$add_sparklines(sheet = current_sheet(), sparklines)

Arguments

Method add_ignore_error()

Ignore error on worksheet

Usage

wbWorkbook$add_ignore_error(
  sheet = current_sheet(),
  dims = "A1",
  calculated_column = FALSE,
  empty_cell_reference = FALSE,
  eval_error = FALSE,
  formula = FALSE,
  formula_range = FALSE,
  list_data_validation = FALSE,
  number_stored_as_text = FALSE,
  two_digit_text_year = FALSE,
  unlocked_formula = FALSE,
  ...
)

Arguments

Method set_sheetview()

add sheetview

Usage

wbWorkbook$set_sheetview(
  sheet = current_sheet(),
  color_id = NULL,
  default_grid_color = NULL,
  right_to_left = NULL,
  show_formulas = NULL,
  show_grid_lines = NULL,
  show_outline_symbols = NULL,
  show_row_col_headers = NULL,
  show_ruler = NULL,
  show_white_space = NULL,
  show_zeros = NULL,
  tab_selected = NULL,
  top_left_cell = NULL,
  view = NULL,
  window_protection = NULL,
  workbook_view_id = NULL,
  zoom_scale = NULL,
  zoom_scale_normal = NULL,
  zoom_scale_page_layout_view = NULL,
  zoom_scale_sheet_layout_view = NULL,
  ...
)

Arguments