1import { ValueException } from "../../errors/ValueException";
  2import { AlphaUtils } from "../../utils/AlphaUtils";
  3import { Range } from "../common/Range";
  4import { isNull } from "../../common/utils/Types";
  5
  6/**
  7 * A cell query represents a query of one or more cells over a single sheet. It is composed of
  8 * two-dimensional ranges: rows and columns, both of which can be unbounded at the lower, or
  9 * upper ends of the range.
 10 */
 11export class CellQuery extends Object {
 12  /**
 13   * Range of columns.
 14   */
 15  readonly columns: Range;
 16  /**
 17   * Range of rows.
 18   */
 19  readonly rows: Range;
 20  /**
 21   * Name of the sheet being queried.
 22   */
 23  private sheet: string = null;
 24
 25  constructor(sheet: string, columns: Range, rows: Range) {
 26    super();
 27    this.sheet = sheet;
 28    this.columns = columns;
 29    this.rows = rows;
 30  }
 31
 32  static builder(query?: CellQuery): CellQueryBuilder {
 33    return new CellQueryBuilder(query);
 34  }
 35
 36  getFormattedSheetName(): string {
 37    if (this.sheet && this.sheet.startsWith("'")) {
 38      return this.sheet.substr(1, this.sheet.length - 2);
 39    }
 40    return this.sheet;
 41  }
 42
 43  /**
 44   * Does this query intersect with the other one?
 45   *
 46   * @param other - other query.
 47   * @return true if at least one cell overlaps.
 48   */
 49  intersects(other: CellQuery): boolean {
 50    return other.columns.isConnected(this.columns) && other.rows.isConnected(this.rows);
 51  }
 52
 53  /**
 54   * Bound this query using an upper column and upper row. Will default to the lower of the upper column passed in,
 55   * and the upper column bound existing on the columns range. Same for rows.
 56   *
 57   * @param upperColumn - upper endpoint for columns to default to.
 58   * @param upperRow    - upper endpoint of rows to default to.
 59   * @return bounded query.
 60   */
 61  toBounded(upperColumn: number, upperRow: number): CellQuery {
 62    if (
 63      !Range.closed(0, upperColumn).isConnected(this.columns) &&
 64      !Range.closed(0, upperRow).isConnected(this.rows)
 65    ) {
 66      return this;
 67    }
 68    return CellQuery.builder(this)
 69      .columnsBetween(
 70        this.columns.lowerEndpoint(),
 71        Math.min(
 72          upperColumn,
 73          this.columns.hasUpperBound() ? this.columns.upperEndpoint() : upperColumn
 74        )
 75      )
 76      .rowsBetween(
 77        this.rows.lowerEndpoint(),
 78        Math.min(upperRow, this.rows.hasUpperBound() ? this.rows.upperEndpoint() : upperRow)
 79      )
 80      .build();
 81  }
 82
 83  toString() {
 84    const sheet = isNull(this.sheet) ? "" : `${this.sheet}!`;
 85    const upperColumn = this.columns.hasUpperBound()
 86      ? AlphaUtils.oneIndexedNumberToLetter(this.columns.upper + 1)
 87      : "";
 88    const lowerColumn = this.columns.hasLowerBound()
 89      ? AlphaUtils.oneIndexedNumberToLetter(this.columns.lower + 1)
 90      : "";
 91    const upperRow = this.rows.hasUpperBound() ? (this.rows.upper + 1).toString() : "";
 92    const lowerRow = this.rows.hasLowerBound() ? (this.rows.lower + 1).toString() : "";
 93    return `${sheet}${lowerColumn}${lowerRow}:${upperColumn}${upperRow}`;
 94  }
 95}
 96
 97/**
 98 * Easier way to build a query.
 99 */
100class CellQueryBuilder {
101  private sheet: string = null;
102  private columns: Range;
103  private rows: Range;
104
105  constructor(cellQuery?: CellQuery) {
106    if (cellQuery) {
107      this.sheet = cellQuery.getFormattedSheetName();
108      this.columns = cellQuery.columns;
109      this.rows = cellQuery.rows;
110    }
111  }
112
113  setSheet(sheet: string): CellQueryBuilder {
114    this.sheet = sheet;
115    return this;
116  }
117
118  columnsBetween(low: number, high: number): CellQueryBuilder {
119    this.columns = Range.closed(low, high);
120    return this;
121  }
122
123  columnsBetweenAlpha(low: string, high: string): CellQueryBuilder {
124    this.columns = Range.closed(AlphaUtils.columnToInt(low), AlphaUtils.columnToInt(high));
125    return this;
126  }
127
128  rowsBetween(low: number, high: number): CellQueryBuilder {
129    this.rows = Range.closed(low, high);
130    return this;
131  }
132
133  rowsBetweenAlpha(low: string, high: string): CellQueryBuilder {
134    this.rows = Range.closed(parseInt(low) - 1, parseInt(high) - 1);
135    return this;
136  }
137
138  columnsStartingAt(low: number): CellQueryBuilder {
139    this.columns = Range.atLeast(low);
140    return this;
141  }
142
143  rowsStartingAt(low: number): CellQueryBuilder {
144    this.rows = Range.atLeast(low);
145    return this;
146  }
147
148  openColumnsStartingAtZero(): CellQueryBuilder {
149    this.columns = Range.atLeast(0);
150    return this;
151  }
152
153  openColumnsStartingAt(columnString: string): CellQueryBuilder {
154    this.columns = Range.atLeast(AlphaUtils.columnToInt(columnString));
155    return this;
156  }
157
158  openColumnsStartingAtNumber(column: number): CellQueryBuilder {
159    this.columns = Range.atLeast(column);
160    return this;
161  }
162
163  openRowsStartingAtZero(): CellQueryBuilder {
164    this.rows = Range.atLeast(0);
165    return this;
166  }
167
168  openRowsStartingAtNumber(row: number): CellQueryBuilder {
169    this.rows = Range.atLeast(row);
170    return this;
171  }
172
173  openRowsStartingAt(rowString: string): CellQueryBuilder {
174    this.rows = Range.atLeast(AlphaUtils.rowToInt(rowString));
175    return this;
176  }
177
178  expand(query: CellQuery): CellQueryBuilder {
179    if (this.columns == null) {
180      this.columns = query.columns;
181    } else {
182      this.columns = this.columns.span(query.columns);
183    }
184    if (this.rows == null) {
185      this.rows = query.rows;
186    } else {
187      this.rows = this.rows.span(query.rows);
188    }
189    if (this.sheet !== null && this.sheet !== query.getFormattedSheetName()) {
190      throw new ValueException("Different sheet names.");
191    } else {
192      this.sheet = query.getFormattedSheetName();
193    }
194
195    return this;
196  }
197
198  build(): CellQuery {
199    return new CellQuery(this.sheet, this.columns, this.rows);
200  }
201}