Excel.Range to String conversion in C#

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 w开发者_C百科orks with "named ranges".

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.

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.

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.

If what you are trying to get is the actual contents of the cell, use the Value2 property. Here's some code that examines the cell value type and does different things accordingly.

Excel.Range cell = (Excel.Range)sheet.UsedRange[row, col];
if (cell.Value2 != null)
{
    switch (Type.GetTypeCode(cell.Value2.GetType()))
    {
        case TypeCode.String:
            string formula = cell.Value2;
            break;
        case TypeCode.Double:
            double amt = (Double)cell.Value2;
            break;
    }
}

cell.Value2 = amt + someotheramt;

HTH