Excel-Like Headers in JavaScript

Question

I'm trying to write a function that emits a sequence of strings to match the headers on Excel. If you aren't familiar with Excel, that sequence looks like:

A,B,...,Z,AA,...,AZ,BA,...,ZZ,AAA,...,etc.

This is the code I've come up with:

function next(id) {
    if(id === "")
        return "A";
    var prefix = id.substring(0, id.length-1);
    var last = id[id.length-1]
    if(last === "Z")
        return (next(prefix) + "A");
    return prefix + String.fromCharCode(id.charCodeAt(id.length-1) + 1);
}

Do you know of any better/cleaner way of doing this?

Answer 1

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"

Answer 2

@aqm's answer will return incorrect result when index > 702

Counting A...Z and AA...ZZ, the result is 26 + 26 * 26 = 702, which means toExcelHeader(703) should return 'AAA', but the function returns 'ABA'

below provides a revised version

(I don't have enough reputation to reply in the original thread)

function toExcelHeader(num) {

  if(num <= 0) {
    return '';
  }

  var str = num.toString(26);
  var arr = str.split('').map(char => {
    var code = char.charCodeAt(0);
    if(code >= 48 && code <= 57) {
      code += 16; // convert 1-9 to A-I and 0 to @
    } else {
      code -= 23; // convert a-p to J-Z
    }
    return code;
  });

  // convert 'A@' to 'Z', 'B@' to 'AZ', etc. 
  // ascii code of '@' is 64
  var index = arr.indexOf(64)
  while(index >= 0) {
    if(index == 0) {
      arr.shift();  // remove head '@'
    } else {
      arr[index] += 26;
      arr[index - 1]--;
    }
    index = arr.indexOf(64);
  }

  var chars = arr.map(code => String.fromCharCode(code));
  return chars.join('');
}

// toExcelHeader(0) == ""
// toExcelHeader(1) == "A"
// toExcelHeader(26) == "Z"
// toExcelHeader(27) == "AA"
// toExcelHeader(702) == "ZZ"
// toExcelHeader(703) == "AAA"
// toExcelHeader(18278) == "ZZZ"
// toExcelHeader(18279) == "AAAA"

Answer 3

found this easy function from this pkg https://www.npmjs.com/package/number-to-excel-header

 const chars =
      ['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'];

    const numberToExcelHeader = (index) => {
      index -= 1;

      const quotient = Math.floor(index / 26);
      if (quotient > 0) {
        return numberToExcelHeader(quotient) + chars[index % 26];
      }

      return chars[index % 26];
    };

numberToExcelHeader(0) === undefined            
numberToExcelHeader(1) === "A"           
numberToExcelHeader(26) === "Z"          
numberToExcelHeader(27) === "AA"         
numberToExcelHeader(702) === "ZZ"        
numberToExcelHeader(703) === "AAA" 
numberToExcelHeader(2074) === "CAT"
numberToExcelHeader(3101) === "DOG"      
numberToExcelHeader(18278) === "ZZZ"     
numberToExcelHeader(18279) === "AAAA"