1import {
  2  ArgsChecker
  3} from "../Utilities/ArgsChecker";
  4import {
  5  NumError, RefError, ValueError
  6} from "../Errors";
  7import {
  8  TypeConverter
  9} from "../Utilities/TypeConverter";
 10import {
 11  Filter
 12} from "../Utilities/Filter";
 13import {Cell} from "../Cell";
 14
 15
 16/**
 17 * Returns an element from a list of choices based on index.
 18 * @param index - Which choice to return. Index starts at 1.
 19 * @param values -  Array of potential value to return. Required. May be a reference to a cell or an individual value.
 20 * @constructor
 21 */
 22let CHOOSE = function (index, ...values) {
 23  ArgsChecker.checkAtLeastLength(arguments, 2, "CHOOSE");
 24  let i = Math.floor(TypeConverter.firstValueAsNumber(index));
 25  let data = Filter.flattenAndThrow(values);
 26  if (i < 1 || i > data.length) {
 27    throw new NumError("Function CHOOSE parameter 1 value is " + i + ". Valid values are between 1 and "
 28        + (data.length) + " inclusive.");
 29  }
 30  return data[i-1];
 31};
 32
 33/**
 34 * Returns a text representation of a cell address based on the row, column, and sheet.
 35 * @param row - Row of cell address.
 36 * @param column - Column of cell address
 37 * @param {number} absoluteVsRelativeMode - [OPTIONAL - default is 1] Should return a relative(A1, vs $A$1) or absolute address. 1 is row and
 38 * column absolute (e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute
 39 * (e.g. $A1), 4 is row and column relative (e.g. A1).
 40 * @param {boolean} useA1Notation - [OPTIONAL - default is TRUE] Should use A1 notation.
 41 * @param sheet - [OPTIONAL] Sheet name to use in address.
 42 * @returns {string}
 43 * @constructor
 44 */
 45let ADDRESS = function (row, column, absoluteVsRelativeMode = 1, useA1Notation = true, sheet?) {
 46  ArgsChecker.checkLengthWithin(arguments, 2, 5, "ADDRESS");
 47  row = TypeConverter.firstValueAsNumber(row);
 48  column = TypeConverter.firstValueAsNumber(column);
 49  absoluteVsRelativeMode = TypeConverter.firstValueAsNumber(absoluteVsRelativeMode);
 50  useA1Notation = TypeConverter.firstValueAsBoolean(useA1Notation);
 51  sheet = (sheet === undefined) ? sheet : TypeConverter.firstValueAsString(sheet);
 52
 53  function calculateColumnLetters(n) {
 54    n--; // ensuring 1-indexed.
 55    let ordA = 'a'.charCodeAt(0);
 56    let ordZ = 'z'.charCodeAt(0);
 57    let len = ordZ - ordA + 1;
 58
 59    let s = "";
 60    while(n >= 0) {
 61      s = String.fromCharCode(n % len + ordA) + s;
 62      n = Math.floor(n / len) - 1;
 63    }
 64    return s.toUpperCase();
 65  }
 66
 67  if (row < 1) {
 68    throw new ValueError("Function ADDRESS parameter 1 value is " + row
 69        + ", but it should be greater than or equal to 1.");
 70  }
 71  if (column < 1) {
 72    throw new ValueError("Function ADDRESS parameter 2 value is " + column
 73      + ", but it should be greater than or equal to 1.");
 74  }
 75  if (absoluteVsRelativeMode > 4 || absoluteVsRelativeMode < 1) {
 76    throw new NumError("Function ADDRESS parameter 3 value is " + absoluteVsRelativeMode
 77        + ", while valid values are between 1 and 4 inclusively");
 78  }
 79
 80  let cellNotation = "";
 81  if (useA1Notation) {
 82    let columnLetter = calculateColumnLetters(column);
 83    switch (absoluteVsRelativeMode) {
 84      // 1 is row and column absolute (e.g. $A$1)
 85      case 1:
 86        cellNotation = cellNotation + "$" + columnLetter + "$" + row.toString();
 87        break;
 88      // 2 is row absolute and column relative (e.g. A$1)
 89      case 2:
 90        cellNotation = cellNotation + columnLetter + "$" + row.toString();
 91        break;
 92      // 3 is row relative and column absolute (e.g. $A1)
 93      case 3:
 94        cellNotation = cellNotation + "$" + columnLetter + row.toString();
 95        break;
 96      // 4 is row and column relative (e.g. A1).
 97      case 4:
 98        cellNotation = cellNotation + columnLetter + row.toString();
 99        break;
100    }
101  } else {
102    switch (absoluteVsRelativeMode) {
103      // 1 is row and column absolute (e.g. R1C1)
104      case 1:
105        cellNotation = "R" + row.toString() + "C" + column.toString();
106        break;
107      // 2 is row absolute and column relative (e.g. R1C[1])
108      case 2:
109        cellNotation = "R" + row.toString() + "C[" + column.toString() + "]";
110        break;
111      // 3 is row relative and column absolute (e.g. R[1]C1)
112      case 3:
113        cellNotation = "R[" + row.toString() + "]C" + column.toString();
114        break;
115      // 4 is row and column relative (e.g. R[1]C[1]).
116      case 4:
117        cellNotation = "R[" + row.toString() + "]C[" + column.toString() + "]";
118        break;
119    }
120  }
121  if (sheet !== undefined) {
122    // If the sheet name contains non-alpha numeric characters, wrap it in single-quotes.
123    // Safe sheet name examples: 'sheet_one', 'sheetone'.
124    // Unsafe sheet name examples: '_one', '12345sheet', 'sheet 1'.
125    if (sheet.match(/^[a-zA-Z]+[a-zA-Z0-9_]*$/) === null) {
126      return "'" + sheet + "'!" + cellNotation;
127    }
128    return sheet + "!" + cellNotation;
129
130  } else {
131    return cellNotation;
132  }
133};
134
135
136/**
137 * Gets the number of columns in a specified array or range.
138 * @param value - The array of range to consider.
139 * @returns {number}
140 * @constructor
141 */
142let COLUMNS = function (value) {
143  ArgsChecker.checkLength(arguments, 1, "COLUMNS");
144  if (value instanceof Array) {
145    if (value.length === 0) {
146      throw new RefError("Reference does not exist.");
147    } else if (value.length === 1) {
148      return 1;
149    }
150    if (value[0] instanceof Cell) {
151      let start = value[0];
152      let end = value[value.length - 1];
153      return end.getColumn() - start.getColumn() + 1; // counting columns inclusively
154    } else {
155      // if the array passed in is just values, assume that each value is a column.
156      return value.length;
157    }
158  } else {
159    return 1;
160  }
161};
162
163
164let ROWS = function (value) {
165  ArgsChecker.checkLength(arguments, 1, "ROWS");
166  if (value instanceof Array) {
167    if (value.length === 0) {
168      throw new RefError("Reference does not exist.");
169    }
170    if (value[0] instanceof Cell) {
171      let start = value[0];
172      let end = value[value.length - 1];
173      return end.getRow() - start.getRow() + 1; // counting columns inclusively
174    } else {
175      // if the array passed in is just values, array is considered to be a single row
176      return 1;
177    }
178  } else {
179    return 1;
180  }
181};
182
183
184export {
185  CHOOSE,
186  ADDRESS,
187  COLUMNS,
188  ROWS
189}