Apache poi Excel: Creating a formula based on the

问题:

Say that my column number is 26, when I create a formula my formula should look like, say: SUM(AA1:AA3) for example. but how do I translate 26 to AA? Or say 27 to AB? Is there a way for Apache POI to use Column index as an integer and translate it into its letter representation?

回答1:

That requires just a little code:

public static String columnName(int index) {
    StringBuilder s = new StringBuilder();
    while (index >= 26) {
        s.insert(0, (char) ('A' + index % 26));
        index = index / 26 - 1;
    }
    s.insert(0, (char) ('A' + index));
    return s.toString();
}

And something to test it:

public static void main(String[] args) {
    System.out.println(columnName(25));
    System.out.println(columnName(26));
    System.out.println(columnName(52));
    System.out.println(columnName(27 * 26));
}

Output:

Z
AA
BA
AAA

回答2:

You'll want to use CellReference from Apache POI. That provides the methods you need to convert

For your specific case, you want convertNumToColString(int)):

Takes in a 0-based base-10 column and returns a ALPHA-26 representation.

回答3:

Erwin Bolwidt has the right idea.

In english it would be:

num / 26 - 1 = first_letter
num % 26     = second_letter

So for example:

27 / 26 - 1 = 0 => A
27 % 26     = 1 => B

Thus:

27 = AB