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