1package io.protobase.f7.spreadsheet;
  2
  3import io.protobase.f7.errors.ValueException;
  4import io.protobase.f7.testutils.TestExecution;
  5import org.junit.Test;
  6
  7/**
  8 * "Collateral Indexing" is what I call it when you're using a range inside a cell that runs parallel column-wise,
  9 * parallel row-wise, or both, but is in another grid.
 10 * <p>
 11 * Parallel Column-Wise:  C4 using A1:A10 results in a collateral index lookup of A4. The 4 is what's used here.
 12 * Parallel Row-Wise:  C4 using A1:D1 results in a collateral index lookup of C1. The C is what's used here.
 13 * Both:  C4 using OtherGrid!A1:C10 results in a collateral lookup of OtherGrid!C4. The C and the 4 are important.
 14 */
 15public class GeneralCollateralIndexRangeTest extends TestExecution {
 16  @Test
 17  public void test_ColumnWise() {
 18    runner()
 19        .addCell("Alpha", "A1", "= 1")
 20        .addCell("Alpha", "A2", "= 2")
 21        .addCell("Alpha", "A3", "= 3")
 22        .addCell("Alpha", "A4", "= 4")
 23        .addCell("Alpha", "B1", "= A1:A5")
 24        .addCell("Alpha", "B2", "= A1:A5")
 25        .addCell("Alpha", "B3", "= A1:A5")
 26        .addCell("Alpha", "B4", "= A1:A5")
 27        .addCell("Alpha", "B5", "= A1:A5")
 28        .addExpectedValue("Alpha", "B1", 1.0)
 29        .addExpectedValue("Alpha", "B2", 2.0)
 30        .addExpectedValue("Alpha", "B3", 3.0)
 31        .addExpectedValue("Alpha", "B4", 4.0)
 32        .addExpectedEmptyComputedValue("Alpha", "B5")
 33        .run();
 34  }
 35
 36  @Test
 37  public void test_RowWise() {
 38    runner()
 39        .addCell("Alpha", "A1", "= 1")
 40        .addCell("Alpha", "B1", "= 2")
 41        .addCell("Alpha", "C1", "= 3")
 42        .addCell("Alpha", "D1", "= 4")
 43        .addCell("Alpha", "A2", "= A1:E1")
 44        .addCell("Alpha", "B2", "= A1:E1")
 45        .addCell("Alpha", "C2", "= A1:E1")
 46        .addCell("Alpha", "D2", "= A1:E1")
 47        .addCell("Alpha", "E2", "= A1:E1")
 48        .addExpectedValue("Alpha", "A2", 1.0)
 49        .addExpectedValue("Alpha", "B2", 2.0)
 50        .addExpectedValue("Alpha", "C2", 3.0)
 51        .addExpectedValue("Alpha", "D2", 4.0)
 52        .addExpectedEmptyComputedValue("Alpha", "E2")
 53        .run();
 54  }
 55
 56  @Test
 57  public void test_ColumnWiseWithRowOffsetFirst() {
 58    runner()
 59        .addCell("Alpha", "A1", "= 1")
 60        .addCell("Alpha", "A2", "= 2")
 61        .addCell("Alpha", "A3", "= 3")
 62        .addCell("Alpha", "A4", "= 4")
 63        .addCell("Alpha", "B1", "= A1:A")
 64        .addCell("Alpha", "B2", "= A1:A")
 65        .addCell("Alpha", "B3", "= A1:A")
 66        .addCell("Alpha", "B4", "= A1:A")
 67        .addCell("Alpha", "B5", "= A1:A")
 68        .addExpectedValue("Alpha", "B1", 1.0)
 69        .addExpectedValue("Alpha", "B2", 2.0)
 70        .addExpectedValue("Alpha", "B3", 3.0)
 71        .addExpectedValue("Alpha", "B4", 4.0)
 72        .addExpectedEmptyComputedValue("Alpha", "B5")
 73        .run();
 74  }
 75
 76  @Test
 77  public void test_ColumnWiseWithRowOffsetLast() {
 78    runner()
 79        .addCell("Alpha", "A1", "= 1")
 80        .addCell("Alpha", "A2", "= 2")
 81        .addCell("Alpha", "A3", "= 3")
 82        .addCell("Alpha", "A4", "= 4")
 83        .addCell("Alpha", "B1", "= A:A1")
 84        .addCell("Alpha", "B2", "= A:A1")
 85        .addCell("Alpha", "B3", "= A:A1")
 86        .addCell("Alpha", "B4", "= A:A1")
 87        .addCell("Alpha", "B5", "= A:A1")
 88        .addExpectedValue("Alpha", "B1", 1.0)
 89        .addExpectedValue("Alpha", "B2", 2.0)
 90        .addExpectedValue("Alpha", "B3", 3.0)
 91        .addExpectedValue("Alpha", "B4", 4.0)
 92        .addExpectedEmptyComputedValue("Alpha", "B5")
 93        .run();
 94  }
 95
 96  @Test
 97  public void test_RowWiseWithColumnOffsetFirst() {
 98    runner()
 99        .addCell("Alpha", "A1", "= 1")
100        .addCell("Alpha", "B1", "= 2")
101        .addCell("Alpha", "C1", "= 3")
102        .addCell("Alpha", "D1", "= 4")
103        .addCell("Alpha", "A2", "= A1:1")
104        .addCell("Alpha", "B2", "= A1:1")
105        .addCell("Alpha", "C2", "= A1:1")
106        .addCell("Alpha", "D2", "= A1:1")
107        .addCell("Alpha", "E2", "= A1:1")
108        .addExpectedValue("Alpha", "A2", 1.0)
109        .addExpectedValue("Alpha", "B2", 2.0)
110        .addExpectedValue("Alpha", "C2", 3.0)
111        .addExpectedValue("Alpha", "D2", 4.0)
112        .addExpectedEmptyComputedValue("Alpha", "E2")
113        .run();
114  }
115
116  @Test
117  public void test_RowWiseWithColumnOffsetLast() {
118    runner()
119        .addCell("Alpha", "A1", "= 1")
120        .addCell("Alpha", "B1", "= 2")
121        .addCell("Alpha", "C1", "= 3")
122        .addCell("Alpha", "D1", "= 4")
123        .addCell("Alpha", "A2", "= 1:A1")
124        .addCell("Alpha", "B2", "= 1:A1")
125        .addCell("Alpha", "C2", "= 1:A1")
126        .addCell("Alpha", "D2", "= 1:A1")
127        .addCell("Alpha", "E2", "= 1:A1")
128        .addExpectedValue("Alpha", "A2", 1.0)
129        .addExpectedValue("Alpha", "B2", 2.0)
130        .addExpectedValue("Alpha", "C2", 3.0)
131        .addExpectedValue("Alpha", "D2", 4.0)
132        .addExpectedEmptyComputedValue("Alpha", "E2")
133        .run();
134  }
135
136  @Test
137  public void test_ColumnAndRowWise_Error() {
138    runner()
139        .addCell("Alpha", "A1", "= 1")
140        .addCell("Alpha", "A2", "= 2")
141        .addCell("Alpha", "B1", "= 3")
142        .addCell("Alpha", "B2", "= 4")
143        .addCell("Alpha", "K5", "= A1:B2")
144        .addExpectedValue("Alpha", "K5", new ValueException())
145        .run();
146  }
147
148  @Test
149  public void test_ColumnAndRowWise_CrossGrid() {
150    runner()
151        .addCell("Alpha", "A1", "= 1")
152        .addCell("Alpha", "A2", "= 2")
153        .addCell("Alpha", "A3", "= 3")
154        .addCell("Alpha", "A4", "= 4")
155        .addCell("Alpha", "B1", "= 1.1")
156        .addCell("Alpha", "B2", "= 2.1")
157        .addCell("Alpha", "B3", "= 3.1")
158        .addCell("Alpha", "B4", "= 4.1")
159        .addCell("Beta", "A1", "=Alpha!A1:B5")
160        .addCell("Beta", "A2", "=Alpha!A1:B5")
161        .addCell("Beta", "A3", "=Alpha!A1:B5")
162        .addCell("Beta", "A4", "=Alpha!A1:B5")
163        .addCell("Beta", "A5", "=Alpha!A1:B5")
164        .addCell("Beta", "B1", "=Alpha!A1:B5")
165        .addCell("Beta", "B2", "=Alpha!A1:B5")
166        .addCell("Beta", "B3", "=Alpha!A1:B5")
167        .addCell("Beta", "B4", "=Alpha!A1:B5")
168        .addCell("Beta", "B5", "=Alpha!A1:B5")
169        .addExpectedValue("Beta", "A1", 1.0)
170        .addExpectedValue("Beta", "A2", 2.0)
171        .addExpectedValue("Beta", "A3", 3.0)
172        .addExpectedValue("Beta", "A4", 4.0)
173        .addExpectedEmptyComputedValue("Beta", "A5")
174        .addExpectedValue("Beta", "B1", 1.1)
175        .addExpectedValue("Beta", "B2", 2.1)
176        .addExpectedValue("Beta", "B3", 3.1)
177        .addExpectedValue("Beta", "B4", 4.1)
178        .addExpectedEmptyComputedValue("Beta", "B5")
179        .run();
180  }
181
182  @Test
183  public void test_ArrayNotFound() {
184    runner()
185        .addCell("Alpha", "A1", "= 1")
186        .addCell("Beta", "M14", "=Alpha!A1:B5")
187        .addExpectedValue("Beta", "M14", new ValueException())
188        .run();
189  }
190
191  @Test
192  public void test_IndexErasureFromArrayLiteral() {
193    runner()
194        .addCell("Alpha", "A1", "= 1")
195        .addCell("Alpha", "A2", "= 2")
196        .addCell("Alpha", "A3", "= 3")
197        .addCell("Alpha", "A4", "= 4")
198        .addCell("Alpha", "B1", "= 1.1")
199        .addCell("Alpha", "B2", "= 2.1")
200        .addCell("Alpha", "B3", "= 3.1")
201        .addCell("Alpha", "B4", "= 4.1")
202        .addCell("Beta", "A1", "= {Alpha!A1:B5}")
203        .addExpectedValue("Beta", "A1", 1.0)
204        .addExpectedValue("Beta", "A2", 2.0)
205        .addExpectedValue("Beta", "A3", 3.0)
206        .addExpectedValue("Beta", "A4", 4.0)
207        .addExpectedEmptyComputedValue("Beta", "A5")
208        .addExpectedValue("Beta", "B1", 1.1)
209        .addExpectedValue("Beta", "B2", 2.1)
210        .addExpectedValue("Beta", "B3", 3.1)
211        .addExpectedValue("Beta", "B4", 4.1)
212        .addExpectedEmptyComputedValue("Beta", "B5")
213        .run();
214  }
215
216  @Test
217  public void test_All_RangeOperationsWithIndex() {
218    runner()
219        .addCell("Alpha", "A1", "= 10")
220        .addCell("Alpha", "A2", "= 20")
221        .addCell("Alpha", "A3", "= 30")
222        .addCell("Alpha", "B1", "= A1:A4")
223        .addCell("Alpha", "B2", "= A1:A4")
224        .addCell("Alpha", "B3", "= A1:A4")
225        .addCell("Alpha", "B4", "= A1:A4")
226        .addCell("Alpha", "B5", "= {A1:A4}")
227        .addCell("Alpha", "C10", "= {A1:A4 + 5}")
228        .addCell("Alpha", "C11", "= {A1 + 5}")
229        .addCell("Alpha", "C12", "= {A1:A1 + 5}")
230        .addExpectedValue("Alpha", "A1", 10.0)
231        .addExpectedValue("Alpha", "A2", 20.0)
232        .addExpectedValue("Alpha", "A3", 30.0)
233        .addExpectedValue("Alpha", "B1", 10.0)
234        .addExpectedValue("Alpha", "B2", 20.0)
235        .addExpectedValue("Alpha", "B3", 30.0)
236        .addExpectedEmptyComputedValue("Alpha", "B4")
237        .addExpectedValue("Alpha", "B5", 10.0)
238        .addExpectedValue("Alpha", "B6", 20.0)
239        .addExpectedValue("Alpha", "B7", 30.0)
240        .addExpectedValue("Alpha", "C10", new ValueException())
241        .addExpectedValue("Alpha", "C11", 15.0)
242        .addExpectedValue("Alpha", "C12", 15.0)
243        .run();
244  }
245}