current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 7 down vote favorite

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

    }
}
share|improve this question

1 Answer 1

up vote 7 down vote

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();
    }
}
share|improve this answer
    
"In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point. –  h.j.k. Mar 18 at 0:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.