The World’s Leading Microsoft .NET Magazine
Sign-In | Register
   
 
timstall

Donate Today!

Search Box

 

Calendar

««Mar 2010»»
SMTWTFS
  12
3
456
7
8
9
10
111213
14151617181920
21222324252627
28293031

My RSS Feeds








Mailing List

Most Popular Tags

                                                           

Reading Excel from .Net: Creating a Cell structure

« H E General :: email
posted Thursday, 29 September 2005

I've been blogging a series on enhancing Excel with .Net. One way to do this is to have some process in Excel kick off a .Net Console app, and than have the .Net app do all the difficult programming (where you have the support of the .Net Framework) as opposed to VBA. In order to do this, we need to be able to read an Excel sheet from .Net.

Previous posts in this series were:

This post will start showing how to read Excel from .Net. This is pretty standard. If you google it you'll see a lot of variations. However, many "solutions" are not very reusable. We'd like to create a reusable ExcelReader utility class. This will be much easier if we first build a "Cell" class. This Class should have:

  • Constructors to take a row and column in both RC (int-int) and A1 (letter-int)  notation.
  • Properties for the row and column (even if you use the A1 notation). So if you enter the column 'C', it will return 3.
  • A Property to always return the A1Reference. So if you enter (2,2), it should return "B2".

This post will show how to build and test this cell. The next post will apply it to our ExcelReader.
 

Source Code:

using System;

namespace ExcelUtilities
{
    public struct Cell 
    {   
        #region Constructors

        public Cell(int intRow, int intCol)
        {
            if (intRow < 1)
                throw new ArgumentException("Row must be greater than 0");
            if (intCol < 1)
                throw new ArgumentException("Column must be greater than 0");
            _intRow = intRow;
            _intCol = intCol;
            _strA1Reference = ConvertIntToStringColumn(intCol) + intRow;
        }

        public Cell(string strCol, int intRow)
        {
      if (strCol == null)
        throw new System.ArgumentNullException("Column can't be null");
      if (intRow < 1)
        throw new ArgumentException("Row must be greater than 0");

            _intRow = intRow;
            _intCol = ConvertStringToIntColumn(strCol);
            if (_intCol > 256)
                throw new ArgumentException("Column cannot be greater than IV (256). Value was '" + strCol + "'.");

            _strA1Reference = strCol.ToUpper() + intRow.ToString();
        }  
       
        #endregion

        #region Implementation

        private static int ConvertStringToIntColumn(string strCol)
        {
            strCol = strCol.ToUpper();
            //A --> 1, BD --> (26+4), IV     
            //Can only have max of two characters.     
            char[] ach = strCol.ToCharArray();
            if (ach.Length == 0)
                throw new ArgumentException("Column cannot have 0 length. Must be A - IV. Was: '" + strCol + "'.");
            else if (ach.Length == 1)
                return GetNumberValue(ach[0]);
            else       
                return 26*GetNumberValue(ach[0]) + GetNumberValue(ach[1]);
        }

        private static string ConvertIntToStringColumn(int intCol)
        {
            if (intCol < 1)
                throw new ArgumentOutOfRangeException("intCol",intCol,"Column must be between 1 and 256.");
            if (intCol > 256)
                throw new ArgumentOutOfRangeException("intCol",intCol,"Column must be between 1 and 256.");

            //single digit, just convert it.
            if (intCol <= 26)   
                return GetCharacterValue(intCol).ToString();

            //multiple char
            int intDigit1 = intCol / 26;
            int intDigit2 = intCol % 26;

            string s = GetCharacterValue(intDigit1).ToString() + GetCharacterValue(intDigit2).ToString();

            return s;
        }

        private static char GetCharacterValue(int i)
        {
            return (char)(i + 64);
        }

        private static int GetNumberValue(char c)
        {
            return (int)c - 64;
        }

   
        #endregion

        #region Public Properties

        private int _intRow, _intCol;
        private string _strA1Reference;

        public int Row
        {
            get
            {
                return this._intRow;
            }
        }

        public int Column
        {
            get
            {
                return this._intCol;
            }
        }

        public string A1Reference
        {
            get
            {
                return _strA1Reference;
            }
        }

        #endregion

        #region Test Wrappers

#if DEBUG

        public static string TestConvertIntToStringColumn(int intCol)
        {
            return ConvertIntToStringColumn(intCol);
        }

#endif

        #endregion

    }
}

Unit Tests:

This kind of class - purely an in-memory data structure with no external dependencies, is easily unit tested. This codes uses the NUnit 2.2 Framework.

using System;
using NUnit.Framework;
using ExcelUtilities;

namespace UnitTests
{
    /// <summary>
    /// Summary description for TestCell.
    /// </summary>
    [TestFixture] public class TestCell
    {
        public TestCell()
        {

        }

        #region A1 Constructor
        [Test] public void GetRowAndColumn_1() 
        {
            Cell c = new Cell("B",3);
            Assert.AreEqual(3,c.Row);
            Assert.AreEqual(2,c.Column);
        }

        [Test] public void GetRowAndColumn_IgnoreCase() 
        {
            Cell c = new Cell("b",3);
            Assert.AreEqual(3,c.Row);
            Assert.AreEqual(2,c.Column);
        }

        [Test] [ExpectedException(typeof(ArgumentException))] 
        public void GetRowAndColumn_InvalidCol() 
        {
            Cell c = new Cell("ZZ",3);
        }

        [Test] [ExpectedException(typeof(ArgumentException))] 
        public void GetRowAndColumn_InvalidRow() 
        {
            Cell c = new Cell("A",-1);
        }

    [Test] [ExpectedException(typeof(System.ArgumentNullException))] 
    public void GetRowAndColumn_InvalidColumn0() 
    {
      Cell c = new Cell(null,-1);
    }

    [Test] [ExpectedException(typeof(System.ArgumentException))] 
    public void GetRowAndColumn_InvalidColumn1() 
    {
      Cell c = new Cell("",-1);
    }

    [Test] [ExpectedException(typeof(System.ArgumentException))] 
    public void GetRowAndColumn_InvalidColumn2() 
    {
      Cell c = new Cell("#$%",-1);
    }

        #endregion

        #region RC constructor

        [Test] public void RC_1() 
        {
            Cell c = new Cell(2,3);
            Assert.AreEqual(2,c.Row);
            Assert.AreEqual(3,c.Column);
        }

        [Test] [ExpectedException(typeof(ArgumentException))] 
        public void RC_2() 
        {
            Cell c = new Cell(-1,3);
        }

        [Test] [ExpectedException(typeof(ArgumentException))] 
        public void RC_3() 
        {
            Cell c = new Cell(1,-1);
        }

        #endregion

        #region ConvertIntToStringColumn
        
#if DEBUG
        [Test] [ExpectedException(typeof(System.ArgumentOutOfRangeException))] 
        public void ConvertIntToStringColumn_1() 
        {
            Cell.TestConvertIntToStringColumn(-1);
        }

        [Test] [ExpectedException(typeof(System.ArgumentOutOfRangeException))]
        public void ConvertIntToStringColumn_2a() 
        {
            Cell.TestConvertIntToStringColumn(0);
        }

        [Test] [ExpectedException(typeof(System.ArgumentOutOfRangeException))]
        public void ConvertIntToStringColumn_2() 
        {
            Cell.TestConvertIntToStringColumn(257);
        }

        [Test] public void ConvertIntToStringColumn_3() 
        {
            Assert.AreEqual("A",Cell.TestConvertIntToStringColumn(1));
        }

        [Test] public void ConvertIntToStringColumn_4() 
        {
            Assert.AreEqual("IV",Cell.TestConvertIntToStringColumn(256));
        }

        [Test] public void ConvertIntToStringColumn_5() 
        {
            Assert.AreEqual("Z",Cell.TestConvertIntToStringColumn(26));
        }

#endif

        #endregion

        #region A1Reference

        [Test] public void A1Reference_1a() 
        {
            Cell c = new Cell("B",3);
            Assert.AreEqual("B3",c.A1Reference);
        }

    [Test] public void A1Reference_CaseInsensitive() 
    {
      Cell c = new Cell("b",3);
      Assert.AreEqual("B3",c.A1Reference);
    }

        [Test] public void A1Reference_2() 
        {
            Cell c = new Cell(3,2);
            Assert.AreEqual("B3",c.A1Reference);
        }

        #endregion


    }
}

There's not much to explain about this code. One note about using the #if DEBUG to enclose a test-wrapper. We could use reflection to avoid this, as explained in this Code Project article, but I was just being quick and informal. Next we'll do the more interesting task of using this in an ExcelReader utility class.

links: digg this    technorati    


comments (1)


1. Christopher Rath left...
Wednesday, 22 February 2006 11:57 am :: http://rath.ca/

I don't believe the ConvertIntToStringColumn() function will work correctly. For example, try converting column 52 (i.e., column AZ). See http://rath.ca/Misc/VBA for a VBA example that I know works.