Java Code to find Excel Column Name of given Column Number

Before writing the program to find the Column Name of a given Excel Column Number, we have to understand little bit about excel worksheet.

The size of work sheet of Excel 2007 is 1048576 rows by 16384 columns. The first column name starts with ‘A’ , then ‘B’ , ‘C’, ……’Z’ , then ‘AA’ , ‘AB’ , ‘AC’ ……….’AZ’, then ‘BA’, ‘BB’, ……’BZ’ and the Last column name is XFD.

The column number for the above column name will be 1- A, 2 – B, …..26 – Z, 27 – AA, 28 – AB, ……….52-AZ, 53 – BA, 54 – BB, …. 78 – BZ, ………..16384 – XFD

So the base value is 26. Suppose consider column number n= 28 . Steps to calculate column name for column number 28 is as follows

Step 1 : n%26 => 28 % 26 = 2, Now the column name for the column number 2 is ‘B’
Step 2 : (n-1)/26 => (28-1) / 26 = 1, Now n=1, continue step 1 until n>0
i.e Step 1: 1%26 =1, Now the column name for the column number 1 is ‘A’

So result will be merging the result of both step 1 is ‘A’ + ‘B’ => AB

The following code accepts the column number from the command prompt and finds the Excel Column Name .

package com.javaonline;

import javax.swing.JOptionPane;

public class FindExcelColumn {

public static void main(String args[])
{

	String input = JOptionPane.showInputDialog("Enter any column number: "); // Di

	int colNum=Integer.parseInt(input);

	System.out.println("Excel Column Name  Given ColumNumber "+colNum+ "  is   " + ExcelColumnName(colNum));


}

   private static  String ExcelColumnName(int colNum) {
        int Base = 26;
        String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        String colName = "";

        while (colNum > 0) {
           int position = colNum % Base;
           colName = (position == 0 ? 'Z' : chars.charAt(position > 0 ? position - 1 : 0)) + colName;
           colNum = (colNum - 1) / Base;
        }
        return colName;
    }

}

Running the above code will output as follows for the inputs 28, 16384, 26

Excel Column Name Given ColumNumber 28 is AB

Excel Column Name Given ColumNumber 16384 is XFD

Excel Column Name Given ColumNumber 26 is Z