1 2 3 4context("Images and Tables.") 5 6 7test_that("Images and Tables - reordering and removing", { 8 if (FALSE) { 9 options("stringsAsFactors" = FALSE) 10 tempFile <- temp_xlsx("break") 11 12 getPlot <- function(i) { 13 n <- 5000 14 plot(1:n, rnorm(n)) 15 title(main = sprintf("Plot for Sheet: %s", i)) 16 } 17 18 df1 <- iris[1:5, 1:4] 19 df2 <- mtcars 20 21 22 df3 <- data.frame( 23 "Date" = Sys.Date() - 0:10, 24 "Logical" = sample(c(TRUE, FALSE), 1, replace = TRUE), 25 "Currency" = as.numeric(-5:5) * 100, 26 "Accounting" = as.numeric(-5:5), 27 "hLink" = "https://CRAN.R-project.org/", 28 "Percentage" = seq(-5, 5, length.out = 11), 29 "TinyNumber" = runif(11) / 1E9, stringsAsFactors = FALSE 30 ) 31 32 df3U <- df3 33 34 class(df3$Currency) <- "currency" 35 class(df3$Accounting) <- "accounting" 36 class(df3$hLink) <- "hyperlink" 37 class(df3$Percentage) <- "percentage" 38 class(df3$TinyNumber) <- "scientific" 39 40 41 df4 <- data.frame("X" = 1:10000, "Y" = sample(LETTERS, size = 10000, replace = TRUE)) 42 df5 <- USJudgeRatings 43 44 hs <- createStyle(fontColour = "blue", textRotation = 45) 45 46 47 wb <- createWorkbook() 48 expect_equal(names(wb), character(0)) 49 50 addWorksheet(wb = wb, sheetName = "Sheet 1", gridLines = FALSE, tabColour = "red", zoom = 75) 51 writeDataTable(wb, sheet = 1, x = df1, startCol = 7, startRow = 10, tableName = "Sheet1Table1") 52 expect_equal(names(wb), "Sheet 1") 53 54 55 addWorksheet(wb, sheetName = "Sheet 2", tabColour = "purple") 56 writeDataTable(wb, sheet = "Sheet 2", x = df2, startCol = 2, startRow = 2, rowNames = TRUE) 57 expect_equal(names(wb), c("Sheet 1", "Sheet 2")) 58 59 60 61 addWorksheet(wb, sheetName = "Sheet 3", tabColour = "green") 62 writeDataTable(wb, sheet = 3, x = df3, startCol = 1, startRow = 1) 63 expect_equal(names(wb), c("Sheet 1", "Sheet 2", "Sheet 3")) 64 65 addWorksheet(wb, sheetName = "Sheet 4", tabColour = "orange") 66 writeDataTable(wb, sheet = 4, x = df4) 67 expect_equal(names(wb), c("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")) 68 69 addWorksheet(wb, sheetName = "Sheet 5", tabColour = "yellow") 70 writeData(wb, sheet = "Sheet 5", x = df5, rowNames = TRUE) 71 expect_equal(names(wb), c("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5")) 72 73 74 75 worksheetOrder(wb) <- c(1, 3, 5, 4, 2) 76 expect_equal(names(wb), c("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5")) 77 78 ## save and load 1 79 saveWorkbook(wb, file = tempFile, overwrite = TRUE) 80 81 wb <- loadWorkbook(tempFile) 82 expect_equal(names(wb), c("Sheet 1", "Sheet 3", "Sheet 5", "Sheet 4", "Sheet 2")) 83 84 85 expect_equal(df1, read.xlsx(wb, sheet = 1)) 86 expect_equal(df1, read.xlsx(wb, sheet = "Sheet 1")) 87 expect_equal(df1, read.xlsx(tempFile, sheet = 1)) 88 expect_equal(df1, read.xlsx(tempFile, sheet = "Sheet 1")) 89 90 91 expect_equal(df3U, read.xlsx(wb, sheet = 2, detectDates = TRUE)) 92 expect_equal(df3U, read.xlsx(wb, sheet = "Sheet 3", detectDates = TRUE)) 93 expect_equal(df3U, read.xlsx(tempFile, sheet = 2, detectDates = TRUE)) 94 expect_equal(df3U, read.xlsx(tempFile, sheet = "Sheet 3", detectDates = TRUE)) 95 96 97 expect_equal(df5, read.xlsx(wb, sheet = 3, rowNames = TRUE)) 98 expect_equal(df5, read.xlsx(wb, sheet = "Sheet 5", rowNames = TRUE)) 99 expect_equal(df5, read.xlsx(tempFile, sheet = 3, rowNames = TRUE)) 100 expect_equal(df5, read.xlsx(tempFile, sheet = "Sheet 5", rowNames = TRUE)) 101 102 103 expect_equal(df4, read.xlsx(wb, sheet = 4)) 104 expect_equal(df4, read.xlsx(wb, sheet = "Sheet 4")) 105 expect_equal(df4, read.xlsx(tempFile, sheet = 4)) 106 expect_equal(df4, read.xlsx(tempFile, sheet = "Sheet 4")) 107 108 109 expect_equal(df2, read.xlsx(wb, sheet = 5, rowNames = TRUE)) 110 expect_equal(df2, read.xlsx(wb, sheet = "Sheet 2", rowNames = TRUE)) 111 expect_equal(df2, read.xlsx(tempFile, sheet = 5, rowNames = TRUE)) 112 expect_equal(df2, read.xlsx(tempFile, sheet = "Sheet 2", rowNames = TRUE)) 113 114 115 116 ## remove "Sheet 5" by index (3) 117 removeWorksheet(wb, sheet = 3) 118 expect_equal(names(wb), c("Sheet 1", "Sheet 3", "Sheet 4", "Sheet 2")) 119 120 ## remove sheet "Sheet 4" 121 removeWorksheet(wb, sheet = "Sheet 4") 122 expect_equal(names(wb), c("Sheet 1", "Sheet 3", "Sheet 2")) 123 124 125 ## Introduce some images 126 getPlot(1) 127 insertPlot(wb = wb, sheet = "Sheet 1", startCol = 14, startRow = 3) 128 129 getPlot(2) 130 insertPlot(wb = wb, sheet = "Sheet 2", startCol = 14, startRow = 3) 131 132 getPlot(3) 133 insertPlot(wb = wb, sheet = "Sheet 3", startCol = 14, startRow = 3) 134 135 136 expect_true(any(grepl("image1", wb$drawings_rels[[1]]))) 137 expect_true(any(grepl("image3", wb$drawings_rels[[2]]))) 138 expect_true(any(grepl("image2", wb$drawings_rels[[3]]))) 139 140 141 142 ## put back to original order 143 worksheetOrder(wb) <- c(1, 3, 2) 144 saveWorkbook(wb, file = tempFile, overwrite = TRUE) 145 146 wb <- loadWorkbook(file = tempFile) 147 148 149 ## drawings added in order 150 expect_true(any(grepl("image1", wb$drawings_rels[[1]]))) 151 expect_true(any(grepl("image2", wb$drawings_rels[[2]]))) 152 expect_true(any(grepl("image3", wb$drawings_rels[[3]]))) 153 154 155 ## Introduce some more images 156 getPlot("1_2") 157 insertPlot(wb = wb, sheet = "Sheet 1", startCol = 14, startRow = 25) 158 159 getPlot("2_2") 160 insertPlot(wb = wb, sheet = "Sheet 2", startCol = 14, startRow = 25) 161 162 163 getPlot("3_2") 164 insertPlot(wb = wb, sheet = "Sheet 3", startCol = 14, startRow = 25) 165 166 saveWorkbook(wb, file = tempFile, overwrite = TRUE) 167 wb <- loadWorkbook(tempFile) 168 169 worksheetOrder(wb) <- c(3, 2, 1) 170 saveWorkbook(wb, file = tempFile, overwrite = TRUE) 171 wb <- loadWorkbook(tempFile) 172 173 174 hl <- rep("https://google.com.au", 5) 175 names(hl) <- sprintf("Link to google %s", 1:5) 176 class(hl) <- "hyperlink" 177 writeData(wb, "Sheet 1", hl) 178 179 ## Add in some column widths 180 181 setColWidths(wb, sheet = 1, cols = 1:50, widths = "auto") 182 worksheetOrder(wb) <- c(3, 2, 1) 183 removeWorksheet(wb, sheet = "Sheet 2") 184 185 saveWorkbook(wb, file = tempFile, overwrite = TRUE) 186 wb <- loadWorkbook(tempFile) 187 188 expect_equal(names(wb), c("Sheet 1", "Sheet 3")) 189 expect_equal(df1, read.xlsx(tempFile, sheet = 1, startRow = 10)) 190 expect_equal(df3U, read.xlsx(tempFile, sheet = 2, detectDates = TRUE)) 191 192 expect_equal(df1, read.xlsx(wb, sheet = 1, startRow = 10)) 193 expect_equal(df3U, read.xlsx(wb, sheet = 2, detectDates = TRUE)) 194 195 196 unlink(tempFile, recursive = TRUE, force = TRUE) 197 rm(wb) 198 } 199}) 200 201test_that("setColWidths() should support zero-length cols", { 202 file <- temp_xlsx() 203 on.exit(unlink(file), add = TRUE) 204 wb <- createWorkbook() 205 ws <- addWorksheet(wb, "empty") 206 tbl <- data.frame(A = 1:3) 207 writeData(wb, ws, tbl) 208 setColWidths(wb, ws, integer(0L), widths = 12) 209 saveWorkbook(wb, file) 210 x <- readWorkbook(file) 211 expect_equal(x, tbl) 212}) 213