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}