Package 'openxlsx2'

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

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)

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)

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

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)

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)

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)

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)

Description

Border styles can any of the following: "thin", "thick", "slantDashDot", "none", "mediumDashed", "mediumDashDot", "medium", "hair", "double", "dotted", "dashed", "dashedDotDot", "dashDot" 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 = "",
  ...
)

Arguments

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

Description

Helper to create a cell style

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

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

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)

Description

Create a new style to apply to worksheet cells. Created styles have to be assigned to a workbook to use them

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"),
  bgFill = 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"),
  bgFill = wb_color(hex = "FFFFC7CE")
)

## See package vignettes for further examples

Description

Create fill pattern

Usage

create_fill(
  gradientFill = "",
  patternType = "",
  bgColor = NULL,
  fgColor = NULL,
  ...
)

Arguments

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

Description

Create font format

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

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

font <- create_font()
# 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"))

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

Description

Create number format

Usage

create_numfmt(numFmtId, formatCode)

Arguments

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

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

Arguments

Details

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

Value

A string containing XML code

Examples

# create sparklineGroup
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")
)

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)

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

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)

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 list of rows and columns for to_rowcol

See Also

wb_dims()

Examples

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

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!

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
)

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

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.

Examples

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

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.

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)

Description

Converts an integer to an Excel column label.

Usage

int2col(x)

Arguments

Examples

int2col(1:10)

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

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.

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

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

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)

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 = Sys.time(),
  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()

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

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)

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)

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.

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

Description

Get all styles on a sheet

Usage

styles_on_sheet(wb, sheet)

Arguments

Description

helper function to create temporary directory for testing purpose

Usage

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

Arguments

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

Usage

current_sheet()

next_sheet()

na_strings()

Value

An object of class openxlsx2_waiver

Description

wb wrapper to create borders for cell regions.

Usage

wb_add_border(
  wb,
  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,
  ...
)

Arguments

See Also

create_border()

Other styles: wb_add_cell_style(), wb_add_fill(), wb_add_font(), wb_add_named_style(), wb_add_numfmt(), wb_cell_style

Examples

wb <- wb_workbook() %>% wb_add_worksheet("S1") %>%  wb_add_data("S1", mtcars)
wb <- wb_add_border(wb, 1, dims = "A1:K1",
 left_border = NULL, right_border = NULL,
 top_border = NULL, bottom_border = "double")
wb <- wb_add_border(wb, 1, dims = "A5",
 left_border = "dotted", right_border = "dotted",
 top_border = "hair", bottom_border = "thick")
wb <- wb_add_border(wb, 1, dims = "C2:C5")
wb <- wb_add_border(wb, 1, dims = "G2:H3")

wb <- wb_add_border(wb, 1, dims = "G12:H13",
 left_color = wb_color(hex = "FF9400D3"), right_color = wb_color(hex = "FF4B0082"),
 top_color = wb_color(hex = "FF0000FF"), bottom_color = wb_color(hex = "FF00FF00"))
wb <- wb_add_border(wb, 1, dims = "A20:C23")
wb <- wb_add_border(wb, 1, dims = "B12:D14",
 left_color = wb_color(hex = "FFFFFF00"), right_color = wb_color(hex = "FFFF7F00"),
 bottom_color = wb_color(hex = "FFFF0000"))
wb <- wb_add_border(wb, 1, dims = "D28:E28")

# With chaining

wb <- wb_workbook()
wb$add_worksheet("S1")$add_data("S1", mtcars)
wb$add_border(1, dims = "A1:K1",
 left_border = NULL, right_border = NULL,
 top_border = NULL, bottom_border = "double")
wb$add_border(1, dims = "A5",
 left_border = "dotted", right_border = "dotted",
 top_border = "hair", bottom_border = "thick")
wb$add_border(1, dims = "C2:C5")
wb$add_border(1, dims = "G2:H3")
wb$add_border(1, dims = "G12:H13",
 left_color = wb_color(hex = "FF9400D3"), right_color = wb_color(hex = "FF4B0082"),
 top_color = wb_color(hex = "FF0000FF"), bottom_color = wb_color(hex = "FF00FF00"))
wb$add_border(1, dims = "A20:C23")
wb$add_border(1, dims = "B12:D14",
 left_color = wb_color(hex = "FFFFFF00"), right_color = wb_color(hex = "FFFF7F00"),
 bottom_color = wb_color(hex = "FFFF0000"))
wb$add_border(1, dims = "D28:E28")
# if (interactive()) wb$open()

wb <- wb_workbook()
wb$add_worksheet("S1")$add_data("S1", mtcars)
wb$add_border(1, dims = "A2:K33", inner_vgrid = "thin", inner_vcolor = c(rgb="FF808080"))

Description

Add cell style to a cell region

Usage

wb_add_cell_style(
  wb,
  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

Value

The wbWorkbook object, invisibly

See Also

Other styles: wb_add_border(), wb_add_fill(), wb_add_font(), wb_add_named_style(), wb_add_numfmt(), wb_cell_style

Examples

wb <- wb_workbook() %>%
  wb_add_worksheet("S1") %>%
  wb_add_data("S1", x = mtcars)

wb %>%
  wb_add_cell_style(
    dims = "A1:K1",
    text_rotation = "45",
    horizontal = "center",
    vertical = "center",
    wrap_text = "1"
)
# Chaining
wb <- wb_workbook()$add_worksheet("S1")$add_data(x = mtcars)
wb$add_cell_style(dims = "A1:K1",
                  text_rotation = "45",
                  horizontal = "center",
                  vertical = "center",
                  wrap_text = "1")

Description

Add a chart XML to a worksheet

Usage

wb_add_chart_xml(
  wb,
  sheet = current_sheet(),
  dims = NULL,
  xml,
  col_offset = 0,
  row_offset = 0,
  ...
)

Arguments

See Also

wb_add_drawing() wb_add_image() wb_add_mschart() wb_add_plot()

Description

A chartsheet is a special type of sheet that handles charts output. You must add a chart to the sheet. Otherwise, this will break the workbook.

Usage

wb_add_chartsheet(
  wb,
  sheet = next_sheet(),
  tab_color = NULL,
  zoom = 100,
  visible = c("true", "false", "hidden", "visible", "veryhidden"),
  ...
)

Arguments

See Also

wb_add_mschart()

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, row_heights-wb, 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()

Description

Add comment to worksheet

Usage

wb_add_comment(wb, sheet = current_sheet(), dims = "A1", comment, ...)

wb_get_comment(wb, sheet = current_sheet(), dims = NULL)

wb_remove_comment(wb, sheet = current_sheet(), dims = "A1", ...)

Arguments

Details

If applying a comment with a string, it will use wb_comment() default values. If additional background colors are applied, RGB colors should be provided, either as hex code or with builtin R colors. The alpha channel is ignored.

Value

The Workbook object, invisibly.

See Also

wb_comment(), wb_add_thread()

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)

Description

Add conditional formatting to cells. You can find more details in vignette("conditional-formatting").

Usage

wb_add_conditional_formatting(
  wb,
  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),
  ...
)

wb_remove_conditional_formatting(
  wb,
  sheet = current_sheet(),
  dims = NULL,
  first = FALSE,
  last = FALSE
)

Arguments

Details

openxml uses the alpha channel first then RGB, whereas the usual default is RGBA.

Conditional formatting type accept different parameters. Unless noted, unlisted parameters are ignored.

expression

⁠[style]⁠
A Style object

⁠[rule]⁠
An Excel expression (as a character). Valid operators are: <, <=, >, >=, ==, !=

colorScale

⁠[style]⁠
A character vector of valid colors with length 2 or 3

⁠[rule]⁠
NULL or a character vector of valid colors of equal length to styles

dataBar

⁠[style]⁠
A character vector of valid colors with length 2 or 3

⁠[rule]⁠
A numeric vector specifying the range of the databar colors. Must be equal length to style

⁠[params$showValue]⁠
If FALSE the cell value is hidden. Default TRUE

⁠[params$gradient]⁠
If FALSE color gradient is removed. Default TRUE

⁠[params$border]⁠
If FALSE the border around the database is hidden. Default TRUE

duplicatedValues / uniqueValues / containsErrors

⁠[style]⁠
A Style object

contains

⁠[style]⁠
A Style object

⁠[rule]⁠
The text to look for within cells

between

⁠[style]⁠
A Style object.

⁠[rule]⁠
A numeric vector of length 2 specifying lower and upper bound (Inclusive)

topN

⁠[style]⁠
A Style object

⁠[params$rank]⁠
A numeric vector of length 1 indicating number of highest values. Default 5L

⁠[params$percent]⁠ If TRUE, uses percentage

bottomN

⁠[style]⁠
A Style object

⁠[params$rank]⁠
A numeric vector of length 1 indicating number of lowest values. Default 5L

⁠[params$percent]⁠
If TRUE, uses percentage

iconSet

⁠[params$showValue]⁠
If FALSE, the cell value is hidden. Default TRUE

⁠[params$reverse]⁠
If TRUE, the order is reversed. Default FALSE

⁠[params$percent]⁠
If TRUE, uses percentage

⁠[params$iconSet]⁠
Uses one of the implemented icon sets. Values must match the length of the icons in the set 3Arrows, 3ArrowsGray, 3Flags, 3Signs, 3Stars, 3Symbols, 3Symbols2, 3TrafficLights1, 3TrafficLights2, 3Triangles, 4Arrows, 4ArrowsGray, 4Rating, 4RedToBlack, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Boxes, 5Quarters, 5Rating. The default is 3TrafficLights1.

See Also

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, named_region-wb, row_heights-wb, 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("a")
wb$add_data(x = 1:4, col_names = FALSE)
wb$add_conditional_formatting(dims = wb_dims(cols = "A", rows = 1:4), rule = ">2")

Description

Add data to worksheet with optional styling.

Usage

wb_add_data(
  wb,
  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

Details

Formulae written using wb_add_formula() to a Workbook object will not get picked up by read_xlsx(). This is because only the formula is written and left to Excel to evaluate the formula when the file is opened in Excel. The string "_openxlsx_NA" is reserved for openxlsx2. If the data frame contains this string, the output will be broken.

Supported classes are data frames, matrices and vectors of various types and everything that can be converted into a data frame with as.data.frame(). Everything else that the user wants to write should either be converted into a vector or data frame or written in vector or data frame segments. This includes base classes such as table, which were coerced internally in the predecessor of this package.

Even vectors and data frames can consist of different classes. Many base classes are covered, though not all and far from all third-party classes. When data of an unknown class is written, it is handled with as.character(). It is not possible to write character nodes beginning with ⁠<r>⁠ or ⁠<r/>⁠. Both are reserved for internal functions. If you need these. You have to wrap the input string in fmt_txt().

The columns of x with class Date/POSIXt, currency, accounting, hyperlink, percentage are automatically styled as dates, currency, accounting, hyperlinks, percentages respectively.

Functions wb_add_data() and wb_add_data_table() behave quite similar. The distinction is that the latter creates a table in the worksheet that can be used for different kind of formulas and can be sorted independently, though is less flexible than basic cell regions.

Value

A wbWorkbook, invisibly.

See Also

Other workbook wrappers: base_font-wb, col_widths-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), 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, row_heights-wb, wb_add_conditional_formatting(), 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

## See formatting vignette for further examples.

## Options for default styling (These are the defaults)
options("openxlsx2.dateFormat" = "mm/dd/yyyy")
options("openxlsx2.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")
options("openxlsx2.numFmt" = NULL)

#############################################################################
## Create Workbook object and add worksheets
wb <- wb_workbook()

## Add worksheets
wb$add_worksheet("Cars")
wb$add_worksheet("Formula")

x <- mtcars[1:6, ]
wb$add_data("Cars", x, start_col = 2, start_row = 3, row_names = TRUE)

#############################################################################
## Hyperlinks
## - vectors/columns with class 'hyperlink' are written as hyperlinks'

v <- rep("https://CRAN.R-project.org/", 4)
names(v) <- paste0("Hyperlink", 1:4) # Optional: names will be used as display text
class(v) <- "hyperlink"
wb$add_data("Cars", x = v, dims = "B32")

#############################################################################
## Formulas
## - vectors/columns with class 'formula' are written as formulas'

df <- data.frame(
  x = 1:3, y = 1:3,
  z = paste(paste0("A", 1:3 + 1L), paste0("B", 1:3 + 1L), sep = "+"),
  stringsAsFactors = FALSE
)

class(df$z) <- c(class(df$z), "formula")

wb$add_data(sheet = "Formula", x = df)

#############################################################################
# update cell range and add mtcars
xlsxFile <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
wb2 <- wb_load(xlsxFile)

# read dataset with inlinestr
wb_to_df(wb2)
wb2 <- wb2 %>% wb_add_data(sheet = 1, mtcars, dims = wb_dims(4, 4))
wb_to_df(wb2)

Description

Add data to a worksheet and format as an Excel table.

Usage

wb_add_data_table(
  wb,
  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

Details

Formulae written using wb_add_formula() to a Workbook object will not get picked up by read_xlsx(). This is because only the formula is written and left to Excel to evaluate the formula when the file is opened in Excel. The string "_openxlsx_NA" is reserved for openxlsx2. If the data frame contains this string, the output will be broken.

Supported classes are data frames, matrices and vectors of various types and everything that can be converted into a data frame with as.data.frame(). Everything else that the user wants to write should either be converted into a vector or data frame or written in vector or data frame segments. This includes base classes such as table, which were coerced internally in the predecessor of this package.

Even vectors and data frames can consist of different classes. Many base classes are covered, though not all and far from all third-party classes. When data of an unknown class is written, it is handled with as.character(). It is not possible to write character nodes beginning with ⁠<r>⁠ or ⁠<r/>⁠. Both are reserved for internal functions. If you need these. You have to wrap the input string in fmt_txt().

The columns of x with class Date/POSIXt, currency, accounting, hyperlink, percentage are automatically styled as dates, currency, accounting, hyperlinks, percentages respectively.

Functions wb_add_data() and wb_add_data_table() behave quite similar. The distinction is that the latter creates a table in the worksheet that can be used for different kind of formulas and can be sorted independently, though is less flexible than basic cell regions.

Modify total row argument

It is possible to further tweak the total row. In addition to the default FALSE possible values are TRUE (the xlsx file will create column sums each variable).

In addition it is possible to tweak this further using a character string with one of the following functions for each variable: "average", "count", "countNums", "max", "min", "stdDev", "sum", "var". It is possible to leave the cell empty "none" or to create a text input using a named character with name text like: c(text = "Total"). It's also possible to pass other spreadsheet software functions if they return a single value and hence "SUM" would work too.

See Also

Other worksheet content functions: col_widths-wb, filter-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), 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, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), 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

wb <- wb_workbook()$add_worksheet()$
  add_data_table(
    x = as.data.frame(USPersonalExpenditure),
    row_names = TRUE,
    total_row = c(text = "Total", "none", "sum", "sum", "sum", "SUM")
  )