Add data to worksheet with optional styling.

Write an object to worksheet with optional styling.

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
)

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

Value

A clone of `wb``

invisible(0)

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

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.

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, 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)
#>    PairIndex Drug1 Drug2 Conc1 Conc2   Response ConcUnit
#> 2          1   abc   def 10000 10000 -1.7960711       nM
#> 3          1   abc   def 10000  3000  1.0102900       nM
#> 4          1   abc   def 10000  1000  0.4490178       nM
#> 5          1   abc   def 10000   300  0.0000000       nM
#> 6          1   abc   def 10000   100  0.8980355       nM
#> 7          1   abc   def 10000    30  0.1122544       nM
#> 8          1   abc   def 10000    10  3.1431244       nM
#> 9          1   abc   def 10000     0  1.4593078       nM
#> 10         2   abc   def 10000     0  1.6838167       nM
#> 11         2   abc   def 10000    10 -0.7857811       nM
# read_xlsx(wb2)
write_data(wb2, 1, mtcars, startCol = 4, startRow = 4)
#> A Workbook object.
#>  
#> Worksheets:
#>  Sheets: Synergy 
#>  Write order: 1
wb_to_df(wb2)
#>    PairIndex Drug1 Drug2 Conc1 Conc2           Response ConcUnit   NA    NA
#> 2          1   abc   def 10000 10000 -1.796071094480823       nM <NA>  <NA>
#> 3          1   abc   def 10000  3000  1.010289990645463       nM <NA>  <NA>
#> 4          1   abc   def   mpg   cyl               disp       hp drat    wt
#> 5          1   abc   def    21     6                160      110  3.9  2.62
#> 6          1   abc   def    21     6                160      110  3.9 2.875
#> 7          1   abc   def  22.8     4                108       93 3.85  2.32
#> 8          1   abc   def  21.4     6                258      110 3.08 3.215
#> 9          1   abc   def  18.7     8                360      175 3.15  3.44
#> 10         2   abc   def  18.1     6                225      105 2.76  3.46
#> 11         2   abc   def  14.3     8                360      245 3.21  3.57
#> 12        NA  <NA>  <NA>  24.4     4              146.7       62 3.69  3.19
#> 13        NA  <NA>  <NA>  22.8     4              140.8       95 3.92  3.15
#> 14        NA  <NA>  <NA>  19.2     6              167.6      123 3.92  3.44
#> 15        NA  <NA>  <NA>  17.8     6              167.6      123 3.92  3.44
#> 16        NA  <NA>  <NA>  16.4     8              275.8      180 3.07  4.07
#> 17        NA  <NA>  <NA>  17.3     8              275.8      180 3.07  3.73
#> 18        NA  <NA>  <NA>  15.2     8              275.8      180 3.07  3.78
#> 19        NA  <NA>  <NA>  10.4     8                472      205 2.93  5.25
#> 20        NA  <NA>  <NA>  10.4     8                460      215    3 5.424
#> 21        NA  <NA>  <NA>  14.7     8                440      230 3.23 5.345
#> 22        NA  <NA>  <NA>  32.4     4               78.7       66 4.08   2.2
#> 23        NA  <NA>  <NA>  30.4     4               75.7       52 4.93 1.615
#> 24        NA  <NA>  <NA>  33.9     4               71.1       65 4.22 1.835
#> 25        NA  <NA>  <NA>  21.5     4              120.1       97  3.7 2.465
#> 26        NA  <NA>  <NA>  15.5     8                318      150 2.76  3.52
#> 27        NA  <NA>  <NA>  15.2     8                304      150 3.15 3.435
#> 28        NA  <NA>  <NA>  13.3     8                350      245 3.73  3.84
#> 29        NA  <NA>  <NA>  19.2     8                400      175 3.08 3.845
#> 30        NA  <NA>  <NA>  27.3     4                 79       66 4.08 1.935
#> 31        NA  <NA>  <NA>    26     4              120.3       91 4.43  2.14
#> 32        NA  <NA>  <NA>  30.4     4               95.1      113 3.77 1.513
#> 33        NA  <NA>  <NA>  15.8     8                351      264 4.22  3.17
#> 34        NA  <NA>  <NA>  19.7     6                145      175 3.62  2.77
#> 35        NA  <NA>  <NA>    15     8                301      335 3.54  3.57
#> 36        NA  <NA>  <NA>  21.4     4                121      109 4.11  2.78
#>       NA   NA   NA   NA   NA
#> 2   <NA> <NA> <NA> <NA> <NA>
#> 3   <NA> <NA> <NA> <NA> <NA>
#> 4   qsec   vs   am gear carb
#> 5  16.46    0    1    4    4
#> 6  17.02    0    1    4    4
#> 7  18.61    1    1    4    1
#> 8  19.44    1    0    3    1
#> 9  17.02    0    0    3    2
#> 10 20.22    1    0    3    1
#> 11 15.84    0    0    3    4
#> 12    20    1    0    4    2
#> 13  22.9    1    0    4    2
#> 14  18.3    1    0    4    4
#> 15  18.9    1    0    4    4
#> 16  17.4    0    0    3    3
#> 17  17.6    0    0    3    3
#> 18    18    0    0    3    3
#> 19 17.98    0    0    3    4
#> 20 17.82    0    0    3    4
#> 21 17.42    0    0    3    4
#> 22 19.47    1    1    4    1
#> 23 18.52    1    1    4    2
#> 24  19.9    1    1    4    1
#> 25 20.01    1    0    3    1
#> 26 16.87    0    0    3    2
#> 27  17.3    0    0    3    2
#> 28 15.41    0    0    3    4
#> 29 17.05    0    0    3    2
#> 30  18.9    1    1    4    1
#> 31  16.7    0    1    5    2
#> 32  16.9    1    1    5    2
#> 33  14.5    0    1    5    4
#> 34  15.5    0    1    5    6
#> 35  14.6    0    1    5    8
#> 36  18.6    1    1    4    2