1
2
3context(desc = "Deleting tables from worksheets")
4
5test_that("Deleting a Table Object", {
6  wb <- createWorkbook()
7  addWorksheet(wb, sheetName = "Sheet 1")
8  addWorksheet(wb, sheetName = "Sheet 2")
9  writeDataTable(wb, sheet = "Sheet 1", x = iris, tableName = "iris")
10  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
11
12  # Get table ----
13
14  expect_equal(length(getTables(wb, sheet = 1)), 2L)
15  expect_equal(length(getTables(wb, sheet = "Sheet 1")), 2L)
16
17  expect_equal(length(getTables(wb, sheet = 2)), 0)
18  expect_equal(length(getTables(wb, sheet = "Sheet 2")), 0)
19
20  expect_error(getTables(wb, sheet = 3))
21  expect_error(getTables(wb, sheet = "Sheet 3"))
22
23  expect_equal(getTables(wb, sheet = 1), c("iris", "mtcars"), check.attributes = FALSE)
24  expect_equal(getTables(wb, sheet = "Sheet 1"), c("iris", "mtcars"), check.attributes = FALSE)
25
26  expect_equal(attr(getTables(wb, sheet = 1), "refs"), c("A1:E151", "J1:T33"))
27  expect_equal(attr(getTables(wb, sheet = "Sheet 1"), "refs"), c("A1:E151", "J1:T33"))
28
29  expect_equal(length(wb$tables), 2L)
30
31  ## Deleting a worksheet ----
32
33  removeWorksheet(wb, 1)
34  expect_equal(length(wb$tables), 2L)
35  expect_equal(length(getTables(wb, sheet = 1)), 0)
36
37  expect_equal(attr(wb$tables, "tableName"), c("iris_openxlsx_deleted", "mtcars_openxlsx_deleted"))
38  expect_equal(attr(wb$tables, "sheet"), c(0, 0))
39
40
41
42
43  ###################################################################################
44  ## write same tables again
45
46  writeDataTable(wb, sheet = 1, x = iris, tableName = "iris")
47  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
48
49  expect_equal(attr(wb$tables, "tableName"), c("iris_openxlsx_deleted", "mtcars_openxlsx_deleted", "iris", "mtcars"))
50  expect_equal(attr(wb$tables, "sheet"), c(0, 0, 1, 1))
51
52  expect_equal(length(getTables(wb, sheet = 1)), 2L)
53  expect_equal(length(getTables(wb, sheet = "Sheet 2")), 2L)
54
55  expect_error(getTables(wb, sheet = 2))
56  expect_error(getTables(wb, sheet = "Sheet 1"))
57
58  expect_equal(getTables(wb, sheet = 1), c("iris", "mtcars"), check.attributes = FALSE)
59  expect_equal(getTables(wb, sheet = "Sheet 2"), c("iris", "mtcars"), check.attributes = FALSE)
60
61  expect_equal(attr(getTables(wb, sheet = 1), "refs"), c("A1:E151", "J1:T33"))
62  expect_equal(attr(getTables(wb, sheet = "Sheet 2"), "refs"), c("A1:E151", "J1:T33"))
63
64  expect_equal(length(wb$tables), 4L)
65
66
67  ###################################################################################
68  ## removeTable
69
70  ## remove iris and re-write it
71  removeTable(wb = wb, sheet = 1, table = "iris")
72
73  expect_equal(length(wb$tables), 4L)
74  expect_equal(wb$worksheets[[1]]$tableParts, "<tablePart r:id=\"rId6\"/>", check.attributes = FALSE)
75  expect_equal(attr(wb$worksheets[[1]]$tableParts, "tableName"), "mtcars")
76
77  expect_equal(attr(wb$tables, "tableName"), c(
78    "iris_openxlsx_deleted",
79    "mtcars_openxlsx_deleted",
80    "iris_openxlsx_deleted",
81    "mtcars"
82  ))
83
84  ## removeTable clears table object and all data
85  writeDataTable(wb, sheet = 1, x = iris, tableName = "iris", startCol = 1)
86  expect_equal(wb$worksheets[[1]]$tableParts, c("<tablePart r:id=\"rId6\"/>", "<tablePart r:id=\"rId7\"/>"), check.attributes = FALSE)
87  expect_equal(attr(wb$worksheets[[1]]$tableParts, "tableName"), c("mtcars", "iris"))
88
89
90  removeTable(wb = wb, sheet = 1, table = "iris")
91
92  expect_equal(length(wb$tables), 5L)
93  expect_equal(wb$worksheets[[1]]$tableParts, "<tablePart r:id=\"rId6\"/>", check.attributes = FALSE)
94  expect_equal(attr(wb$worksheets[[1]]$tableParts, "tableName"), "mtcars")
95
96  expect_equal(attr(wb$tables, "tableName"), c(
97    "iris_openxlsx_deleted",
98    "mtcars_openxlsx_deleted",
99    "iris_openxlsx_deleted",
100    "mtcars",
101    "iris_openxlsx_deleted"
102  ))
103
104
105  expect_equal(getTables(wb, sheet = 1), "mtcars", check.attributes = FALSE)
106})
107
108test_that("Save and load Table Deletion", {
109  temp_file <- temp_xlsx()
110
111  wb <- createWorkbook()
112  addWorksheet(wb, sheetName = "Sheet 1")
113  addWorksheet(wb, sheetName = "Sheet 2")
114  writeDataTable(wb, sheet = "Sheet 1", x = iris, tableName = "iris")
115  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
116
117
118  ###################################################################################
119  ## Deleting a worksheet
120
121  removeWorksheet(wb, 1)
122  expect_equal(length(wb$tables), 2L)
123  expect_equal(length(getTables(wb, sheet = 1)), 0)
124
125  expect_equal(attr(wb$tables, "tableName"), c("iris_openxlsx_deleted", "mtcars_openxlsx_deleted"))
126  expect_equal(attr(wb$tables, "sheet"), c(0, 0))
127
128
129  ## both table were written to sheet 1 and are expected to not exist after load
130  saveWorkbook(wb = wb, file = temp_file, overwrite = TRUE)
131  wb <- loadWorkbook(file = temp_file)
132  expect_null(wb$tables)
133  unlink(temp_file)
134
135
136
137
138  ###################################################################################
139  ## Deleting a table
140
141  wb <- createWorkbook()
142  addWorksheet(wb, sheetName = "Sheet 1")
143  addWorksheet(wb, sheetName = "Sheet 2")
144  writeDataTable(wb, sheet = "Sheet 1", x = iris, tableName = "iris")
145  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
146
147  ## remove iris and re-write it
148  removeTable(wb = wb, sheet = 1, table = "iris")
149  expect_equal(attr(wb$tables, "tableName"), c("iris_openxlsx_deleted", "mtcars"))
150
151  temp_file <- temp_xlsx()
152  saveWorkbook(wb = wb, file = temp_file, overwrite = TRUE)
153  wb <- loadWorkbook(file = temp_file)
154
155  expect_equal(length(wb$tables), 1L)
156  expect_equal(unname(attr(wb$tables, "tableName")), "mtcars")
157
158  expect_equal(wb$worksheets[[1]]$tableParts, "<tablePart r:id=\"rId3\"/>", check.attributes = FALSE) ## rId reset
159  expect_equal(unname(attr(wb$worksheets[[1]]$tableParts, "tableName")), "mtcars")
160  unlink(temp_file)
161
162
163
164  ## now delete the other table
165  wb <- createWorkbook()
166  addWorksheet(wb, sheetName = "Sheet 1")
167  addWorksheet(wb, sheetName = "Sheet 2")
168  writeDataTable(wb, sheet = "Sheet 1", x = iris, tableName = "iris")
169  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
170  writeDataTable(wb, sheet = 2, x = mtcars, tableName = "mtcars2", startCol = 3)
171
172  removeTable(wb = wb, sheet = 1, table = "iris")
173  removeTable(wb = wb, sheet = 1, table = "mtcars")
174  expect_equal(attr(wb$tables, "tableName"), c("iris_openxlsx_deleted", "mtcars_openxlsx_deleted", "mtcars2"))
175
176  temp_file <- temp_xlsx()
177  saveWorkbook(wb = wb, file = temp_file, overwrite = TRUE)
178  wb <- loadWorkbook(file = temp_file)
179
180
181  expect_equal(length(wb$tables), 1L)
182  expect_equal(unname(attr(wb$tables, "tableName")), "mtcars2")
183  expect_length(wb$worksheets[[1]]$tableParts, 0)
184  expect_equal(wb$worksheets[[2]]$tableParts, "<tablePart r:id=\"rId3\"/>", check.attributes = FALSE)
185  expect_equal(unname(attr(wb$worksheets[[2]]$tableParts, "tableName")), "mtcars2")
186  unlink(temp_file)
187
188
189  ## write tables back in
190  writeDataTable(wb, sheet = "Sheet 1", x = iris, tableName = "iris")
191  writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)
192
193  expect_equal(length(wb$tables), 3L)
194  expect_equal(unname(attr(wb$tables, "tableName")), c("mtcars2", "iris", "mtcars"))
195
196  expect_length(wb$worksheets[[1]]$tableParts, 2)
197  expect_equal(wb$worksheets[[1]]$tableParts, c("<tablePart r:id=\"rId4\"/>", "<tablePart r:id=\"rId5\"/>"), check.attributes = FALSE)
198  expect_equal(unname(attr(wb$worksheets[[1]]$tableParts, "tableName")), c("iris", "mtcars"))
199
200  expect_length(wb$worksheets[[2]]$tableParts, 1)
201  expect_equal(wb$worksheets[[2]]$tableParts, c("<tablePart r:id=\"rId3\"/>"), check.attributes = FALSE)
202  expect_equal(unname(attr(wb$worksheets[[2]]$tableParts, "tableName")), "mtcars2")
203
204  saveWorkbook(wb = wb, file = temp_file, overwrite = TRUE)
205
206
207  ## Ids should get reset after load
208  wb <- loadWorkbook(file = temp_file)
209
210  expect_equal(length(wb$tables), 3L)
211  expect_equal(unname(attr(wb$tables, "tableName")), c("iris", "mtcars", "mtcars2"))
212
213  expect_length(wb$worksheets[[1]]$tableParts, 2)
214  expect_equal(wb$worksheets[[1]]$tableParts, c("<tablePart r:id=\"rId3\"/>", "<tablePart r:id=\"rId4\"/>"), check.attributes = FALSE)
215  expect_equal(unname(attr(wb$worksheets[[1]]$tableParts, "tableName")), c("iris", "mtcars"))
216
217  expect_length(wb$worksheets[[2]]$tableParts, 1)
218  expect_equal(wb$worksheets[[2]]$tableParts, c("<tablePart r:id=\"rId5\"/>"), check.attributes = FALSE)
219  expect_equal(unname(attr(wb$worksheets[[2]]$tableParts, "tableName")), "mtcars2")
220
221  unlink(temp_file)
222})
223