Apache poi Excel: Creating a formula based on the integer index of the column

Question

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?

Answer 1

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.

Answer 2

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