retrieve single cell value in Excel automation function with c#

Question

i need to retrieve a single cell instead of Excel.Range in my method.

using Excel = Microsoft.Office.Interop.Excel;
...
public object vReadNumber(Excel.Range targetRange)
{
    ...
    Doing Something With targetRange
    ...
}

any idea?

Answer 1

The Excel.Range.Count property reports how many cells are in the range. For example:

if (targetRange.Count > 1) 
{
   throw new ArgumentException(...);
}

The Excel.Range.Cells[,] indexer returns any cells from the first area of the range. But note that Excel uses base 1 indexing (not base 0). So to access the top-left cell of the range, you could use:

Excel.Range topLeftCell = (Excel.Range)targetRange.Cells[1,1];

Note that you have to cast to Excel.Range in the above because the Excel.Range.Cells[,] indexer returns an Excel.Range object cast as System.Object.

You should also beware that the Excel.Range.Value property will return an atomic value such as a double, string, boolean, etc. when the range consists of a single cell. It will return a two-dimensional base 1 array, however, if the first area of the range consists of more than one cell. For example:

if (targetRange.Count = 1) 
{
   object myValue = targetRange.get_Value(Type.Missing);
   MessageBox.Show(myValue.ToString());
}
else
{
    object[,] myArray = targetRange.get_Value(Type.Missing);
    for(int row = 1; row <= myValue.GetLength(0); row++)
    {
        for(int column = 1; column <= myValue.GetLength(1); column++)
        {
            MessageBox.Show(myArray[row, column].ToString());
        }
    }
}

Hope this helps!

Mike