Excel.Range to String conversion in C#

Question

Using .NET's Office interop libraries, does anybody know the best way to convert back and forth between strings (eg "A57", "$L$2:$M:$3") and corresponding objects of type Excel.Range?

Bonus points if it also works with "named ranges".

Answer 1

Use the Range property of a Worksheet object, and pass Type.Missing as the second parameter.

For example:

Range range = sheet.get_Range("$L$2:$M:$3", Type.Missing);

This also supports named ranges.

Answer 2

To get a string from a Range:

/// <summary>
/// Extensions to the Range class.
/// </summary>
public static class RangeExtensions
{
    /// <summary>
    /// Returns the range as a string indicating its address.
    /// </summary>
    /// <param name="range">The range to convert to a string.</param>
    /// <returns>A string indicating the range's address.</returns>
    public static string ToAddressString(this Range range)
    {
        return range.Address[true, true, XlReferenceStyle.xlA1, false, null];
    }
}

To get a Range from a string:

public class ExcelUtil
{
    /// <summary>
    /// Converts the given address string on the given sheet to a Range object.
    /// </summary>
    /// <param name="sheet">The worksheet.</param>
    /// <param name="addressString">The address string to convert.</param>
    /// <returns>The range.</returns>
    public static Range RangeFromAddresssString(Worksheet sheet, string addressString)
    {
        return sheet.Range[addressString];
    }
}

The second method might be a little gratuitous, but I prefer being crystal clear in my method names.

Answer 3

As SLaks said, you can get a range object from a string address with the worksheet's Range property like worksheet.Range["A3:C30"]. The second argument can be omitted in .NET 4.0. .get_Range() is equivalent to .Range[].

To go the other way, use the range objects's Address property like this: range.Address.