wb_add_data: Add data to a worksheet

Description

Add data to worksheet with optional styling.

Write an object to worksheet with optional styling.

Usage

wb_add_data(
  wb,
  sheet = current_sheet(),
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  array = FALSE,
  colNames = TRUE,
  rowNames = FALSE,
  withFilter = FALSE,
  name = NULL,
  sep = ", ",
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE
)
write_data(
  wb,
  sheet,
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  array = FALSE,
  colNames = TRUE,
  rowNames = FALSE,
  withFilter = FALSE,
  sep = ", ",
  name = NULL,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE
)

Value

A clone of `wb``

invisible(0)

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.
startCol: A vector specifying the starting column to write to.
startRow: A vector specifying the starting row to write to.
dims: Spreadsheet dimensions that will determine startCol and startRow: "A1", "A1:B2", "A:B"
array: A bool if the function written is of type array
colNames: If TRUE, column names of x are written.
rowNames: If TRUE, data.frame row names of x are written.
withFilter: 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).
applyCellStyle: apply styles when writing on the sheet
removeCellStyle: if writing into existing cells, should the cell style be removed?
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

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, startCol = 2, startRow = 3, rowNames = 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", "inline_str.xlsx", package = "openxlsx2")
wb2 <- wb_load(xlsxFile)

# read dataset with inlinestr
wb_to_df(wb2)
# read_xlsx(wb2)
write_data(wb2, 1, mtcars, startCol = 4, startRow = 4)
wb_to_df(wb2)