-
Notifications
You must be signed in to change notification settings - Fork 11
/
test-read_from_created_wb.R
189 lines (137 loc) · 5.17 KB
/
test-read_from_created_wb.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
test_that("Reading from new workbook", {
curr_wd <- getwd()
wb <- wb_workbook()
for (i in 1:4) {
wb$add_worksheet(sprintf("Sheet %s", i))
}
## colNames = TRUE, rowNames = TRUE
wb$add_data(sheet = 1, x = mtcars, colNames = TRUE, rowNames = TRUE, startRow = 10, startCol = 5)
x <- read_xlsx(wb, 1, colNames = TRUE, rowNames = TRUE)
expect_equal(object = mtcars, expected = x, ignore_attr = TRUE)
## colNames = TRUE, rowNames = FALSE
wb$add_data(sheet = 2, x = mtcars, colNames = TRUE, rowNames = FALSE, startRow = 10, startCol = 5)
x <- read_xlsx(wb, sheet = 2, colNames = TRUE, rowNames = FALSE)
expect_equal(object = mtcars, expected = x, ignore_attr = TRUE)
expect_equal(object = colnames(mtcars), expected = colnames(x), ignore_attr = TRUE)
## colNames = FALSE, rowNames = TRUE
wb$add_data(sheet = 3, x = mtcars, colNames = FALSE, rowNames = TRUE, startRow = 2, startCol = 2)
x <- read_xlsx(wb, sheet = 3, colNames = FALSE, rowNames = TRUE)
expect_equal(object = mtcars, expected = x, ignore_attr = TRUE)
expect_equal(object = rownames(mtcars), expected = rownames(x))
## colNames = FALSE, rowNames = FALSE
wb$add_data(sheet = 4, x = mtcars, colNames = FALSE, rowNames = FALSE, startRow = 12, startCol = 1)
x <- read_xlsx(wb, sheet = 4, colNames = FALSE, rowNames = FALSE)
expect_equal(object = mtcars, expected = x, ignore_attr = TRUE)
expect_equal(object = getwd(), curr_wd)
rm(wb)
})
test_that("Reading NAs and NaN values", {
fileName <- file.path(tempdir(), "NaN.xlsx")
na.string <- "*"
wb <- wb_workbook()
## data
A <- data.frame(
X = c(-pi / 0, NA, NaN),
Y = letters[1:3],
Z = c(pi / 0, 99, NaN),
Z2 = c(1, NaN, NaN),
stringsAsFactors = FALSE
)
wb$add_worksheet("Sheet 1")$add_data(1, A)
# not used
B <- A
B[B == -Inf] <- NaN
B[B == Inf] <- NaN
wb$add_worksheet("Sheet 2")$add_data(2, B)
# not used
C <- B
is_na <- sapply(C, is.na)
is_nan <- sapply(C, is.nan)
C[is_na & !is_nan] <- na.string
is_nan_after <- sapply(C, is.nan)
C[is_nan & !is_nan_after] <- NA
wb$add_worksheet("Sheet 3")$add_data(3, C)
# write file
wb_save(wb, file = fileName, overwrite = TRUE)
exp <- data.frame(
X = c("#NUM!", NA, "#VALUE!"),
Y = letters[1:3],
Z = c("#NUM!", 99, "#VALUE!"),
Z2 = c(1, "#VALUE!", "#VALUE!"),
stringsAsFactors = FALSE
)
expect_equal(read_xlsx(fileName), exp, ignore_attr = TRUE)
unlink(fileName, recursive = TRUE, force = TRUE)
})
test_that("dims != rows & cols", {
wb <- wb_workbook()$
add_worksheet("sheet1")$
add_data(1, data.frame("A" = 1))
got1 <- wb_to_df(wb, dims = "A2:D5", colNames = FALSE)
got2 <- wb_to_df(wb, rows = 2:5, cols = 1:4, colNames = FALSE)
expect_equal(got1, got2)
got3 <- wb_to_df(wb, rows = c(2:3, 5:6), cols = c(1, 3:5), colNames = FALSE)
expect_equal(c(4, 4), dim(got3))
expect_equal(c("A", "C", "D", "E"), colnames(got3))
expect_equal(c("2", "3", "5", "6"), rownames(got3))
got4 <- wb_to_df(wb, rows = 1:5, colNames = FALSE)
expect_equal(c(5, 1), dim(got4))
got5 <- wb_to_df(wb, cols = 1:5, colNames = FALSE)
expect_equal(c(2, 5), dim(got5))
got6 <- wb_to_df(wb, startRow = 4, cols = 1:4, colNames = FALSE)
expect_true(all(is.na(got6)))
expect_equal("4", rownames(got6))
})
test_that("read startCol", {
wb <- wb_workbook()$add_worksheet()$add_data(x = cars, startCol = "E")
got <- wb_to_df(wb, startCol = 1, colNames = FALSE)
expect_equal(LETTERS[1:6], names(got))
got <- wb_to_df(wb, startCol = "F", colNames = FALSE)
expect_equal(LETTERS[6], names(got))
})
test_that("reading with multiple sections in freezePane works", {
temp <- temp_xlsx()
wb <- wb_workbook()$add_worksheet()
wb$worksheets[[1]]$freezePane <- "<pane xSplit=\"7320\" ySplit=\"1640\"/><selection pane=\"topRight\"/><selection pane=\"bottomLeft\"/><selection pane=\"bottomRight\" activeCell=\"C5\" sqref=\"C5\"/>"
wb$save(temp)
expect_silent(wb <- wb_load(temp))
})
test_that("skipEmtpyCols keeps empty named columns", {
## initialize empty cells
na_mat <- matrix(NA, nrow = 22, ncol = 7)
## create artificial data with empty column
dat <- iris[1:20, ]
dat$Species <- NA_real_
dat <- rbind(dat, NA)
## create the workbook
wb <- wb_workbook()$
add_worksheet()$
add_data(x = na_mat, colNames = FALSE)$
add_data(x = dat)
got <- wb_to_df(wb, skipEmptyCols = TRUE)
expect_equal(dat, got, ignore_attr = TRUE)
})
test_that("reading with pre defined types works", {
dat <- data.frame(
numeric = rnorm(n = 5),
integer = sample(1:5, 5, TRUE),
date = Sys.Date() - 0:4,
datetime = Sys.time() - 0:4,
character = letters[1:5]
)
wb <- wb_workbook()$add_worksheet()$add_data(x = dat)
got <- wb_to_df(wb)
expect_equal(got, dat, ignore_attr = TRUE)
types <- c(numeric = 1, integer = 1, date = 2, datetime = 3, character = 0)
got <- wb_to_df(wb, types = types)
expect_equal(got, dat, ignore_attr = TRUE)
})
test_that("wb_load contains path", {
tmp <- temp_xlsx()
wb_workbook()$add_worksheet()$add_worksheet()$save(tmp)
wb_load(tmp)$remove_worksheet()$save()
wb <- wb_load(tmp)
exp <- c(`Sheet 1` = "Sheet 1")
got <- wb$get_sheet_names()
expect_equal(exp, got)
})