1import {
  2  Sheet
  3} from "../src/Sheet";
  4import {
  5  assertEquals,
  6  assertIsNull,
  7  assertArrayEquals,
  8  test
  9} from "./Utils/Asserts";
 10import {
 11  REF_ERROR,
 12  NAME_ERROR
 13} from "../src/Errors";
 14
 15test("Sheet.setCell, Sheet.getCell", function(){
 16  let s = new Sheet();
 17  s.setCell("A2", "22");
 18  let cell = s.getCell("A2");
 19  assertEquals(null, cell.getFormula());
 20  assertEquals("22", cell.getValue());
 21  assertEquals("A2", cell.getId());
 22  assertEquals(1, cell.getRow());
 23  assertEquals(0, cell.getColumn());
 24  assertIsNull(cell.getError());
 25  assertArrayEquals([], cell.getDependencies());
 26
 27  let blankCell = s.getCell("N1");
 28  assertEquals(true, blankCell.isBlank());
 29  assertIsNull(blankCell.getError());
 30  assertEquals(false, blankCell.hasFormula());
 31
 32  s = new Sheet();
 33  let SUM_FORM = "=SUM(A1:A4)";
 34  s.setCell("A1", "1");
 35  s.setCell("A2", "20");
 36  s.setCell("A3", "3.4");
 37  s.setCell("A4", "45");
 38  s.setCell("A5", "=SUM(A1:A4)");
 39  let A5 = s.getCell("A5");
 40  assertEquals(69.4, A5.getValue());
 41  assertEquals(SUM_FORM.substr(1), A5.getFormula());
 42  assertIsNull(cell.getError());
 43  assertArrayEquals(['A1', 'A2', 'A3', 'A4'], A5.getDependencies());
 44});
 45
 46test("Sheet.load", function(){
 47  let sheet = new Sheet();
 48  let SUM_FORMULA = "=SUM(A1:D1, H1)";
 49  let MAX_FORMULA = "=MAX(A2:J2)";
 50  let MIN_FORMULA = "=MIN(A3:J3)";
 51  let AVERAGE_FORMULA = "=AVERAGE(A4:J4)";
 52  let SUM_IF_FORMULA = "=SUMIF(A5:J5,5)";
 53  let SUM_REF_FORMULA = "=SUM(K1, K2, K3, K4)";
 54  sheet.load([[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, SUM_FORMULA],
 55    [-1, -10, 2, 4, 100, 1, 50, 20, 200, -100, MAX_FORMULA],
 56    [-1, -40, -53, 1, 10, 30, 10, 301, -1, -20, MIN_FORMULA],
 57    [20, 50, 100, 20, 1, 5, 15, 25, 45, 23, AVERAGE_FORMULA],
 58    [0, 10, 1, 10, 2, 10, 3, 10, 4, 10, SUM_IF_FORMULA],
 59    [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, SUM_REF_FORMULA]]);
 60  let K1 = sheet.getCell("K1");
 61  assertEquals(18, K1.getValue());
 62  assertEquals(SUM_FORMULA.substr(1), K1.getFormula());
 63  assertIsNull(K1.getError());
 64  assertArrayEquals(['A1', 'B1', 'C1', 'D1', 'H1'], K1.getDependencies());
 65  let K2 = sheet.getCell("K2");
 66  assertEquals(200, K2.getValue());
 67  assertEquals(MAX_FORMULA.substr(1), K2.getFormula());
 68  assertIsNull(K2.getError());
 69  assertArrayEquals(['A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2', 'H2', 'I2', 'J2'], K2.getDependencies());
 70  let K3 = sheet.getCell("K3");
 71  assertEquals(-53, K3.getValue());
 72  assertEquals(MIN_FORMULA.substr(1), K3.getFormula());
 73  assertIsNull(K3.getError());
 74  assertArrayEquals(['A3', 'B3', 'C3', 'D3', 'E3', 'F3', 'G3', 'H3', 'I3', 'J3'], K3.getDependencies());
 75  let K4 = sheet.getCell("K4");
 76  assertEquals(30.4, K4.getValue());
 77  assertEquals(AVERAGE_FORMULA.substr(1), K4.getFormula());
 78  assertIsNull(K4.getError());
 79  assertArrayEquals(['A4', 'B4', 'C4', 'D4', 'E4', 'F4', 'G4', 'H4', 'I4', 'J4'], K4.getDependencies());
 80  let K5 = sheet.getCell("K5");
 81  assertEquals(0, K5.getValue());
 82  assertEquals(SUM_IF_FORMULA.substr(1), K5.getFormula());
 83  assertIsNull(K5.getError());
 84  assertArrayEquals(['A5', 'B5', 'C5', 'D5', 'E5', 'F5', 'G5', 'H5', 'I5', 'J5'], K5.getDependencies());
 85  let K6 = sheet.getCell("K6");
 86  assertEquals(195.4, K6.getValue());
 87  assertEquals(SUM_REF_FORMULA.substr(1), K6.getFormula());
 88  assertIsNull(K6.getError());
 89  assertArrayEquals(['K1', 'K2', 'K3', 'K4'], K6.getDependencies());
 90});
 91
 92test("Sheet REF error", function(){
 93  let sheet  = new Sheet();
 94  sheet.setCell("A1", "200");
 95  sheet.setCell("A2", "200");
 96  sheet.setCell("A3", "=SUM(A1, A2)");
 97  sheet.setCell("B1", "=SUM(A3, B2)");
 98  sheet.setCell("B2", "=SUM(A1, B1)");
 99  let B1 = sheet.getCell("B1");
100  assertIsNull(B1.getValue());
101  assertEquals(REF_ERROR, B1.getError().name);
102  assertArrayEquals(['A3', 'B2'], B1.getDependencies());
103  let B2 = sheet.getCell("B2");
104  assertIsNull(B2.getValue());
105  assertEquals(REF_ERROR, B2.getError().name);
106  assertArrayEquals(['A1', 'B1'], B2.getDependencies());
107});
108
109test("Sheet cell NAME error", function(){
110  let sheet  = new Sheet();
111  sheet.setCell("A1", "1");
112  sheet.setCell("A2", "=SUM(A1, NN)");
113  let A2 = sheet.getCell("A2");
114  assertIsNull(A2.getValue());
115  assertEquals(NAME_ERROR, A2.getError().name);
116  assertArrayEquals(['A1'], A2.getDependencies());
117});
118
119test("Sheet unsupported formula NAME error", function(){
120  let sheet  = new Sheet();
121  sheet.setCell("A1", "1");
122  sheet.setCell("A2", "=BEN(A1)");
123  let A2 = sheet.getCell("A2");
124  assertIsNull(A2.getValue());
125  assertEquals(NAME_ERROR, A2.getError().name);
126  assertArrayEquals(['A1'], A2.getDependencies());
127});
128
129test("Sheet nested formulas", function(){
130  let sheet  = new Sheet();
131  sheet.setCell("A1", "1");
132  sheet.setCell("A2", "10");
133  sheet.setCell("A3", "44.4");
134  sheet.setCell("A4", "=SUM(A1:A3, MAX(A1, A3))");
135  let A4 = sheet.getCell("A4");
136  assertEquals(99.8, A4.getValue());
137  assertEquals("SUM(A1:A3, MAX(A1, A3))", A4.getFormula());
138  assertIsNull(A4.getError());
139  assertArrayEquals(['A1', 'A2', 'A3'], A4.getDependencies());
140});
141
142test("Sheet cell range query", function(){
143  let sheet  = new Sheet();
144  sheet.setCell("A1", "1");
145  sheet.setCell("A2", "1");
146  sheet.setCell("A3", "1");
147  sheet.setCell("A4", "1");
148  sheet.setCell("A5", "1");
149  sheet.setCell("N1", "=SUM(A1:A7)");
150  let N1 = sheet.getCell("N1");
151  assertArrayEquals(['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7'], N1.getDependencies());
152});
153
154test("Sheet dependency calculation propagation", function(){
155  let sheet  = new Sheet();
156  sheet.setCell("A1", "1");
157  sheet.setCell("A2", "=SUM(A1, 100)");
158  let A2 = sheet.getCell("A2");
159  assertEquals(101, A2.getValue());
160  assertArrayEquals(['A1'], A2.getDependencies());
161  sheet.setCell("A1", "2");
162  assertEquals(102, A2.getValue());
163  assertArrayEquals(['A1'], A2.getDependencies());
164});
165
166test("Sheet cell formula update", function(){
167  let sheet  = new Sheet();
168  sheet.setCell("A1", "1");
169  sheet.setCell("A2", "=SUM(A1, 100)");
170  let A2 = sheet.getCell("A2");
171  assertEquals(101, A2.getValue());
172  assertArrayEquals(['A1'], A2.getDependencies());
173  sheet.setCell("A2", "=MAX(A1, 100)");
174  assertEquals(100, A2.getValue());
175  assertArrayEquals(['A1'], A2.getDependencies());
176});