javascriptexceloffice-js

Create Excel ranges from list of cell addresses with OfficeJs


I am working with an Excel add-in and am given a list of cell addresses that contain custom functions that I need to reference, ie.,

 ["A1","B1","C1","F2","F3","F4","G2","G3","G4","X5"] 

This list of cell addresses can vary from one cell address to a list of many and can vary from a row to a column to every other cell, it's all up to the user for where they place their custom function.

I need to create a list of ranges the same way Excel creates them to display to the user.

So for the example array I would expect my return to be

["A1:C1","F2:G4","X5:X5"].

This would look something like

const exampleCells1 = ["A1","B1","C1","F3","F4","G3","G4","X1"];
const exampleCells2 = ["AN299","AN300"];
const exampleCells3 = ["P44"];

const getRanges = (cells) => {
  const ranges = [];
  // createRanges
  return ranges;
 };
 
 
 //expected returns being
 // getRanges(exampleCells1) => ["A1:C1","F3:G4","X1:X1"];
 // getRanges(exampleCells2) => ["AN299:AN300"];
 // getRanges(exampleCells3) => ["P44:P44"];


Solution

  • Okay so I have no idea about Excel nor OfficeJS, but here's a possible solution using plain JavaScript.

    The only assumption I've made is that cells in a same range are ordered (otherwise, the problem would be unsolvable) - either by row or by column.

    const cells = ["A1","B1","C1","F2","F3","F4","G2","G3","G4","X5"];
    
    function rangesFromCells(cells) {
      /* 'range' is a temp array containing the
      range currently being explored. */
      let range = [];
      /* 'dir' contains the direction of the
      current range's exploration. */
      let dir = 0;
      /* 'sizeRange' contains the size of 'range' in the
      direction of exploration: if it's being explored by row,
      its number of columns; otherwise, its number of rows.
      'currentSize' - the same thing, but for the current
      row/column inside the current range. */
      let [sizeRange, currentSize] = [null, 0];
      const output = cells.reduce(function (acc, cell) {
        switch (range.length) {
            case 0:
            range.push(cell);
            return acc;
          case 1:
            dir = whichDirection(range.at(-1), cell);
            if (!dir) {
                acc.push(range[0].concat(':', range[0]));
                range = [cell];
              return acc;
            } else {
                range.push(cell);
              currentSize = 2;
              return acc;
            }
          default:
            switch (whichDirection(range.at(-1), cell)) {
                case dir:  // The direction stays the same.
                /* 'sizeRange' is null if 'range' contains only one row/column. */
                if (!sizeRange || currentSize < sizeRange) {
                  range.push(cell);
                  currentSize++;
                  return acc;
                } else {  /* If a cell is added that exceeds the size
                of the current range, start a new range with said cell. */
                  acc.push(range[0].concat(':', range.at(-1)));
                  range = [cell];
                  [sizeRange, currentSize] = [null, 0];
                  return acc;
                }
              case 0:
                sizeRange ??= range.length;
                /* If the latest cell is adjacent to the first cell in the current
                row/column, with an opposite direction of 'dir', it can mean the
                beginning of a new row/column inside the same range. */
                if (dir + whichDirection(range.at(-sizeRange), cell) === 0) {
                    currentSize = 1;
                  range.push(cell);
                  return acc;
                }   /* Otherwise, a new, different range begins, so fall-through. */
              /* If the direction changes from 'down' to 'right' or
              viceversa, necessarily a new different range is starting. */
              default:
                /* If 'range' is a rectangle, it represents one range. */
                if (currentSize === sizeRange) {
                  acc.push(range[0].concat(':', range.at(-1)));
                  range = [cell];
                  [sizeRange, currentSize] = [null, 0];
                  return acc;
                /* Otherwise, the last row/column constitutes a separate range. */
                } else {
                    acc.push(range[0].concat(':', range.at(-currentSize-1)));
                  acc.push(range.at(-currentSize).concat(':', range.at(-1)));
                  range = [cell];
                  [sizeRange, currentSize] = [null, 0];
                  return acc;
                }
            }
        }
      }, []);
      /* When 'reduce' has processed the last item in 'cells',
      the last range is still contained in 'range'. */
      if (!sizeRange || currentSize === sizeRange) {
        output.push(range[0].concat(':', range.at(-1)));
      } else {
        output.push(range[0].concat(':', range.at(-currentSize-1)));
        output.push(range.at(-currentSize).concat(':', range.at(-1)));
      }
      return output;
    }
    
    /**
     * Returns whether a cell is immediately to the right \
     * or immediately down of another cell.
     *
     * @param {string} cell1 - The A1 notation of the first cell.
     * @param {string} cell2 - The A1 notation of the second cell.
     * @return {number} 1 if the second cell is to the right \
     * of the first one; -1, if it's down; 0 otherwise.
     */
    function whichDirection(cell1, cell2) {
      const [row1, row2] = cell1.concat(cell2).match(/(?:[0-9]+)/g).map(Number);
        const [col1, col2] = cell1.concat(cell2).match(/(?:[A-Z]+)/g);
      switch (true) {
        case col1 === col2 && row2 === row1+1:
            return -1;
        case row1 === row2 && col2 === nextCol(col1):
            return 1;
        default:
            return 0;
      }
    }
    
    /**
     * Returns the letters of a given column's right neighbour.
     *
     * @param {string} col - The name of the column.
     * @return {string} The name of the next column.
     */
    function nextCol(col) {
        switch (col.at(-1)) {
        case undefined:
            return 'A';
        case 'Z':
            return nextCol(col.slice(0, -1)).concat('A');
        default:
            return col.slice(0, -1).concat(String.fromCharCode(col.charCodeAt(col.length-1) + 1));
      }
    }
    
    console.log(rangesFromCells(cells));