Excel Helper Utilities Library Part Three

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;
}

.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; }

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);
}

.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; }

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 kick it on DotNetKicks.com

6 thoughts on “Excel Helper Utilities Library Part Three

  1. When I originally commented I clicked the "Notify me when new comments are added" checkbox and now each time a comment is added I get four emails with the same comment.
    Is there any way you can remove me from that service?
    Thanks!

  2. Hey – nice blog, just looking around some blogs, seems a pretty nice platform you are using. I’m currently using WordPress for a few of my sites but looking to change one of them over to a platform similar to yours as a trial run. Anything in particular you would recommend about it?

Comments are closed.