Learn R Programming

openxlsx2 (version 0.5.1)

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,
  xy = NULL,
  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, xy = NULL, 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

xy

An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow).

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.

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.

See Also

write_datatable()

Other workbook wrappers: wb_add_chartsheet(), wb_add_data_table(), wb_add_formula(), wb_add_pivot_table(), wb_add_worksheet(), wb_clone_worksheet(), wb_copy_cells(), wb_creators, wb_freeze_pane(), wb_get_base_font(), wb_save(), wb_set_col_widths(), wb_set_last_modified_by(), wb_set_row_heights(), wb_workbook(), workbook_grouping, ws_cell_merge

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, xy = c("B", 32))

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

Run the code above in your browser using DataLab