Saturday, July 11, 2009

Get letter code for a column index in Excel

Very often, when working with Excel via COM, selecting of particular range of cells might be required. For this purpose method get_Range() of Microsoft.Office.Interop.Excel._Worksheet class. It works only if we pass a range in the letter-digit format. Letters for columns and digits for rows, for instance call of get_Range("A9", "IV9") will get range of whole 9th row of the sheet. Sometimes, we do not know a letter code for current column. Usually we have several loops that iterate cells, forming ranges and putting data into Excel. I was surprised when found out what there is no way to get range without knowing a letter codes for particular columns. Of course we still can get a cell by calling Cells[i,j] property, but it is very slow way of forming a sheet. If we, for example, need export a large array of doubles to Excel, it is not very clever to iterate through the array and export its values one by one. Much better approach instead is to select a range that equals to size of the array and call set_Value() method passing the array into it. It is much faster and more convenient.
So I decided to write converter that will convert column index (0-255) to corresponding letter code (A-IV). The algorithm of columns’ naming used by Excel is pretty simple:

A-Z : 1-26
AA-AZ : 27-52
BA-BZ : 53-78
CA-CZ : 79 - 104
... : ...
IA-IV : 235-255

The regularity is obvious here, thus I wrote following code:

public static string GetExcelLetterCodeForColumnIndex(int columnIndex)
    {
      const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
      const byte lettersCount = 26;

      if (columnIndex < 0 || columnIndex > 255)
        throw new ArgumentOutOfRangeException("columnIndex is out of the range [0..255]");

      if (columnIndex < lettersCount)
        return alphabet[columnIndex].ToString();

      byte firstIndex = (byte)Math.Floor((double)(columnIndex/lettersCount - 1));
      byte secondIndex = (byte)(columnIndex - (firstIndex + 1)*lettersCount);
    
      return String.Format("{0}{1}", alphabet[firstIndex], alphabet[secondIndex]);
    }

* This source code was highlighted with Source Code Highlighter.

This method is safe and can be used without doubt. Here are testing code and test results:
[Test]
    [Category(("Utils_Tests"))]
    public void GetExcelLetterCodeForColumnIndex()
    {
      try
      {
        Utils.GetExcelLetterCodeForColumnIndex(-434923);
        Assert.Fail();
      }
      catch(ArgumentOutOfRangeException)
      {
        
      }

      try
      {
        Utils.GetExcelLetterCodeForColumnIndex(Int32.MaxValue);
        Assert.Fail();
      }
      catch (ArgumentOutOfRangeException)
      {

      }

      Assert.AreEqual("A",Utils.GetExcelLetterCodeForColumnIndex(0));
      Assert.AreEqual("IV", Utils.GetExcelLetterCodeForColumnIndex(255));

      const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
      const byte lettersCount = 26;
      byte currentIteration = 0;
      do
      {
        for (byte i = 0; i < alphabet.Length; i++)
        {
          for (byte j = 0; j < alphabet.Length; j++)
          {
            string testString = currentIteration < lettersCount
                     ? alphabet[j].ToString()
                     : String.Format("{0}{1}", alphabet[i-1], alphabet[j]);
            Assert.AreEqual(testString, Utils.GetExcelLetterCodeForColumnIndex(currentIteration));
            Debug.WriteLine(String.Format("{0}-{1}",testString, currentIteration));
            
            if (++currentIteration == byte.MaxValue)
              return;
          }
        }
      } while (true);
    }

* This source code was highlighted with Source Code Highlighter.


Initially I used byte variable as an input parameter and didn’t perform any check. But it is not convenient always to use byte variables in code and it is not safe to perform castings before passing int values into the method.

2 comments:

him said...

please give me your mail ID..

Bashir Magomedov said...

For what?