-
Notifications
You must be signed in to change notification settings - Fork 1
/
add_table.R
215 lines (190 loc) · 6.09 KB
/
add_table.R
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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
#' @name add_table
#'
#' @title Function that adds a data frame to an (existing) .xlsx workbook sheet
#'
#' @param Table : data frame to be exported to the workbook sheet
#' @param WbTitle : workbook
#' @param SheetTitle : string used for the sheet's name
#' @param TableTitle : string used for the data frame's title
#' @param StartRow : export start line number in the sheet (by default 1)
#' @param StartCol : export start column number in the sheet (by default 1)
#' @param FormatList : list that indicates the format of each column of the data frame
#' @param HeightTableTitle : multiplier (if needed) for the height of the title line (by default 2)
#' @param TableFootnote1 : string for TableFootnote1
#' @param TableFootnote2 : string for TableFootnote2
#' @param TableFootnote3 : string for TableFootnote3
#' @param MergeCol : character vector that indicates the columns for which to merge the modalities
#' @param ByGroup character vector indicating the name of the columns by which to group
#' @param GroupName boolean indicating whether the name of the grouping variable should be written
#' @param asTable logical indicating if data should be written as an Excel Table (FALSE by default)
#'
#' @return excel wb object
#'
#' @export
add_table <- function(
Table,
WbTitle,
SheetTitle,
TableTitle,
StartRow = 1,
StartCol = 1,
FormatList = list(),
HeightTableTitle = 2,
TableFootnote1 = "",
TableFootnote2 = "",
TableFootnote3 = "",
MergeCol = character(0),
ByGroup = character(0),
GroupName = FALSE,
asTable = FALSE) {
# Assert parameters
assert_class(Table, "data.frame")
assert_class(WbTitle, "Workbook")
assert_character1(SheetTitle)
assert_character1(TableTitle)
assert_numeric1(StartRow)
assert_numeric1(StartCol)
assert_class(FormatList, "list")
assert_numeric1(HeightTableTitle)
assert_character1(TableFootnote1)
assert_character1(TableFootnote2)
assert_character1(TableFootnote3)
if (asTable & length(ByGroup) > 0L) {
stop("asTable cannot be TRUE if ByGroup is defined")
}
if (!all(MergeCol %in% colnames(Table))) {
stop("All elements of MergeCol must be existing column names of Table")
}
# If the sheet does not exist in the Excel file, we create it; otherwise, we invoke it
if (!(SheetTitle %in% names(WbTitle))) {
mysheet <- openxlsx::addWorksheet(WbTitle, SheetTitle)
} else {
mysheet <- SheetTitle
}
# Adjusting the size of columns and rows
openxlsx::setColWidths(WbTitle, sheet = mysheet, cols = StartCol + 1, widths = 45)
openxlsx::setColWidths(WbTitle, sheet = mysheet, cols = StartCol + 2, widths = 30)
openxlsx::setColWidths(WbTitle, sheet = mysheet, cols = c(StartCol + 3:38), widths = 20)
# Size of column headers
openxlsx::setRowHeights(WbTitle, sheet = mysheet, rows = StartRow + 2, heights = 20 * HeightTableTitle)
# Add title
openxlsx::setRowHeights(WbTitle, sheet = mysheet, rows = StartRow, heights = 20)
# Definition of column formats
openxlsx::writeData(
WbTitle,
sheet = mysheet,
x = TableTitle,
startCol = StartCol,
startRow = StartRow
)
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = StartCol,
rows = StartRow,
style = style$title
)
if (isTRUE(asTable)) {
writeDataFunction <- openxlsx::writeDataTable
} else {
writeDataFunction <- openxlsx::writeData
}
# Add a table
if (length(ByGroup) == 0L) {
writeDataFunction(
wb = WbTitle,
sheet = mysheet,
x = Table,
startRow = StartRow + 2,
startCol = StartCol + 1,
rowNames = FALSE,
headerStyle = style$col_header
)
lastrowtable <- StartRow + 2 + nrow(Table)
} else {
WbTitle <- writeDataByGroup(
wb = WbTitle,
sheet = mysheet,
x = Table,
startRow = StartRow + 2,
startCol = StartCol + 1,
rowNames = FALSE,
headerStyle = style$col_header,
group = ByGroup,
groupname = GroupName,
)
lastrowtable <- StartRow + 2 + nrow(Table) + nrow(unique(Table[ByGroup]))
}
# Format of the table's columns
sapply(seq_len(length(FormatList)), function(i) {
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = i + StartCol,
rows = ((StartRow + 3):lastrowtable),
style = FormatList[[i]]
)
})
# Add footnotes
openxlsx::writeData(
WbTitle,
sheet = mysheet, x = TableFootnote1,
startCol = StartCol, startRow = lastrowtable + 2
)
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = StartCol, rows = lastrowtable + 2,
style = style$footnote1
)
openxlsx::writeData(
WbTitle,
sheet = mysheet, x = TableFootnote2,
startCol = StartCol, startRow = lastrowtable + 3
)
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = StartCol, rows = lastrowtable + 3,
style = style$footnote2
)
openxlsx::writeData(
WbTitle,
sheet = mysheet, x = TableFootnote3,
startCol = StartCol, startRow = lastrowtable + 4
)
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = StartCol, rows = lastrowtable + 4,
style = style$footnote3
)
# If mergecol is filled in
if(length(MergeCol)>0) {
# loop on each column of mergecol
for (mycol in MergeCol) {
# distinct_mergecol count the number of unique modalities for each column of mergecol
distinct_mergecol <- length(unique(Table[[mycol]]))
# loop on each modality of mycol
for (i in (1:distinct_mergecol)) {
mergeCells(wb = WbTitle,
sheet = mysheet,
# here we add 1 because the table starts to be written from col 2 in workbook
cols = which(names(Table) %in% mycol)+1,
rows = convert_range_string(
range_string = get_indices_of_identical_elements(Table[[mycol]])[i]
) + StartRow + 2
)
}
openxlsx::addStyle(
WbTitle,
sheet = mysheet,
cols = which(names(Table) %in% mycol)+1,
rows = convert_range_string(
get_indices_from_vector(Table[[mycol]])
) + StartRow + 2,
style = style$mergedcell
)
}
}
}