Excel-like headers in JavaScript

Question

I am trying to write a function that emits a sequence of rows to match headers in Excel. If you are not familiar with Excel, this sequence looks like this:

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

This is the code I came 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 improved / clean way of doing this?

Answer 1

I wrote a little something to make this problem for you, I think it is understandable and easier to use, with tests

so you just need to call "toExcelHeaderString (4)" for A, B, C, D

or for individual excel lines "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

 function columnNames(n) { let result = []; const indexA = "A".charCodeAt(0); const indexZ = "Z".charCodeAt(0); let alphabetLength = indexZ - indexA + 1; const repeatNum = Math.floor(n / alphabetLength); let startIndex = 0; let startString = ""; let string = ""; while (startIndex <= repeatNum) { if (startIndex > 0) { startString = String.fromCharCode(indexA + startIndex - 1); } if (startIndex === repeatNum) { alphabetLength = n % alphabetLength; } for (var i = 0; i < alphabetLength; i++) { string = String.fromCharCode(indexA + i); result.push(startString + string); } startIndex++; } console.log(result, result.length); return result; } columnNames(55); 

Answer 3

I see that this is an old branch, but since when I was looking for some advice, I could not find it, and I found my own solution, I will post it here in case it can be useful to everyone. This creates headers, such as: A, B .., Z, AA-AZ, AB-AZ, ... ZZ-ZZ → maximum number of headers: 702 → I think if you need more, you can change the function to match to your needs.

The function takes a number: the number of output as headers to create, and requires an array ALPHABETICAL_ORDERED_LETTERS: ('abcdefghijklmnopqrstuvwxyz'.toUpperCase (). Split (' ')).

Ex. buildExcelLikeHeaders(59)

 const MAX_LENGTH = 702; // can handle till 702 columns; function buildArray(remainingRows, prevArrOfHeaders = [], size, letter = 'A', index = 0) { const newSetOfHeaders = []; let currentIndex = index; if (currentIndex > MAX_LENGTH) currentIndex = MAX_LENGTH; for (let i = 0; i < remainingRows; i++) { if (ALPHABETICAL_ORDERED_LETTERS[i]) { const header = '${letter + ALPHABETICAL_ORDERED_LETTERS[i]}-${letter}Z'; newSetOfHeaders.push(header); } } const nextArrayOfHeaders = [...prevArrOfHeaders, ...newSetOfHeaders]; const remainingRowsNext = Math.abs(remainingRows - ALPHABETICAL_ORDERED_LETTERS.length); if (nextArrayOfHeaders.length < size && nextArrayOfHeaders.length < MAX_LENGTH) { currentIndex++; return buildArray( remainingRowsNext, nextArrayOfHeaders, size, ALPHABETICAL_ORDERED_LETTERS[currentIndex].toUpperCase(), currentIndex, ); } return nextArrayOfHeaders; } const buildExcelLikeHeaders = row => { if (row <= ALPHABETICAL_ORDERED_LETTERS.length) { return ALPHABETICAL_ORDERED_LETTERS.slice(0, row); } const remainingRows = row - ALPHABETICAL_ORDERED_LETTERS.length; const referenceSize = row; return buildArray(remainingRows, ALPHABETICAL_ORDERED_LETTERS, referenceSize); }; 

Answer 4

@Aqm answer will return incorrect result if index> 702

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

the following is the corrected version

(I do not 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 AI and 0 to @ } else { code -= 23; // convert ap to JZ } 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"