Archive

Posts Tagged ‘Microsoft Office’

Excel Column Name Calculator

March 16, 2010 1 comment

I need to calculate the column name of a cell in Excel so here is a class I am using to do so.

public class ExcelColumnCalculator
    {
        private int index;
        private readonly char[] chars = new[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };

        public ExcelColumnCalculator(int index)
        {
            this.index = index;
        }

        public string CalculateColumnName()
        {
            AdjustIndexToZeroBased();
            return ColumnName(index);
        }

        private string ColumnName(int position)
        {
            int quotient = position / 26;
            if (quotient > 0)
                return ColumnName(quotient) + chars[position % 26];
            return chars[position % 26].ToString();
        }

        private void AdjustIndexToZeroBased()
        {
            index -= 1; //adjust so it matches 0-indexed array rather than 1-indexed column    
        }

        public int Index
        {
            get { return index; }
        }

    }

Blair..