-
Notifications
You must be signed in to change notification settings - Fork 11
/
wb_add_data.Rd
172 lines (143 loc) · 5.51 KB
/
wb_add_data.Rd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/class-workbook-wrappers.R
\name{wb_add_data}
\alias{wb_add_data}
\title{Add data to a worksheet}
\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,
...
)
}
\arguments{
\item{wb}{A Workbook object containing a worksheet.}
\item{sheet}{The worksheet to write to. Can be the worksheet index or name.}
\item{x}{Object to be written. For classes supported look at the examples.}
\item{dims}{Spreadsheet cell range that will determine \code{start_col} and \code{start_row}: "A1", "A1:B2", "A:B"}
\item{start_col}{A vector specifying the starting column to write \code{x} to.}
\item{start_row}{A vector specifying the starting row to write \code{x} to.}
\item{array}{A bool if the function written is of type array}
\item{col_names}{If \code{TRUE}, column names of \code{x} are written.}
\item{row_names}{If \code{TRUE}, the row names of \code{x} are written.}
\item{with_filter}{If \code{TRUE}, add filters to the column name row.
NOTE: can only have one filter per worksheet.}
\item{name}{The name of a named region if specified.}
\item{sep}{Only applies to list columns. The separator used to collapse list
columns to a character vector e.g. \code{sapply(x$list_column, paste, collapse = sep)}.}
\item{apply_cell_style}{Should we write cell styles to the workbook}
\item{remove_cell_style}{keep the cell style?}
\item{na.strings}{Value used for replacing \code{NA} values from \code{x}. Default
\code{\link[=na_strings]{na_strings()}} uses the special \verb{#N/A} value within the workbook.}
\item{inline_strings}{write characters as inline strings}
\item{...}{additional arguments}
}
\value{
A \code{wbWorkbook}, invisibly.
}
\description{
Add data to worksheet with optional styling.
}
\details{
Formulae written using \code{\link[=wb_add_formula]{wb_add_formula()}} to a Workbook object will
not get picked up by \code{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 \code{"_openxlsx_NA"} is reserved for \code{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 \code{as.character()}.
It is not possible to write character nodes beginning with \verb{<r>} or \verb{<r/>}. Both
are reserved for internal functions. If you need these. You have to wrap
the input string in \code{fmt_txt()}.
}
\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, 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 = "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)
}
\seealso{
Other workbook wrappers:
\code{\link{base_font-wb}},
\code{\link{col_widths-wb}},
\code{\link{creators-wb}},
\code{\link{grouping-wb}},
\code{\link{row_heights-wb}},
\code{\link{wb_add_chartsheet}()},
\code{\link{wb_add_data_table}()},
\code{\link{wb_add_formula}()},
\code{\link{wb_add_pivot_table}()},
\code{\link{wb_add_slicer}()},
\code{\link{wb_add_worksheet}()},
\code{\link{wb_clone_worksheet}()},
\code{\link{wb_copy_cells}()},
\code{\link{wb_freeze_pane}()},
\code{\link{wb_merge_cells}()},
\code{\link{wb_save}()},
\code{\link{wb_set_last_modified_by}()},
\code{\link{wb_workbook}()}
Other worksheet content functions:
\code{\link{col_widths-wb}},
\code{\link{filter-wb}},
\code{\link{grouping-wb}},
\code{\link{named_region-wb}},
\code{\link{row_heights-wb}},
\code{\link{wb_add_conditional_formatting}()},
\code{\link{wb_add_data_table}()},
\code{\link{wb_add_formula}()},
\code{\link{wb_add_pivot_table}()},
\code{\link{wb_add_slicer}()},
\code{\link{wb_add_thread}()},
\code{\link{wb_freeze_pane}()},
\code{\link{wb_merge_cells}()}
}
\concept{workbook wrappers}
\concept{worksheet content functions}