name:
src/Sheet.ts
-rw-r--r--
7537
1import {Cell} from "./Cell";
2import {NameError, RefError} from "./Errors";
3import {DataStore} from "./Parser/DataStore";
4import {FormulaParser} from "./Parser/Parser";
5import {Formulas} from "./Formulas";
6import {
7 numberToCharacter,
8 convertXYtoA1Coordinates,
9 A1toRowColCoordinates
10} from "./Utilities/MoreUtils";
11
12/**
13 * Represents a spreadsheet parser and data-store that act together as a functional spreadsheet.
14 */
15class Sheet {
16 private parser;
17 private dataStore : DataStore;
18
19 constructor() {
20 this.parser = FormulaParser(this);
21 this.dataStore = new DataStore();
22 }
23
24 /**
25 * Iterate through cells in the data-store, returning the collected cells in the range.
26 * @param origin
27 * @param startCell
28 * @param endCell
29 * @returns {{index: Array; value: Array}}
30 */
31 iterateCells (origin, startCell, endCell) {
32 let result = {
33 index: [], // list of cell index: A1, A2, A3
34 value: [] // list of cell value
35 };
36 let cols = {
37 start: 0,
38 end: 0
39 };
40 if (endCell.col >= startCell.col) {
41 cols = {
42 start: startCell.col,
43 end: endCell.col
44 };
45 } else {
46 cols = {
47 start: endCell.col,
48 end: startCell.col
49 };
50 }
51 let rows = {
52 start: 0,
53 end: 0
54 };
55 if (endCell.row >= startCell.row) {
56 rows = {
57 start: startCell.row,
58 end: endCell.row
59 };
60 } else {
61 rows = {
62 start: endCell.row,
63 end: startCell.row
64 };
65 }
66 for (let column = cols.start; column <= cols.end; column++) {
67 for (let row = rows.start; row <= rows.end; row++) {
68 let cellIndex = numberToCharacter(column) + (row + 1),
69 cellValue = this.cellValue(origin, cellIndex);
70
71 result.index.push(cellIndex);
72 result.value.push(cellValue);
73 }
74 }
75 return result;
76 }
77
78 /**
79 * Call function with given arguments. Used for calling formulas.
80 * @param fn
81 * @param args
82 * @returns {any}
83 */
84 callFunction(fn, args) {
85 fn = fn.toUpperCase();
86 args = args || [];
87 if (Formulas.exists(fn)) {
88 return Formulas.get(fn).apply(this, args);
89 }
90
91 throw new NameError("Unknown function: '" + fn + "'.");
92 }
93
94 /**
95 * Call variable, which could include calling a function.
96 * @param args
97 * @returns {any}
98 */
99 callVariable(args) {
100 args = args || [];
101 let str = args.shift(); // the first in args is the name of the function to call.
102
103 if (str) {
104 str = str.toUpperCase();
105 if (Formulas.exists(str)) {
106 return Formulas.get(str).apply(this, args);
107 }
108 }
109
110 throw new NameError("Unknown variable: '" + str + "'.");
111 };
112
113 /**
114 * Fetch cell, updating dependencies in process.
115 * @param origin
116 * @param cellId
117 * @returns {Cell}
118 */
119 cellValue(origin, cellId) {
120 let cell = this.dataStore.getCell(cellId);
121
122 //update dependencies
123 this.dataStore.getCell(origin).updateDependencies([cellId]);
124 // check references error
125 if (cell && cell.getDependencies()) {
126 if (cell.getDependencies().indexOf(cellId) !== -1) {
127 throw new RefError("Reference does not exist.");
128 }
129 }
130 return cell;
131 }
132
133 /**
134 * Get a range of cells.
135 * @param origin - the cell id in A1 notation from which this range is being referenced.
136 * @param {string} start - first cell coordinate (in A1 notation) in iteration
137 * @param {string} end - final cell coordinate (in A1 notation) in iteration
138 * @returns {Array}
139 */
140 cellRangeValue(origin, start: string, end: string) {
141 let coordsStart = A1toRowColCoordinates(start),
142 coordsEnd = A1toRowColCoordinates(end);
143
144 // iterate cells to get values and indexes
145 let cells = this.iterateCells(origin, coordsStart, coordsEnd),
146 result = [];
147 //update dependencies
148 this.dataStore.getCell(origin).updateDependencies(cells.index);
149
150 result.push(cells.value);
151 return result;
152 }
153
154 /**
155 * Get a fixed cell value.
156 * @param origin
157 * @param id
158 * @returns {Cell}
159 */
160 fixedCellValue (origin, id) {
161 id = id.replace(/\$/g, '');
162 return this.cellValue(origin, id);
163 };
164
165 /**
166 * Get a fixed cell value range.
167 * @param origin
168 * @param start
169 * @param end
170 * @returns {Array}
171 */
172 fixedCellRangeValue(origin, start, end) {
173 start = start.replace(/\$/g, '');
174 end = end.replace(/\$/g, '');
175
176 return this.cellRangeValue(origin, start, end);
177 };
178
179 /**
180 * Recalculate dependencies for a cell.
181 * @param {Cell} cell
182 */
183 private recalculateCellDependencies(cell: Cell) {
184 let allDependencies = this.dataStore.getDependencies(cell.getId());
185
186 for (let refId of allDependencies) {
187 let currentCell = this.dataStore.getCell(refId);
188 if (currentCell && currentCell.hasFormula()) {
189 this.calculateCellFormula(currentCell);
190 }
191 }
192 }
193
194 /**
195 * Executes the formula in a cell.
196 * @param {Cell} cell
197 * @returns {{error: Error; result: any} | {error: any; result: any}}
198 */
199 private calculateCellFormula(cell: Cell) {
200 // to avoid double translate formulas, update cell data in parser
201 let parsed = this.parse(cell.getFormula(), cell.getId());
202
203 this.dataStore.getCell(cell.getId()).setValue(parsed.result);
204 this.dataStore.getCell(cell.getId()).setError(parsed.error);
205
206 return parsed;
207 }
208
209 /**
210 * Add a cell to the data-store, recording and updating dependencies if necessary.
211 * @param {Cell} cell
212 */
213 private registerCellInDataStore(cell: Cell) {
214 this.dataStore.addCell(cell);
215 if (cell.hasFormula()) {
216 this.calculateCellFormula(cell);
217 }
218 }
219
220 /**
221 * Parse a formula for a given cellId. This involves all calculations and look-ups.
222 * @param formula
223 * @param cellId
224 * @returns {any}
225 */
226 public parse(formula, cellId) {
227 let result = null;
228 let error = null;
229
230 try {
231 this.parser.yy.originCellId = cellId;
232 result = this.parser.parse(formula);
233 let deps = this.dataStore.getDependencies(cellId);
234
235 if (deps.indexOf(cellId) !== -1) {
236 result = null;
237 for(let id of deps) {
238 this.dataStore.getCell(id).setError(new RefError("Reference does not exist"));
239 this.dataStore.getCell(id).clearValue();
240 }
241 error = new RefError("Reference does not exist.");
242 }
243 } catch (e) {
244 error = e;
245 }
246
247 if (result instanceof Error) {
248 return {
249 error: result,
250 result: null
251 }
252 }
253 return {
254 error: error,
255 result: result
256 }
257 }
258
259 /**
260 * Set a cell's value, by id.
261 * @param {string} id
262 * @param {string} value
263 */
264 public setCell(id: string, value: string) {
265 let cell = new Cell(id);
266 cell.setValue(value.toString());
267 this.registerCellInDataStore(cell);
268 this.recalculateCellDependencies(cell);
269 }
270
271 /**
272 * Get a cell from the data-store, returning null if a cell is undefined.
273 * @param {string} id
274 * @returns {Cell}
275 */
276 public getCell(id: string) : Cell {
277 let cell = this.dataStore.getCell(id);
278 if (cell === undefined) {
279 return null;
280 }
281 return cell;
282 }
283
284 /**
285 * Load an a matrix of cells into the data-store.
286 * @param {Array<Array<any>>} input
287 */
288 public load(input: Array<Array<any>>) {
289 for (let y = 0; y < input.length; y++) {
290 for (let x = 0; x < input[0].length; x++) {
291 // set the cell here
292 let id = convertXYtoA1Coordinates(x, y);
293 this.setCell(id, input[y][x].toString());
294 }
295 }
296 };
297
298}
299
300export {
301 Sheet
302}