name:
src/test/java/io/protobase/f7/spreadsheet/GeneralRangeTest.java
-rw-r--r--
13171
1package io.protobase.f7.spreadsheet;
2
3import io.protobase.f7.errors.RefException;
4import io.protobase.f7.errors.ValueException;
5import io.protobase.f7.testutils.TestExecution;
6import org.junit.Test;
7
8public class GeneralRangeTest extends TestExecution {
9 @Test
10 public void test_SingleCellRange() {
11 runner()
12 .addCell("Alpha", "A1", "= 1")
13 .addCell("Alpha", "Z99", "= SUM(A1)")
14 .addExpectedValue("Alpha", "Z99", 1.0).run();
15 runner()
16 .addCell("Alpha", "A1", "= 1")
17 .addCell("Alpha", "Z99", "= A1")
18 .addExpectedValue("Alpha", "Z99", 1.0).run();
19 runner()
20 .addCell("Alpha", "Z99", "= -A1")
21 .addExpectedValue("Alpha", "Z99", 0.0).run();
22 runner()
23 .addCell("Alpha", "Z99", "= A1")
24 .addExpectedEmptyComputedValue("Alpha", "Z99").run();
25 }
26
27 @Test
28 public void test_GridSingleCellRange() {
29 runner()
30 .addCell("Alpha", "A1", "= 1")
31 .addCell("Beta", "Z99", "= SUM(Alpha!A1)")
32 .addExpectedValue("Beta", "Z99", 1.0).run();
33 }
34
35 @Test
36 public void test_BiCellRange() {
37 runner()
38 .addCell("Alpha", "A1", "= 1")
39 .addCell("Alpha", "A2", "= 2")
40 .addCell("Alpha", "A3", "= 3")
41 .addCell("Alpha", "Z99", "= SUM(A1:A3)")
42 .addExpectedValue("Alpha", "Z99", 6.0).run();
43 }
44
45 @Test
46 public void test_GridBiCellRange() {
47 runner()
48 .addCell("Alpha", "A1", "= 1")
49 .addCell("Alpha", "A2", "= 2")
50 .addCell("Alpha", "A3", "= 3")
51 .addCell("Beta", "Z99", "= SUM(Alpha!A1:A3)")
52 .addExpectedValue("Beta", "Z99", 6.0).run();
53 }
54
55 @Test
56 public void test_MultiCellRange() {
57 runner()
58 .addCell("Alpha", "A1", "= 1")
59 .addCell("Alpha", "A2", "= 2")
60 .addCell("Alpha", "A3", "= 3")
61 .addCell("Alpha", "A4", "= 4")
62 .addCell("Alpha", "A5", "= 5")
63 .addCell("Alpha", "A6", "= 6")
64 .addCell("Alpha", "B1", "= 1.1")
65 .addCell("Alpha", "B2", "= 2.1")
66 .addCell("Alpha", "B3", "= 3.1")
67 .addCell("Alpha", "B4", "= 4.1")
68 .addCell("Alpha", "B5", "= 5.1")
69 .addCell("Alpha", "B6", "= 6.1")
70 .addCell("Alpha", "C1", "= 1.2")
71 .addCell("Alpha", "C2", "= 2.2")
72 .addCell("Alpha", "C3", "= 3.2")
73 .addCell("Alpha", "C4", "= 4.2")
74 .addCell("Alpha", "C5", "= 5.2")
75 .addCell("Alpha", "C6", "= 6.2")
76 .addCell("Alpha", "Z99", "= SUM(A1:A2:B5:C6)")
77 .addExpectedValue("Alpha", "Z99", 64.80000000000001).run();
78 }
79
80 @Test
81 public void test_MultiCellRange_OutOfOrder() {
82 runner()
83 .addCell("Alpha", "A1", "= 1")
84 .addCell("Alpha", "A2", "= 2")
85 .addCell("Alpha", "A3", "= 3")
86 .addCell("Alpha", "A4", "= 4")
87 .addCell("Alpha", "A5", "= 5")
88 .addCell("Alpha", "A6", "= 6")
89 .addCell("Alpha", "B1", "= 1.1")
90 .addCell("Alpha", "B2", "= 2.1")
91 .addCell("Alpha", "B3", "= 3.1")
92 .addCell("Alpha", "B4", "= 4.1")
93 .addCell("Alpha", "B5", "= 5.1")
94 .addCell("Alpha", "B6", "= 6.1")
95 .addCell("Alpha", "C1", "= 1.2")
96 .addCell("Alpha", "C2", "= 2.2")
97 .addCell("Alpha", "C3", "= 3.2")
98 .addCell("Alpha", "C4", "= 4.2")
99 .addCell("Alpha", "C5", "= 5.2")
100 .addCell("Alpha", "C6", "= 6.2")
101 .addCell("Alpha", "Z99", "= SUM(A2:A1:C6:B5)")
102 .addExpectedValue("Alpha", "Z99", 64.80000000000001).run();
103 }
104
105 @Test
106 public void test_GridMultiCellRange() {
107 runner()
108 .addCell("Alpha", "A1", "= 1")
109 .addCell("Alpha", "A2", "= 2")
110 .addCell("Alpha", "A3", "= 3")
111 .addCell("Alpha", "A4", "= 4")
112 .addCell("Alpha", "A5", "= 5")
113 .addCell("Alpha", "A6", "= 6")
114 .addCell("Alpha", "B1", "= 1.1")
115 .addCell("Alpha", "B2", "= 2.1")
116 .addCell("Alpha", "B3", "= 3.1")
117 .addCell("Alpha", "B4", "= 4.1")
118 .addCell("Alpha", "B5", "= 5.1")
119 .addCell("Alpha", "B6", "= 6.1")
120 .addCell("Alpha", "C1", "= 1.2")
121 .addCell("Alpha", "C2", "= 2.2")
122 .addCell("Alpha", "C3", "= 3.2")
123 .addCell("Alpha", "C4", "= 4.2")
124 .addCell("Alpha", "C5", "= 5.2")
125 .addCell("Alpha", "C6", "= 6.2")
126 .addCell("Beta", "Z99", "= SUM(Alpha!A1:A2:Alpha!B5:C6)")
127 .addCell("Beta", "Z100", "= SUM(Alpha!A1:Alpha!A2:Alpha!B5:Alpha!C6)")
128 .addExpectedValue("Beta", "Z99", 64.80000000000001)
129 .addExpectedValue("Beta", "Z100", 64.80000000000001)
130 .run();
131 }
132
133 @Test
134 public void test_GridMultiCellRange_OutOfOrder() {
135 runner()
136 .addCell("Alpha", "A1", "= 1")
137 .addCell("Alpha", "A2", "= 2")
138 .addCell("Alpha", "A3", "= 3")
139 .addCell("Alpha", "A4", "= 4")
140 .addCell("Alpha", "A5", "= 5")
141 .addCell("Alpha", "A6", "= 6")
142 .addCell("Alpha", "B1", "= 1.1")
143 .addCell("Alpha", "B2", "= 2.1")
144 .addCell("Alpha", "B3", "= 3.1")
145 .addCell("Alpha", "B4", "= 4.1")
146 .addCell("Alpha", "B5", "= 5.1")
147 .addCell("Alpha", "B6", "= 6.1")
148 .addCell("Alpha", "C1", "= 1.2")
149 .addCell("Alpha", "C2", "= 2.2")
150 .addCell("Alpha", "C3", "= 3.2")
151 .addCell("Alpha", "C4", "= 4.2")
152 .addCell("Alpha", "C5", "= 5.2")
153 .addCell("Alpha", "C6", "= 6.2")
154 .addCell("Beta", "Z99", "= SUM(Alpha!A2:A1:Alpha!C6:B5)")
155 .addCell("Beta", "Z100", "= SUM(Alpha!A2:Alpha!A1:Alpha!C6:Alpha!B5)")
156 .addExpectedValue("Beta", "Z99", 64.80000000000001)
157 .addExpectedValue("Beta", "Z100", 64.80000000000001)
158 .run();
159 }
160
161 @Test
162 public void test_ErrorFromCrossGridMultiCellRange() {
163 runner()
164 .addCell("Alpha", "A1", "= 1")
165 .addCell("Beta", "Z99", "= SUM(Alpha!A2:A1:Beta!C6:B5)")
166 .addCell("Beta", "Z100", "= SUM(Alpha!A2:Beta!A1:Alpha!C6:Beta!B5)")
167 .addExpectedValue("Beta", "Z99", new ValueException())
168 .addExpectedValue("Beta", "Z100", new ValueException())
169 .run();
170 runner()
171 .addCell("Alpha", "A1", "= 1")
172 .addCell("Beta", "Z99", "= SUM(Alpha!A2:A1:Alpha!C6:B5)")
173 .addExpectedValue("Beta", "Z99", 1.0)
174 .run();
175 }
176
177 @Test
178 public void test_ErrorFromUnresolvedGridName() {
179 runner()
180 .addCell("Alpha", "A1", "= 1")
181 .addCell("Beta", "Z99", "= SUM(NotFound!A1)")
182 .addExpectedValue("Beta", "Z99", new RefException())
183 .run();
184 }
185
186 @Test
187 public void test_PartialBlankRange() {
188 runner()
189 .addCell("Alpha", "A1", "= 1")
190 .addCell("Alpha", "A2", "= 2")
191 .addCell("Alpha", "A3", "= 3")
192 .addCell("Alpha", "A4", "= 4")
193 .addCell("Alpha", "A5", "= 5")
194 .addCell("Alpha", "A6", "= 6")
195 .addCell("Alpha", "C1", "= 1.2")
196 .addCell("Alpha", "C2", "= 2.2")
197 .addCell("Alpha", "C3", "= 3.2")
198 .addCell("Alpha", "C4", "= 4.2")
199 .addCell("Alpha", "C5", "= 5.2")
200 .addCell("Alpha", "C6", "= 6.2")
201 .addCell("Alpha", "Z99", "= SUM(A1:C6)")
202 .addExpectedValue("Alpha", "Z99", 43.2)
203 .run();
204 }
205
206 @Test
207 public void test_FullBlankRange() {
208 runner()
209 .addCell("Alpha", "Z99", "= SUM(A1:C6)")
210 .addExpectedValue("Alpha", "Z99", 0.0)
211 .run();
212 }
213
214 @Test
215 public void test_ColumnWiseRange() {
216 runner()
217 .addCell("Alpha", "A1", "= 1")
218 .addCell("Alpha", "A2", "= 2")
219 .addCell("Alpha", "A3", "= 3")
220 .addCell("Alpha", "A4", "= 4")
221 .addCell("Alpha", "A5", "= 5")
222 .addCell("Alpha", "A6", "= 6")
223 .addCell("Alpha", "B1", "= 1.1")
224 .addCell("Alpha", "B2", "= 2.1")
225 .addCell("Alpha", "B3", "= 3.1")
226 .addCell("Alpha", "B4", "= 4.1")
227 .addCell("Alpha", "B5", "= 5.1")
228 .addCell("Alpha", "B6", "= 6.1")
229 .addCell("Alpha", "C1", "= 1.2")
230 .addCell("Alpha", "C2", "= 2.2")
231 .addCell("Alpha", "C3", "= 3.2")
232 .addCell("Alpha", "C4", "= 4.2")
233 .addCell("Alpha", "C5", "= 5.2")
234 .addCell("Alpha", "C6", "= 6.2")
235 .addCell("Alpha", "Z99", "= SUM(A:C)")
236 .addExpectedValue("Alpha", "Z99", 64.80000000000001)
237 .run();
238 }
239
240 @Test
241 public void test_ColumnWiseWithRowOffsetFirst() {
242 runner()
243 .addCell("Alpha", "A1", "= 1")
244 .addCell("Alpha", "A2", "= 2")
245 .addCell("Alpha", "A3", "= 3")
246 .addCell("Alpha", "A4", "= 4")
247 .addCell("Alpha", "A5", "= 5")
248 .addCell("Alpha", "A6", "= 6")
249 .addCell("Alpha", "B1", "= 1.1")
250 .addCell("Alpha", "B2", "= 2.1")
251 .addCell("Alpha", "B3", "= 3.1")
252 .addCell("Alpha", "B4", "= 4.1")
253 .addCell("Alpha", "B5", "= 5.1")
254 .addCell("Alpha", "B6", "= 6.1")
255 .addCell("Alpha", "C1", "= 1.2")
256 .addCell("Alpha", "C2", "= 2.2")
257 .addCell("Alpha", "C3", "= 3.2")
258 .addCell("Alpha", "C4", "= 4.2")
259 .addCell("Alpha", "C5", "= 5.2")
260 .addCell("Alpha", "C6", "= 6.2")
261 .addCell("Alpha", "Z99", "= SUM(A3:C)") // TODO/HACK: This is a feature only available in G-Sheets. Excel doesn't allow.
262 .addExpectedValue("Alpha", "Z99", 55.2)
263 .run();
264 }
265
266 @Test
267 public void test_ColumnWiseWithRowOffsetLast() {
268 runner()
269 .addCell("Alpha", "A1", "= 1")
270 .addCell("Alpha", "A2", "= 2")
271 .addCell("Alpha", "A3", "= 3")
272 .addCell("Alpha", "A4", "= 4")
273 .addCell("Alpha", "A5", "= 5")
274 .addCell("Alpha", "A6", "= 6")
275 .addCell("Alpha", "B1", "= 1.1")
276 .addCell("Alpha", "B2", "= 2.1")
277 .addCell("Alpha", "B3", "= 3.1")
278 .addCell("Alpha", "B4", "= 4.1")
279 .addCell("Alpha", "B5", "= 5.1")
280 .addCell("Alpha", "B6", "= 6.1")
281 .addCell("Alpha", "C1", "= 1.2")
282 .addCell("Alpha", "C2", "= 2.2")
283 .addCell("Alpha", "C3", "= 3.2")
284 .addCell("Alpha", "C4", "= 4.2")
285 .addCell("Alpha", "C5", "= 5.2")
286 .addCell("Alpha", "C6", "= 6.2")
287 .addCell("Alpha", "Z99", "= SUM(A:C3)") // TODO/HACK: This is a feature only available in G-Sheets. Excel doesn't allow.
288 .addExpectedValue("Alpha", "Z99", 55.2)
289 .run();
290 }
291
292 @Test
293 public void test_RowWise() {
294 runner()
295 .addCell("Alpha", "A1", "= 1")
296 .addCell("Alpha", "A2", "= 2")
297 .addCell("Alpha", "A3", "= 3")
298 .addCell("Alpha", "A4", "= 4")
299 .addCell("Alpha", "A5", "= 5")
300 .addCell("Alpha", "A6", "= 6")
301 .addCell("Alpha", "B1", "= 1.1")
302 .addCell("Alpha", "B2", "= 2.1")
303 .addCell("Alpha", "B3", "= 3.1")
304 .addCell("Alpha", "B4", "= 4.1")
305 .addCell("Alpha", "B5", "= 5.1")
306 .addCell("Alpha", "B6", "= 6.1")
307 .addCell("Alpha", "C1", "= 1.2")
308 .addCell("Alpha", "C2", "= 2.2")
309 .addCell("Alpha", "C3", "= 3.2")
310 .addCell("Alpha", "C4", "= 4.2")
311 .addCell("Alpha", "C5", "= 5.2")
312 .addCell("Alpha", "C6", "= 6.2")
313 .addCell("Alpha", "Z99", "= SUM(1:6)") // TODO/HACK: This is a feature only available in G-Sheets. Excel doesn't allow.
314 .addExpectedValue("Alpha", "Z99", 64.80000000000001)
315 .run();
316 }
317
318 @Test
319 public void test_RowWiseWithColumnOffsetFirst() {
320 runner()
321 .addCell("Alpha", "A1", "= 1")
322 .addCell("Alpha", "A2", "= 2")
323 .addCell("Alpha", "A3", "= 3")
324 .addCell("Alpha", "A4", "= 4")
325 .addCell("Alpha", "A5", "= 5")
326 .addCell("Alpha", "A6", "= 6")
327 .addCell("Alpha", "B1", "= 1.1")
328 .addCell("Alpha", "B2", "= 2.1")
329 .addCell("Alpha", "B3", "= 3.1")
330 .addCell("Alpha", "B4", "= 4.1")
331 .addCell("Alpha", "B5", "= 5.1")
332 .addCell("Alpha", "B6", "= 6.1")
333 .addCell("Alpha", "C1", "= 1.2")
334 .addCell("Alpha", "C2", "= 2.2")
335 .addCell("Alpha", "C3", "= 3.2")
336 .addCell("Alpha", "C4", "= 4.2")
337 .addCell("Alpha", "C5", "= 5.2")
338 .addCell("Alpha", "C6", "= 6.2")
339 .addCell("Alpha", "Z99", "= SUM(B1:6)") // TODO/HACK: This is a feature only available in G-Sheets. Excel doesn't allow.
340 .addExpectedValue("Alpha", "Z99", 43.8)
341 .run();
342 }
343
344 @Test
345 public void test_RowWiseWithColumnOffsetLast() {
346 runner()
347 .addCell("Alpha", "A1", "= 1")
348 .addCell("Alpha", "A2", "= 2")
349 .addCell("Alpha", "A3", "= 3")
350 .addCell("Alpha", "A4", "= 4")
351 .addCell("Alpha", "A5", "= 5")
352 .addCell("Alpha", "A6", "= 6")
353 .addCell("Alpha", "B1", "= 1.1")
354 .addCell("Alpha", "B2", "= 2.1")
355 .addCell("Alpha", "B3", "= 3.1")
356 .addCell("Alpha", "B4", "= 4.1")
357 .addCell("Alpha", "B5", "= 5.1")
358 .addCell("Alpha", "B6", "= 6.1")
359 .addCell("Alpha", "C1", "= 1.2")
360 .addCell("Alpha", "C2", "= 2.2")
361 .addCell("Alpha", "C3", "= 3.2")
362 .addCell("Alpha", "C4", "= 4.2")
363 .addCell("Alpha", "C5", "= 5.2")
364 .addCell("Alpha", "C6", "= 6.2")
365 .addCell("Alpha", "Z99", "= SUM(1:B6)") // TODO/HACK: This is a feature only available in G-Sheets. Excel doesn't allow.
366 .addExpectedValue("Alpha", "Z99", 43.8)
367 .run();
368 }
369}