Convert a column number to a column name. Java example.

In one of my classes we built a spreadsheet program using java.  One of the problems we had to figure out was how to access the columns.  In a spreadsheet the columns are referenced by letters: column A, column AAZ, etc.  Our underlying data structure consisted of array lists of array lists.  Obviously you can’t reference a cell by a letter it has to be a zero based number reference.

So now you have to realize A is column 1 and Z is column 26.  AA is 27, AB is 28 and so on.  To access a certain column you need its number.  But you also need to  get a column name from a number.  This can be tricky.  The columns are one based, A=1, but the indices of the data structure underneath are zero based, A=0.

A Google search came up with plenty of examples but all that I found only worked in a certain range of numbers.  My buddy and I came up with the following algorithm that worked much nicer.  Below is the implementation in Java.  Casting to a char from an int in Java uses ASCII values.  You may need to play with this part of the example in other languages.

public String getColName (int colNum) {

   String res = "";

   int quot = colNum;
   int rem;        
    /*1. Subtract one from number.
    *2. Save the mod 26 value.
   *3. Divide the number by 26, save result.
   *4. Convert the remainder to a letter.
   *5. Repeat until the number is zero.
   *6. Return that bitch...
   */
    while(quot > 0)
    {
        quot = quot - 1;
        rem = quot % 26;
        quot = quot / 26;
       
        //cast to a char and add to the beginning of the string
        //add 97 to convert to the correct ASCII number
        res = (char)(rem+97) + res;            
    }   
    return res;
}

Advertisement