Excel column string to row number and vice versa

Question

Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc. You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number. And then do the exact reverse, given column number return the column name.

Also verify complexity: \$O(\log n)\,ドル where \$n\$ is the input number while \$\log\$ is to base the base being considered (hexa, decimal or binary etc.).

public final class Excel {
 private Excel() {} 
 public static int getExcelColumnNumber(String column) {
 int result = 0;
 for (int i = 0; i < column.length(); i++) {
 result *= 26;
 result += column.charAt(i) - 'A' + 1;
 }
 return result;
 }
 public static String getExcelColumnName(int number) {
 final StringBuilder sb = new StringBuilder();
 int num = number - 1;
 while (num >= 0) {
 int numChar = (num % 26) + 65;
 sb.append((char)numChar);
 num = (num / 26) - 1;
 }
 return sb.reverse().toString();
 }
 public static void main(String[] args) {
 Assert.assertEquals(53, getExcelColumnNumber("BA"));
 Assert.assertEquals("BA", getExcelColumnName(53));
 Assert.assertEquals(703, getExcelColumnNumber("AAA"));
 Assert.assertEquals("AAA", getExcelColumnName(703));
 Assert.assertEquals(26, getExcelColumnNumber("Z"));
 Assert.assertEquals("Z", getExcelColumnName(26));
 Assert.assertEquals(702, getExcelColumnNumber("ZZ"));
 Assert.assertEquals("ZZ", getExcelColumnName(702));
 }
}

Answer 1

Your code is basically fine, and your unit tests are good. All I have is nitpicks.

"Excel" in the method names are a bit redundant.

In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.

In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.

The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)

public final class ExcelColumn {
 private ExcelColumn() {}
 public static int toNumber(String name) {
 int number = 0;
 for (int i = 0; i < name.length(); i++) {
 number = number * 26 + (name.charAt(i) - ('A' - 1));
 }
 return number;
 }
 public static String toName(int number) {
 StringBuilder sb = new StringBuilder();
 while (number-- > 0) {
 sb.append((char)('A' + (number % 26)));
 number /= 26;
 }
 return sb.reverse().toString();
 }
}

• h.j.k.: "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.