wb_add_data: Add data to a worksheet

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

Value

A clone of wb

Arguments

wb: A Workbook object containing a worksheet.
sheet: The worksheet to write to. Can be the worksheet index or name.
x: Object to be written. For classes supported look at the examples.
dims: Spreadsheet dimensions that will determine start_col and start_row: "A1", "A1:B2", "A:B"
start_col: A vector specifying the starting column to write to.
start_row: A vector specifying the starting row to write to.
array: A bool if the function written is of type array
col_names: If TRUE, column names of x are written.
row_names: If TRUE, data.frame row names of x are written.
with_filter: If TRUE, add filters to the column name row. NOTE can only have one filter per worksheet.
name: If not NULL, a named region is defined.
sep: Only applies to list columns. The separator used to collapse list columns to a character vector e.g. sapply(x$list_column, paste, collapse = sep).
apply_cell_style: Should we write cell styles to the workbook
remove_cell_style: keep the cell style?
na.strings: Value used for replacing NA values from x. Default na_strings() uses the special #N/A value within the workbook.
inline_strings: write characters as inline strings
...: additional arguments

Details

Formulae written using write_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. 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().

Examples

Run this code

## 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 = c("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)