Convert row and column range to rows and columns array-CodePudding

I want to convert the ranges of below fashion to rows and column notations

The example input and the expected output is given below

Input is A1 => Then output is [0,1,0,1]
B1 = [0,1,1,2]
A2 = [1,2,0,1]
C2 = [1,2,2,3]
C12 = [11,12,2,3]
A1:B4 = [0,4,0,2]
C1:D11 = [0,11,2,4]
F32:H43 = [31,43,5,8]
C:F = [null,null,2,6]
30:38 = [29,38,null,null]
76:79 = [75,79,null,null]

AA1:AD15 = [0,15,26,30]


Z2:B1 = [null,null,null,null] or undefined //any invalid range
2:B1 = [null,null,null,null] or undefined //any invalid range
0:0 = [null,null,null,null] or undefined //any invalid range
4-2 = [null,null,null,null] or undefined //any invalid range
6-7d7 = [null,null,null,null] or undefined //any invalid range


The general formula
[startrow-1,endrow,startcol-1,endcol]


30:38 = [startrow-1,endrow,null,null]
76:79 = [startrow-1,endrow,null,null]
C:F = [null,null,2,6]


A1
can also be written as 
A1:A1 = [0,1,0,1]


C12
can also be written as 
C12:C12 = [11,12,2,3]

My code works for these

A10:B10 //already works

A:B10 //not working so far 

A10:B //not working so far

A:B //not working so far

10:10 //not working so far

<!DOCTYPE html>
<html>

<body>
<script>
function fromA1Notation(cell) {
    var i, l, chr,
        sum = 0,
        A = "A".charCodeAt(0),
        radix = "Z".charCodeAt(0) - A   1;

    if (typeof cell !== 'string' || !/^[A-Z] $/.test(cell)) {
        throw new Error("Expected column label");
    }

    for (i = 0, l = cell.length; i < l; i  ) {
        chr = cell.charCodeAt(i);
        sum = sum * radix   chr - A   1
    }

    return sum;
}

var input = "A1:B20";

if (input.length > 0 && input.match(/[A-Z] [0-9] :[A-Z] [0-9] /i) != null) {
    var matched = input.match("([A-Za-z] )([0-9] ):([A-Za-z] )([0-9] )");
console.log(JSON.stringify(matched))
    if (matched != null) {
        a1range = {
            a1not: input,
            c1: (fromA1Notation(matched[1].toUpperCase()) - 1),
            r1: (matched[2] - 1),
            c2: fromA1Notation(matched[3].toUpperCase()),
            r2: matched[4]
        };

        if (a1range.c1 >= a1range.c2 || a1range.c1 >= a1range.c2) {
            a1range = undefined;
        }
        console.log(a1range)
    }
}
        console.log(a1range)


</script>
</body>

</html>

CodePudding user response：

Here is a solution that:

turns 'A1' pattern into 'A1:A1'
matches col, num, ':', col, num
converts col letters into 1-based index
does invalid range check
fixed the start row and start col index to be zero based

function A2N (str) {
  return str.split('').reduce((acc, char, idx) => {
    return acc  = char.charCodeAt(0) - 65   (idx * 26);
  }, 1);
}

[ 'A1', 'B1', 'A2', 'C:F', 'A10:B10', 'A:B10', 'A10:B', 'A:B', '10:10', 'AA1', 'AAA1', 'A', '1', 'B', '20', 'Z9:A1', '@'
].forEach(str => {
  let parts = str
    .replace(/^(\w )$/, '$1:$1') // turn 'A1' into 'A1:A1'
    .match(/^([A-Z]*)([0-9]*)(?::([A-Z]*)([0-9]*))?$/);
  let result = [ null, null, null, null ];
  if(parts) {
    result = [
      parts[2] ? Number(parts[2]) : null,
      parts[4] ? Number(parts[4]) : null,
      parts[1] ? A2N(parts[1]) : null,
      parts[3] ? A2N(parts[3]) : null
    ];
    if(result[0] && result[1] && result[0] > result[1]) {
      // invalid range
      result[0] = null;
      result[1] = null;
    }
    if(result[2] && result[3] && result[2] > result[3]) {
      // invalid range
      result[2] = null;
      result[3] = null;
    }
    if(result[0]) {
      // zero-based start row
      result[0]--;
    }
    if(result[2]) {
      // zero-based start col
      result[2]--;
    }
  }
  console.log(str, '==>', result);
});