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"];
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));