The other, other AA
As part of a project for my day job, I'm writing Java code that manipulates Excel spreadsheets. I have a third-party library that handles the gritty details; I can tell it to load a sheet, then ask it for cell contents by row and column. It uses 1-origin coordinates for the rows (so that they match Excel's idea of row numbers) and 0-origin numeric coordinates for the columns.
The latter causes a problem, because Excel labels columns by letter groups rather than numbers. The first 26 columns are labeled A to Z; the 27th column is then AA, followed by AB, AC, and so on to AZ, which is in turn followed by BA. My code needs to be able to go back and forth between this user-visible representation of column labels and the corresponding 0-origin column indices.
My first thought was that the Excel alphabetic notation could be treated as a simple base 26 number, which would be trivial to convert to and from base 10. However, this turns out not to be the case. I soon realized that the Excel notation has no zero. A can't be zero, because it shows up in the 26s place as soon as we go to two digit numbers (AA being the fist of these). So A must correspond to 1, meaning that A through Z map to 1 through 26, AA is 27, and so forth. Subtracting 1 from this value would then get me my desired offset-0 base 10 column coordinate.
But how to manage the conversion to and from this odd zeroless variant of base 26? Here are the functions I came up with. I'm not happy with them, as they involve conditional logic and other things I find ugly in a numeric-base conversion. But so far I haven't come up with a more elegant approach. Suggestsions are welcome.
public static int base26to10(String base26) { final char[] chars26 = base26.toUpperCase().toCharArray(); int val = 0; for (char c : chars26) { val = (val * 26) + (c - 'A') + 1; } return val - 1; } public static String base10to26(int base10) { final StringBuffer buf = new StringBuffer(); int val = base10 + 1; while (val > 0) { int digit = val % 26; if (digit == 0) { digit = 26; val--; } val /= 26; buf.append((char) ('A' + digit - 1)); } return buf.reverse().toString(); }