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.

EDITED

Answer 2

This doesn't answer your question directly but may provide you with an answer...

As I discuss on my blog entitled, Tribal Knowledge: Working with Office Interops, one way to divine the inner workings of any office document and how to manage it via the interops is to record a macro of the process needed. Once done examine the vba code, it will show settings changes and other items of interest that can lead the way through the tribal knowledge of the interops. Most the object calls are the same under the covers...good luck.

Answer 3

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