/
aa-examples.Rmd
165 lines (128 loc) · 6.46 KB
/
aa-examples.Rmd
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
---
title: "Examples gallery"
output: rmarkdown::html_vignette
vignette: >
%\VignetteIndexEntry{aa-examples}
%\VignetteEngine{knitr::rmarkdown}
%\VignetteEncoding{UTF-8}
---
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>"
)
options(rmarkdown.html_vignette.check_title = FALSE)
```
```{r setup}
library(tablexlsx)
library(dplyr, warn.conflicts = FALSE)
```
# Examples gallery
## Table of Contents
1. [Simply export a data frame to an xlsx file ](#simply-export-a-data-frame-to-an-xlsx-file)
2. [Export a list of several data frames to an xlsx file (each data frame in a different sheet)](#export-a-list-of-several-data-frames-to-an-xlsx-file-each-data-frame-in-a-different-sheet)
3. [Export a data frame to an xlsx file (merging modalities for one column)](#export-a-data-frame-to-an-xlsx-file-merging-modalities-for-one-column)
4. [Export a data frame to an xlsx file (merging modalities for several columns)](#export-a-data-frame-to-an-xlsx-file-merging-modalities-for-several-columns)
5. [Export a list of several data frames to an xlsx file (several data frames in a same sheet)](#export-a-list-of-several-data-frames-to-an-xlsx-file-several-data-frames-in-a-same-sheet)
6. [Export a list of data frames to an named xlsx file by specifying which data frame goes in which sheet, styling each column, giving a title and footnotes...](#export-a-list-of-data-frames-to-an-named-xlsx-file-by-specifying-which-data-frame-goes-in-which-sheet-styling-each-column-giving-a-title-and-footnotes)
Let's define an export folder that will be the same for all the examples on this page
```{r define-export-folder}
mypath <- tempdir()
```
> Important : in this vignette, all examples use `%>%` to pass the `object` argument in the `toxlsx()` function, but it works interchangeably with these 3 syntaxes below :
```{r equivalent-syntaxes, eval = FALSE}
toxlsx(object = iris, path = mypath)
iris |> toxlsx(path = mypath)
iris %>% toxlsx(path = mypath)
```
## 1. Simply export a data frame to an xlsx file
```{r first-export}
iris %>% toxlsx(path = mypath)
```
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex1.png" width="100%" />
## 2. Export a list of several data frames to an xlsx file (each data frame in a different sheet)
```{r list-export}
list(iris,cars) %>% toxlsx(path = mypath)
```
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex2.png" width="100%" />
## 3. Export a data frame to an xlsx file (merging modalities for one column)
```{r df-export-merge-one}
# Create df1
df1 <- data.frame(
group = c("dupont","dupont","arnold","arnold"),
name = c("toto","tata","tutu","tete"),
volume = c(10,8,12,15)
)
# Export
df1 %>% toxlsx(path = mypath, mergecol = "group")
```
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex3.png" width="100%" />
## 4. Export a data frame to an xlsx file (merging modalities for several columns)
```{r df-export-merge-several}
# Create df2
df2 <- data.frame(
country = c(rep("france",4),rep("england",4)),
group = c(rep("dupont",2),rep("martin",2),
rep("arnold",2),rep("harry",2)),
name = c("toto","tata","tutu","tete",
"momo","mama","mumu","meme"),
volume = c(10,8,12,15,
14,10,5,12)
)
# Export
df2 %>% toxlsx(path = mypath, mergecol = c("country","group"))
```
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex4.png" width="100%" />
## 5. Export a list of several data frames to an xlsx file (several data frames in a same sheet)
```{r list-export-same-sheet}
tb1 <- data.frame(tables = c(rep("iris",5),rep("cars",2)),
var = c(names(iris),names(cars)))
tb2 <- data.frame(tables = c("iris","cars","cars"),
rownumber = c(150,50,32))
list(tb1,tb2) %>%
toxlsx(tosheet = list("tb1" = "mydata",
"tb2" = "mydata"),
footnote1 = list("tb1" = "The data set contains 3 classes of 50 instances each, where each class refers to a type of iris plant.",
"tb2" = "The data give the speed of cars and the distances taken to stop. Note that the data were recorded in the 1920s."),
footnote2 = list("tb1" = "Predicted attribute: class of iris plant.",
"tb2" = "Data recorded in the 1920s"),
footnote3 = list("tb1" = "Source : R.A. Fisher",
"tb2" = "Source : M. Ezekiel"),
path=mypath)
```
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex5.png" width="100%" />
## 6. Export a list of data frames to an named xlsx file by specifying which data frame goes in which sheet, styling each column, giving a title and footnotes...
```{r list-export-complicated}
iris <- iris %>% head()
cars <- cars %>% head()
list(iris,cars) %>%
toxlsx(tosheet = list("iris" = "first",
"cars" = "second"),
title = list("iris" = "Head of iris",
"cars" = "Head of cars"),
# The `columnstyle` argument is optional in toxlsx().
# It is used only if you want to specify the format of each column
columnstyle = list("iris" = list("c1" = "decimal",
"c2" = "decimal",
"c3" = "number",
"c4" = "number",
"c5" = "character"),
"cars" = list("c1" = "number",
"c2" = "number")),
footnote1 = list("iris" = "The data set contains 3 classes of 50 instances each, where each class refers to a type of iris plant.",
"cars" = "The data give the speed of cars and the distances taken to stop. Note that the data were recorded in the 1920s."),
footnote2 = list("iris" = "Predicted attribute: class of iris plant.",
"cars" = "Data recorded in the 1920s"),
footnote3 = list("iris" = "Source : R.A. Fisher",
"cars" = "Source : M. Ezekiel"),
filename = "Results",
path = mypath)
```
The equivalent with the {openxlsx} syntax would be much longer and more painful to write. See [here](https://gist.github.com/ddotta/25ae0ec6c142dd7327057990ef955ae4) for the equivalent of the sixth example.
Preview of the xlsx file in LibreOffice Calc :
<img src="../man/figures/preview_Calc_ex6.png" width="100%" />