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