Excel-Like Headers in JavaScript
I wrote a little something to do this problem for you, I think it's clear, and more easy to use, with tests
so you just need to call "toExcelHeaderString(4)" for A,B,C,D
or for individual excel rows "toExcelHeader(4)" for D
/**
* @param {Number} rows
* @returns {String}
*/
toExcelHeaderString = function (rows) {
return toExcelHeaderArray(rows).join(",");
}
// toExcelHeaderString(60) == "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH"
/**
* @param {Number} rows
* @returns {Array}
*/
toExcelHeaderArray = function (rows) {
var excelHeaderArr = [];
for(var index = 1; index <= rows; index++) {
excelHeaderArr.push(toExcelHeader(index));
}
return excelHeaderArr;
}
toExcelHeaderArray(60) == ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH"]
/**
* @param {Number} index
* @returns {String}
*/
toExcelHeader = function (index) {
if(index <= 0) {
throw new Error("index must be 1 or greater");
}
index--;
var charCodeOfA = ("a").charCodeAt(0); // you could hard code to 97
var charCodeOfZ = ("z").charCodeAt(0); // you could hard code to 122
var excelStr = "";
var base24Str = (index).toString(charCodeOfZ - charCodeOfA + 1);
for(var base24StrIndex = 0; base24StrIndex < base24Str.length; base24StrIndex++) {
var base24Char = base24Str[base24StrIndex];
var alphabetIndex = (base24Char * 1 == base24Char) ? base24Char : (base24Char.charCodeAt(0) - charCodeOfA + 10);
// bizarre thing, A==1 in first digit, A==0 in other digits
if(base24StrIndex == 0) {
alphabetIndex -= 1;
}
excelStr += String.fromCharCode(charCodeOfA*1 + alphabetIndex*1);
}
return excelStr.toUpperCase();
}
// toExcelHeader(0) == Error
// toExcelHeader(1) == "A"
// toExcelHeader(26) == "Z"
// toExcelHeader(27) == "AA"
// toExcelHeader(3400) == "EAT"
// toExcelHeader(2048) == "CAT"
// toExcelHeader(3733849) == "HELLO"
// toExcelHeader(10768294) == "WORLD"