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();
    }
}