Monthly Archives: August 2009

Obviously I’ve missed my self made time schedule of Monday, Wednesday, and Friday for these entries, so bare with me while I get em’ published as fast as possible. With that written . . .

Welcome to part three of the Excel Helper Utilities Library series. In this entry I will cover the extension of the ExcelHelper class created in Part Two. The first method is one I wrote ages ago for VB.NET, but I’ve added here for the C# helper. As always, tests first.

[Test]

public void GetColumnLetterA()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(1);

    Assert.AreEqual(twentySeventColumn, "A");

[Test]

public void GetColumnLetterB()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(2);

    Assert.AreEqual(twentySeventColumn, "B");

[Test]

public void GetColumnLetterAa()

    string twentySeventColumn = ExcelHelper.GetExcelColumnLetter(703);

    Assert.AreEqual(twentySeventColumn, "AAA");

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

. . . and the implemented code . . .

/// <summary>

/// This method gets the appropriate "A" thru "ZZZ" column header for Excel based

/// on a passed in long data type.

/// </summary>

/// <param name="columnIndex">Pass in a long type to derive the appropriate column header letter.</param>

/// <returns>A string of the appropriate "A" thru "ZZZ" header letter.</returns>

public static string GetExcelColumnLetter(int columnIndex)

    int dividend = columnIndex;

    string columnName = String.Empty;

    while (dividend > 0)

        int mod = (dividend - 1)%26;

        columnName = Convert.ToChar(65 + mod) + columnName;

        dividend = ((dividend - mod)/26);

    return columnName;

The next thing I wanted was a column range set, basically something that would return "A:A" if I passed it the start column index and ending column index. Easy enough, again, tests first.

[Test]

public void GetColumnLetterSetAandA()

    const string columnStart = "1";

    const string columnEnd = "4";

    const string expectedValue = "A:D";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

[Test]

public void GetColumnLetterSetAandAaa()

    const string columnStart = "1";

    const string columnEnd = "703";

    const string expectedValue = "A:AAA";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

[Test]

public void GetColumnLetterSetAandZ()

    const string columnStart = "1";

    const string columnEnd = "26";

    const string expectedValue = "A:Z";

    string returnedValue = ExcelHelper.GetExcelColumnSet(columnStart, columnEnd);

    Assert.AreEqual(expectedValue, returnedValue);

. . . and the implementation . . .

/// <summary>

/// This method returns the ("A:ZZZ") part of the Range("A:ZZZ") column interface.

/// </summary>

/// <param name="firstColumn">Enter the first column to start the range with.</param>

/// <param name="secondColumn">Enter the second column to end the range with.</param>

/// <returns>A string formatted as A:ZZZ is returned from this method.</returns>

public static string GetExcelColumnSet(string firstColumn, string secondColumn)

    int firstColumnValue = Convert.ToInt32(firstColumn);

    int secondColumnValue = Convert.ToInt32(secondColumn);

    return GetExcelColumnLetter(firstColumnValue) + ":" + GetExcelColumnLetter(secondColumnValue);

That is the short and sweet of this entry. I have to catch a bus and get a move on, but the next entry will be a few cell manipulations to simplify capturing and putting things in the cells in Excel.

If you visit, please give this entry a kick over at

Blog Entry Roundup

August 25, 2009

Uncategorized Bits

3 Comments

A few of the good blog entries I have had the pleasure of reading the last few weeks.

Writing Great Unit Tests: Best and Worst Practices by Steve Sanderson. This entry is just an overview of unit testing, but really well written. Also Steve placed a few nice charts in defining unit & integration tests. The combination just made for a nice refresher, or for the test newbie, a great introduction.

Gergely Orosz has some articles on NHibernate versus Entity Framework performance; Thoughts on ranking ORM tools, NHibernate vs Entity Framework: a performance test, and Nhibernate vs Entity Framework ? a revised performance comparison. The interesting conclusion is that there is no good effective way to compare ORM platforms. I suppose, it best to compare on the specific metrics you desire in an ORM platform, then choose.

The REST for ASP.NET MVC SDK entry is a good read. First off it states what it is and why you would want to use it, which would be helpful for many. It caught my eye solely because I would have loved to have the functionality 10 months ago for a project, instead of manually developing all the bits. At least it is out now and I will surely be using it as such.

Last but not least, there is the Facebook & ASP.NET MVC entry. This is a useful entry, being I have just jumped onto a project doing some Facebook Development this is a very helpful entry in getting up to speed.

Excel Helper Utilities Library Part Two

August 19, 2009

Software Projects

Leave a Comment

In the last entry I covered a range of utility class methods including appropriate unit tests. Today I want to cover the first steps into the twisted world of Excel Development with C#. Note, there are many things I probably will not mention that are pivotal to this code working. Namely the order in which Excel objects & framework interfaces and components are instantiated are of massive importance within managed code. So if you are following along, first do the exact example here and then step on to changes. That way you will have working code before arbitrarily having non-working code.

As always, tests are first. In this entry I will actually step from test, to code, then green light the test for each part versus writing all the tests and then working through the methods. That way I can cover each method and the purpose of the method.

Before even jumping straight into tests we will need to add two specific assemblies that are needed to test Excel appropriately; Microsoft.Office.Interop.Excel and Microsoft.Office.Tools.Excel.v9.0. (Click the images to see larger images)

Now to get started with Excel unit tests, we really do need to use the Excel Assemblies we’ve added because mocking just doesn’t cut it. Because of the legacy code & other such droves of complexities, it is extremely hard to really mock accurately. One really needs to know when the actual application is behaving appropriately under load & use from software written to use it. With that said, here’s my first few tests & setup code.

I start out by creating the test class and creating a public property to act for all tests as the actual Excel Application under test.

[TestFixture]
public class ExcelHelperTests
{
    public Application ExcelApplication { get; set; }

Next I create two tests for setup and teardown of the Excel Application object. In addition to the basic Excel Application object, one thing I have learned about Excel from experience, is that one needs to go ahead and immediately add a workbook. If there is no workbook, Excel can throw all sorts of odd errors that provide very little insight into what is going on. So just remove that possibility by adding that default workbook.

[TestFixtureSetUp]
public void CreateExcelAppAppropriately()
{
    ExcelApplication = new Application();
    ExcelApplication.Workbooks.Add(Type.Missing);
    ExcelApplication.Visible = true;
}
[TestFixtureTearDown]
public void QuitExcelAppAppropriately()
{
    foreach (Workbook workbook in ExcelApplication.Workbooks)
    {
        workbook.Close(false, false, Type.Missing);
    }
    ExcelApplication.Quit();
}

Now that this is setup I can really test how the class will react and behave under test with Excel. Just to make sure, I verify that the appropriate Excel Application object is created and has a single workbook that was added in the fixture setup.

[Test]
public void InitializeDefaultWorkbook()
{
    Assert.GreaterOrEqual(1, ExcelApplication.Workbooks.Count);
}

Next on the list is finally getting to the real nitty gritty of the Excel Helper class I’m creating. My first test to begin this class is as follows.

[Test]
public void ExcelHelperInstantiation()
{
    var excelHelper = new ExcelHelper(ExcelApplication);
    Assert.IsNotNull(excelHelper);
    Assert.IsNotNull(excelHelper.ExcelApplication);
}

With these tests throwing errors I’m now ready to add my initial class skeleton.

/// <summary>
/// This class provides helper utility methods for Excel 2007 that cover common
/// needs such as creating a new worksheet, or getting an insantiated instance
/// of an existing worksheet.
/// </summary>
public class ExcelHelper
{
    /// <summary>
    /// This class has a single constructor used to 
    /// setup the needed Microsoft.Office.Interop.Excel.Application
    /// object for use.
    /// </summary>
    /// <param name="excelApplication">Pass the active Excel Application
    /// object.</param>
    public ExcelHelper(Application excelApplication)
    {
        ExcelApplication = excelApplication;
    }
    /// <summary>
    /// This property is set to the active application the class
    /// executes methods against.
    /// </summary>
    public Application ExcelApplication { get; set; }
}

Run the test now and you should get a green light. Moving along I added a test for the first method I want, a way to retrieve a Worksheet that already exists in Excel. First my test looks something like this.

[Test]
public void GetWorksheet()
{
    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,
                                                             Type.Missing) as Worksheet;
    const string newWorksheetName = "UniqueName";
    newWorksheet.Name = newWorksheetName;
    Worksheet foundNewWorksheet = new ExcelHelper(ExcelApplication).GetWorksheet(newWorksheetName);
    Assert.IsNotNull(foundNewWorksheet);
    Assert.AreEqual(foundNewWorksheet.Name, newWorksheet.Name);
}

I get a red light and then implement the code to return a Worksheet Object based on the passed in worksheet name. It fails, since the method isn’t created, so I create the method.

/// <summary>
/// This method retrieves a specific worksheet by name.
/// </summary>
/// <param name="worksheetName">Pass the name of the
/// worksheet to retrieve.</param>
/// <returns>The Worksheet Object of the requested
/// worksheet, or if not found null.</returns>
public Worksheet GetWorksheet(string worksheetName)
{
    Worksheet foundSheet = null;
    foreach (Worksheet sheet in ExcelApplication.Worksheets)
    {
        if (sheet.Name == worksheetName)
            foundSheet = sheet;
    }
    return foundSheet;
}

Next test is for creating a new Worksheet, and adding it to the Excel Workbook.

[Test]
public void NewWorksheet()
{
    const string newWorksheetName = "UniqueName";
    new ExcelHelper(ExcelApplication).NewWorksheet(newWorksheetName);
    bool foundWorksheet = false;
    foreach (Worksheet worksheet in ExcelApplication.Worksheets)
    {
        if(worksheet.Name == newWorksheetName)
            foundWorksheet = true;
    }
    Assert.IsTrue(foundWorksheet);
}

Red lighted, so I add the method.

/// <summary>
/// This method creates a new worksheet with the name
/// specified.
/// </summary>
/// <param name="name">Pass the name you want the new
/// worksheet to have.</param>
/// <returns>Returns the new Worksheet object.</returns>
public Worksheet NewWorksheet(string name)
{
    var returnToThisWorksheet = ExcelApplication.ActiveSheet as _Worksheet;
    var returnSheet = ExcelApplication.Worksheets.Add(
                          Type.Missing,
                          Type.Missing,
                          Type.Missing,
                          Type.Missing) as Worksheet;
    returnSheet.Name = name;
    if (returnToThisWorksheet != null) returnToThisWorksheet.Activate();
    return returnSheet;
}

Now that I’ve simplified the methods to create and retrieve a worksheet from Excel, I want to add some functionality around determining if a Worksheet already exists within a workbook. This is usually a common need within Excel and I find many examples are simply a foreach loop stepping through each Worksheet until it found or did not find the Worksheet it is looking for. This is a perfect example of something that would be better as a method.

First the test, which I’ll make a static method in case one wants to call this method without instantiating?

[Test]
public void IsExistingWorksheetName()
{
    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,
                                                                         Type.Missing) as Worksheet;
    const string newWorksheetName = "UniqueName";
    newWorksheet.Name = newWorksheetName;
    ExcelHelper.IsExistingWorksheetName(newWorksheetName, ExcelApplication.Worksheets);
}

and a test for a static method?

[Test]
public void IsExistingWorksheetNameInstantiatedMethod()
{
    var newWorksheet = ExcelApplication.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing,
                                                              Type.Missing) as Worksheet;
    const string newWorksheetName = "UniqueName";
    newWorksheet.Name = newWorksheetName;
    ExcelHelper helper = new ExcelHelper(ExcelApplication);
    Assert.IsTrue(helper.IsExistingWorksheetName(newWorksheetName));
    foreach (Worksheet sheet in ExcelApplication.Worksheets)
    {
        if (sheet.Name == newWorksheetName)
            sheet.Delete();
    }
    Assert.IsFalse(helper.IsExistingWorksheetName(newWorksheetName));
}

As I finished this test and got a red light I realized, I needed to assure the other tests cleaned up after themselves like this one does with the deletion of the Worksheet. I then went back and add the snippet

foreach (Worksheet sheet in ExcelApplication.Worksheets)
{
    if (sheet.Name == newWorksheetName)
        sheet.Delete();
}

to each of the tests that needed it. After the tests where appropriately refactored I ran the tests to assure they still where all green lights except for the one I just created. I then jumped back into my ExcelHelper Class and created the methods I just wrote tests for.

/// <summary>
/// This method checks for existing names to prevent identical name collisions within the
/// worksheet collection.
/// </summary>
/// <param name="worksheetName">Pass in the worksheet name to check for.</param>
/// <param name="existingWorksheets">Pass in the Excel worksheets collection to search in.</param>
/// <returns>Return true if existing worksheet name is found, false if no existing worksheet names.</returns>
public static bool IsExistingWorksheetName(string worksheetName, Sheets existingWorksheets)
{
    foreach (_Worksheet worksheet in existingWorksheets)
    {
        string existingWorksheet = worksheet.Name;
        if (worksheetName == existingWorksheet)
            return true;
    }
    return false;
}
/// <summary>
/// This method checks for existing names to prevent identical name collisions within the
/// worksheet collection.
/// </summary>
/// <param name="worksheetName">Pass in the worksheet name to check for.</param>
/// <returns>Return true if existing worksheet name is found, false if no existing worksheet names.</returns>
public bool IsExistingWorksheetName(string worksheetName)
{
    return IsExistingWorksheetName(worksheetName, ExcelApplication.Worksheets);
}

For this entry we’ve covered appropriate tests and methods for getting an Excel Worksheet, creating a new Excel Worksheet, and finding out if a Excel Worksheet Name is already taken. In my next entry on this topic I’ll keep adding more Excel Helper Methods that will help make Excel Addin Development easier.

Excel Helper Utilities Library Part One

August 19, 2009

Uncategorized Bits

3 Comments

I’m kicking off a series that is a long time in the making. The series is 99% complete at this point, and will be published every couple days. Right now I?m aiming for a Monday, Wednesday, Friday type of frequency, but might throw in an extra one here or there. When completed the code will be posted to CodePlex for easy download & an additions that someone might want to contribute. Just hit me up at adron [at@] adronbhall [dot.] com.

Here?s a code file I’ve put together to help project using Excel 2007. These are just some small helper methods, etc., that remove some of the redundant Type.Missing and other insidious things one has to go through when developing with Excel.

This first class is a pure and simple utility class to help clean up strings for use in certain situations that come up with Excel. Some examples might be when trying to clean up a string for a worksheet name.

Of course, as usual, the first thing I hit where the unit tests. Since these methods are relatively simple, I just went ahead and wrote tests for each.

[TestFixture]

public class StringParsingTests

    [Test]

    public void IsNumberTrue()

        const string isNumber = "234";

        Assert.IsTrue(StringParsing.IsNumeric(isNumber));

    [Test]

    public void IsNumberFalse()

        const string notNumber = "noNumberHere";

        Assert.IsFalse(StringParsing.IsNumeric(notNumber));

    [Test]

    public void IsNumberFloat()

        const string isNumber = "232.4419";

        Assert.IsTrue(StringParsing.IsNumeric(isNumber));

    [Test]

    public void IsAlphaNumericTrue()

        const string isAlphaNumeric = "asdf1234";

        Assert.IsTrue(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void IsAlphaNumericFalse()

        const string notAlphaNumeric = "asdf.!~1234";

        Assert.IsFalse(StringParsing.IsValidAlphaNumeric(notAlphaNumeric));

    [Test]

    public void RemoveSpecialCharactersRemoval()

        const string notAlphaNumeric = "asdf!@#$";

        string isAlphaNumeric = StringParsing.RemoveSpecialCharacters(notAlphaNumeric);

        Assert.IsFalse(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void RemoveSpecialCharactersCheckOriginalString()

        const string notAlphaNumeric = "asdf!@#$";

        const string expectedResult = "asdf    ";

        string isAlphaNumeric = StringParsing.RemoveSpecialCharacters(notAlphaNumeric);

        Assert.AreEqual(expectedResult, isAlphaNumeric);

    [Test]

    public void TrimAndRemoveSpecialCharacters()

        const string notAlphaNumeric = "asdf!@#$";

        string isAlphaNumeric = StringParsing.TrimAndRemoveSpecialCharacters(notAlphaNumeric);

        Assert.IsTrue(StringParsing.IsValidAlphaNumeric(isAlphaNumeric));

    [Test]

    public void TrimAndRemoveSpecialCharactersCheckOriginalString()

        const string notAlphaNumeric = "asdf!@#$";

        const string expectedResult = "asdf";

        string isAlphaNumeric = StringParsing.TrimAndRemoveSpecialCharacters(notAlphaNumeric);

        Assert.AreEqual(expectedResult, isAlphaNumeric);

The class I created is named StringParsing within a namespace of Abh.Core.Excel2k7.Utilities. I?ve left the comments in place in case you want to copy & paste the code for use.

using System;

using System.Text.RegularExpressions;

namespace Abh.Core.Excel2k7.Utilities

    /// <summary>

    /// This class provides static methods for parsing and manipulating strings to provide

    /// Excel compliant, or other cleaning methods to trim, check for alphanumeric, and numeric

    /// data types within a string.  These methods are in addition to most existing methods

    /// already available on existing objects in the .NET Framework.

    /// </summary>

    public class StringParsing

Within the class are four methods:

The first method is RemoveSpecialCharacters(string stringToClean). This method does exactly what the name implies, it removes a selection of special characters from a passed in string.

/// <summary>

/// This method removes special characters from a string.

/// </summary>

/// <param name="stringToClean">Pass in the string to remove the special characters from.</param>

/// <returns>A string based on the passed in string without special characters is returned.</returns>

public static string RemoveSpecialCharacters(string stringToClean)

    return stringToClean.Replace("!", " ").

        Replace("@", " ").

        Replace("#", " ").

        Replace("$", " ").

        Replace("%", " ").

        Replace("^", " ").

        Replace("&", " ").

        Replace("*", " ").

        Replace("(", " ").

        Replace(")", " ").

        Replace("_", "").

        Replace("+", "").

        Replace("-", "").

        Replace("=", "").

        Replace("`", "").

        Replace("~", "").

        Replace("<", "").

        Replace(">", "").

        Replace("?", "").

        Replace(":", "").

        Replace(@"\", "").

        Replace("{", "").

        Replace("}", "").

        Replace("|", "").

        Replace("[", "").

        Replace("]", "").

        Replace(";", "").

        Replace("'", "").

        Replace(",", "").

        Replace(".", "").

        Replace("/", "").

        Replace("'", "");

The second method is TrimAndRemoveSpecialCharacters(string stringToClean). Again, this method name describes exactly what the method does. The string passed in has the edges trimmed of whitespace and then executes the previous method to remove special characters.

/// <summary>

/// This method removes special characters and trims any white space from the left and right of the

/// string.

/// </summary>

/// <param name="stringToClean">Pass in the string to remove the special characters from.</param>

/// <returns>A string based on the passed in string without special characters and trimmed

/// of white space is returned.</returns>

public static string TrimAndRemoveSpecialCharacters(string stringToClean)

    RemoveSpecialCharacters(stringToClean);

    return stringToClean.Trim();

The third method, again with the theme of self describing method names, is IsValidAlphaNumeric(string verifyThis).

/// <summary>

/// This method takes a string as input and return a boolean value representing

/// if the string is or is not alphanumeric.

/// </summary>

/// <param name="verifyThis">Pass the string to verify if it is or is not alphanumeric.</param>

/// <returns>True is returned if alphanumeric, false if not.</returns>

public static bool IsValidAlphaNumeric(string verifyThis)

    if (String.IsNullOrEmpty(verifyThis))

        return false;

    for (int i = 0; i < verifyThis.Length; i++)

        if (!(Char.IsLetter(verifyThis[i])) && (!(Char.IsNumber(verifyThis[i]))))

            return false;

    return true;

The last method is one of the most needed of all, the IsNumeric(string verifyThis) method. Again, self explanatory.

/// <summary>

/// This method takes a string as input and returns a boolean value representing

/// if the string is numeric or not.

/// </summary>

/// <param name="verifyThis">Pass the string to verify if it is or is not numeric.</param>

/// <returns>True is returned if numeric, false if not.</returns>

public static bool IsNumeric(string verifyThis)

    verifyThis = verifyThis.Trim();

    var m = regex.Match(verifyThis);

    return !string.IsNullOrEmpty(m.Value);

That covers the string parsing utility file. More to come later ? stay tuned. The next few entries I’ll cover the ExcelHelper Class I’ve written that simplifies creation & retrieval of Excel Worksheets, and more.