Expand Excel columns range into the list of column names
HTML
<h3 id="result">
Actual result is
</h3>
<h3 id="expectedResult">
Expected result is ['A', ..., 'Z', 'AA', ..., 'AZ', 'BA', ..., 'BZ', 'CA'...]
</h3>
JavaScript
/*
See http://stackoverflow.com/questions/34813980/getting-an-array-of-column-names-at-sheetjs
The task is to expand Excel column names range for the range like ["A1:DD38"].
The range defines a block of columns from A to DD and rows from 1 to 38.
We only need to get an expaned list of column:
A, B, ... Z, AA, AB, ..., AZ, BA, ... BZ, ...
Column names are actually represent numbers in 26-radix system where
0 = "A", 1 = "B" and so on.
And javascript has Number().toString(radix) method to convert the number to
the number system with any given base, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/toString.
The reverse conversion can be done with parseInt(radix). See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/parseInt.
For the system with base 26, javascript uses numbers from 0 to 9 and
then lowercase letters from "a" to "p" for the rest of symbols.
We can switch from javascript system to excel system ("A" to "Z") with
simple chars replacement, since systems have same base.
So our task reduces to this:
- Convert start / end columns to decimal values
- Iterate from start to end
- Convert each number to Excel system and add to resulting array
*/
var convert = function(srcNum, scrDict, targetDict) {
//Like 9f -> JP
var targetNum = "";
for (var idx in srcNum) {
var srcDictIdx = scrDict.search(srcNum[idx]);
targetNum += targetDict[srcDictIdx]
}
return targetNum;
}
var buildColumnsArray = function (rangeString, delimiter) {
var range = rangeString.split(delimiter),
xlsDict = "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
jsDict = "0123456789abcdefghijklmnop",
dictLength = xlsDict.length,
radix = xlsDict.length,
start = range[0],
end = range[1],
rnt = [];
// convert 'A13' to 'A'
start = start.replace(/[^A-Z]/g, "");
end = end.replace(/[^A-Z]/g, "");
...