Apache POI, translating Excel column number to letter

Recently I had to write some code to generate excel files using Apache POI. One of the files was fairly complex and used formulas in a few cells.

I found myself spending a few hours searching for a way to translate the indexed column numbers that POI uses to the column letters I needed in those formulas.

It turns out the solution is quite simple, you just have to use org.apache.poi.hssf.util.CellReference:

String columnLetter = CellReference.convertNumToColString(columnNumber);

6 Responses to Apache POI, translating Excel column number to letter

  1. alidadasb says:

    Thank you so much. It helped me.

  2. aaa says:

    thank you very much. It helped me to.

  3. gregjallen says:

    Even easier: new CellReference(cell).formatAsString()

  4. varantes says:

    Holly crap! So easy!!! Tks a lot!!!!!!

  5. jechaviz says:

    A hand-made way
    public static String columnName(int columnNumber){
    String characterSet=”ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
    String columnNameReverseOrder=””;
    int reminder;
    //numbersSet=”0123456789abcdef”;
    do {
    reminder=(–columnNumber%characterSet.length());
    columnNameReverseOrder+=characterSet.charAt(reminder);
    columnNumber=columnNumber/characterSet.length();
    }while(columnNumber>0);
    String columnName=””;
    for(int i=columnNameReverseOrder.length()-1; i>=0; i–)
    columnName+=columnNameReverseOrder.charAt(i);
    return columnName;
    }

  6. jechaviz says:

    A little improved way:
    public static String cl (int columnNumber){
    //cL stands for columnLetter
    //it’s based on a special case of number base change
    //by example in hex, characterSet=”0123456789ABCDEF”
    String characterSet=”ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
    String columnName=””;
    int reminder;
    do {
    //delete — in next line if you wish just baseChange (binary, etc)..
    reminder=(–columnNumber%characterSet.length());
    columnName=characterSet.charAt(reminder)+columnName;
    columnNumber=columnNumber/characterSet.length();
    }while(columnNumber>0);
    return columnName;
    }

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: