Excel Sheet Column Title/Number

Leave a comment

February 13, 2017 by oneOokay

啊 我发现我竟然不太会进制转化…

Excel Sheet Column Title

Given a positive integer, return its corresponding column title as appear in an Excel sheet.

For example:

    1 -> A
    2 -> B
    3 -> C
    ...
    26 -> Z
    27 -> AA
    28 -> AB

Excel Sheet Column Number

Given a column title as appear in an Excel sheet, return its corresponding column number.

For example:

    A -> 1
    B -> 2
    C -> 3
    ...
    Z -> 26
    AA -> 27
    AB -> 28

以上两个其实是一个26进制的数和10进制的数的相互转换.

首先由字母转成数字:

  • for loop, 从index = 0的字母开始转换.
  • n X 进制数 + 字母代表的单数 为新的n, 以此循环.
public int titleToNumber(String s) {
 int n = 0;
 for (int i = 0; i < s.length(); i ++){
 n = n * 26 + s.charAt(i) - 'A' + 1;
 }
 return n;
 }

然后由数字转换成字母:

  • while loop (n > 0)
  • 确定字母: n % 进制数. 两种加入到str的方法:
    1. 用stringbuilder不断加载sb后,最后reverse()
    2. 插入到String前面: c + s
  • 更新n: n = n/进制数
public String convertToTitle(int n) {
        StringBuilder sb = new StringBuilder();
        while (n > 0){
            char c = (char)((n - 1) % 26 + 'A');
            sb.append(c);
            n = (n - 1) / 26;
        }
        return sb.reverse().toString();
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: