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
)A Workbook object containing a worksheet.
The worksheet to write to. Can be the worksheet index or name.
Object to be written. For classes supported look at the examples.
A vector specifying the starting column to write to.
A vector specifying the starting row to write to.
Spreadsheet dimensions that will determine startCol and startRow: "A1", "A1:B2", "A:B"
A bool if the function written is of type array
If TRUE, column names of x are written.
If TRUE, data.frame row names of x are written.
If TRUE, add filters to the column name row. NOTE can only have one filter per worksheet.
If not NULL, a named region is defined.
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 styles when writing on the sheet
if writing into existing cells, should the cell style be removed?
Value used for replacing NA values from x. Default
na_strings() uses the special #N/A value within the workbook.
write characters as inline strings
A clone of `wb``
invisible(0)
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.
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
## 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