Apache poi Excel: creating a formula based on an integer column index

Question

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

Answer 1

This requires only a small 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 check something:

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

Answer 2

You want to use CellReference from Apache POI. This provides the methods needed to convert

convertNumToColString (int)):

base-10, 0, ALPHA-26.

Answer 3

Erwin Bodwidt has the right idea.

In English, it will be:

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

So for example:

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

In this way:

27 = AB