1import {
  2  CHOOSE,
  3  ADDRESS,
  4  COLUMNS,
  5  ROWS
  6} from "../../src/Formulas/Lookup";
  7import * as ERRORS from "../../src/Errors";
  8import {
  9  catchAndAssertEquals,
 10  test,
 11  assertEquals
 12} from "../Utils/Asserts";
 13import {
 14  Cell
 15} from "../../src/Cell";
 16
 17
 18test("CHOOSE", function(){
 19  assertEquals(CHOOSE(1, 1, 2, 3), 1);
 20  assertEquals(CHOOSE(2, 1, 2, 3), 2);
 21  catchAndAssertEquals(function() {
 22    CHOOSE.apply(this, []);
 23  }, ERRORS.NA_ERROR);
 24  catchAndAssertEquals(function() {
 25    CHOOSE.apply(this, [1]);
 26  }, ERRORS.NA_ERROR);
 27  catchAndAssertEquals(function() {
 28    CHOOSE.apply(this, [4, 1, 2, 3]);
 29  }, ERRORS.NUM_ERROR);
 30  catchAndAssertEquals(function() {
 31    CHOOSE.apply(this, [0, 1, 2, 3]);
 32  }, ERRORS.NUM_ERROR);
 33});
 34
 35
 36test("ADDRESS", function(){
 37  assertEquals(ADDRESS(2170, 2, 4, true, "SheetOne"), "SheetOne!B2170");
 38  assertEquals(ADDRESS(2170, 2, 4, true, "Sheet_One"), "Sheet_One!B2170");
 39  assertEquals(ADDRESS(2170, 2, 4, true, "Sheet!One"), "'Sheet!One'!B2170");
 40  assertEquals(ADDRESS(2170, 2, 4, true, "Sheet^One"), "'Sheet^One'!B2170");
 41  assertEquals(ADDRESS(2170, 444, 4, true, "SheetOne"), "SheetOne!QB2170");
 42  assertEquals(ADDRESS(2170, 2, 4, true, "Sheet One"), "'Sheet One'!B2170");
 43  assertEquals(ADDRESS(2170,2,4,true,"Formula Demo Sheet"), "'Formula Demo Sheet'!B2170");
 44  assertEquals(ADDRESS(1, 1, 4, true), "A1");
 45  assertEquals(ADDRESS(2, 1, 4), "A2");
 46  assertEquals(ADDRESS(2, 2, 4), "B2");
 47  assertEquals(ADDRESS(1, 2, 4), "B1");
 48  assertEquals(ADDRESS(1, 1, 4), "A1");
 49  assertEquals(ADDRESS(1, 1, 3), "$A1");
 50  assertEquals(ADDRESS(1, 1, 2), "A$1");
 51  assertEquals(ADDRESS(1, 1, 1), "$A$1");
 52  assertEquals(ADDRESS(1, 1, 4, false), "R[1]C[1]");
 53  assertEquals(ADDRESS(1, 1, 3, false), "R[1]C1");
 54  assertEquals(ADDRESS(1, 1, 2, false), "R1C[1]");
 55  assertEquals(ADDRESS(1, 1, 1, false), "R1C1");
 56  assertEquals(ADDRESS.apply(this, [2170, 2, 1, 100, false]), "FALSE!$B$2170");
 57  assertEquals(ADDRESS.apply(this, [2170, 2, 1, 100, 123456]), "'123456'!$B$2170");
 58  catchAndAssertEquals(function() {
 59    ADDRESS.apply(this, [1]);
 60  }, ERRORS.NA_ERROR);
 61  catchAndAssertEquals(function() {
 62    ADDRESS(1, 2, 5);
 63  }, ERRORS.NUM_ERROR);
 64  catchAndAssertEquals(function() {
 65    ADDRESS(-1, 2, 1);
 66  }, ERRORS.VALUE_ERROR);
 67  catchAndAssertEquals(function() {
 68    ADDRESS(1, -2, 1);
 69  }, ERRORS.VALUE_ERROR);
 70});
 71
 72
 73test("COLUMNS", function(){
 74  assertEquals(COLUMNS(1), 1);
 75  assertEquals(COLUMNS("str"), 1);
 76  assertEquals(COLUMNS(false), 1);
 77  assertEquals(COLUMNS(Cell.BuildFrom("A1", "str")), 1);
 78  assertEquals(COLUMNS([
 79    Cell.BuildFrom("A1", "str"),
 80    Cell.BuildFrom("A2", "str"),
 81    Cell.BuildFrom("A3", "str"),
 82    Cell.BuildFrom("A4", "str"),
 83    Cell.BuildFrom("A5", "str"),
 84    Cell.BuildFrom("A6", "str"),
 85    Cell.BuildFrom("A7", "str"),
 86    Cell.BuildFrom("A8", "str"),
 87    Cell.BuildFrom("B1", "str"),
 88    Cell.BuildFrom("B2", "str"),
 89    Cell.BuildFrom("B3", "str"),
 90    Cell.BuildFrom("B4", "str"),
 91    Cell.BuildFrom("B5", "str"),
 92    Cell.BuildFrom("B6", "str"),
 93    Cell.BuildFrom("B7", "str"),
 94    Cell.BuildFrom("B8", "str"),
 95    Cell.BuildFrom("C1", "str"),
 96    Cell.BuildFrom("C2", "str"),
 97    Cell.BuildFrom("C3", "str"),
 98    Cell.BuildFrom("C4", "str"),
 99    Cell.BuildFrom("C5", "str")
100  ]), 3);
101  assertEquals(COLUMNS([
102    Cell.BuildFrom("A1", "str"),
103    Cell.BuildFrom("A2", "str"),
104    Cell.BuildFrom("A3", "str"),
105    Cell.BuildFrom("A4", "str"),
106    Cell.BuildFrom("A5", "str"),
107    Cell.BuildFrom("A6", "str"),
108    Cell.BuildFrom("A7", "str"),
109    Cell.BuildFrom("A8", "str"),
110    Cell.BuildFrom("B1", "str")
111  ]), 2);
112  assertEquals(COLUMNS([
113    Cell.BuildFrom("A1", "str"),
114    Cell.BuildFrom("A2", "str"),
115    Cell.BuildFrom("A3", "str")
116  ]), 1);
117  assertEquals(COLUMNS([1, 2, 3, 4]), 4);
118  catchAndAssertEquals(function() {
119    COLUMNS.apply(this, []);
120  }, ERRORS.NA_ERROR);
121  catchAndAssertEquals(function() {
122    COLUMNS([]);
123  }, ERRORS.REF_ERROR);
124});
125
126
127test("ROWS", function(){
128  assertEquals(ROWS(1), 1);
129  assertEquals(ROWS("str"), 1);
130  assertEquals(ROWS(false), 1);
131  assertEquals(ROWS(Cell.BuildFrom("A1", "str")), 1);
132  assertEquals(ROWS([1]), 1);
133  assertEquals(ROWS([1, 2, 3, 4]), 1);
134  //A1:C5
135  assertEquals(ROWS([
136    Cell.BuildFrom("A1", "str"),
137    Cell.BuildFrom("A2", "str"),
138    Cell.BuildFrom("A3", "str"),
139    Cell.BuildFrom("A4", "str"),
140    Cell.BuildFrom("A5", "str"),
141    Cell.BuildFrom("B1", "str"),
142    Cell.BuildFrom("B2", "str"),
143    Cell.BuildFrom("B3", "str"),
144    Cell.BuildFrom("B4", "str"),
145    Cell.BuildFrom("B5", "str"),
146    Cell.BuildFrom("C1", "str"),
147    Cell.BuildFrom("C2", "str"),
148    Cell.BuildFrom("C3", "str"),
149    Cell.BuildFrom("C4", "str"),
150    Cell.BuildFrom("C5", "str"),
151  ]), 5);
152  //A5:C5
153  assertEquals(ROWS([
154    Cell.BuildFrom("A5", "str"),
155    Cell.BuildFrom("B5", "str"),
156    Cell.BuildFrom("C5", "str"),
157  ]), 1);
158  //A1:B2
159  assertEquals(ROWS([
160    Cell.BuildFrom("A1", "str"),
161    Cell.BuildFrom("A2", "str"),
162    Cell.BuildFrom("B1", "str"),
163    Cell.BuildFrom("B2", "str")
164  ]), 2);
165  //A1:A3
166  assertEquals(ROWS([
167    Cell.BuildFrom("A1", "str"),
168    Cell.BuildFrom("A2", "str"),
169    Cell.BuildFrom("A3", "str")
170  ]), 3);
171  catchAndAssertEquals(function() {
172    ROWS.apply(this, []);
173  }, ERRORS.NA_ERROR);
174  catchAndAssertEquals(function() {
175    ROWS([]);
176  }, ERRORS.REF_ERROR);
177});