CODEWITHSUNDEEP
c#.netexcelautomationms-office
syntaxcheck
syntaxcheck

Reputation: 136

retrieve single cell value in Excel automation function with c#

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?

Upvotes: 0

Views: 2965

Answers (2)

Ben
Ben

Reputation: 31

// n.b. cell is derived from a WorkSheet object xlSheet
// by cell = xlSheet.Cells[row, col]

public T CellValue<T>(object cell)
{
    T result = default(T);
    try
    {
        Range rg = (Range)cell;
        object value = rg.Value2;
        if (value != null)
        {
            if (typeof(T) == typeof(DateTime))
            {
                DateTime dateValue;
                if (value is double)
                {
                    dateValue = DateTime.FromOADate((double)value);
                }
                else
                {
                    DateTime.TryParse((string)value, out dateValue);
                }
                result = (T)Convert.ChangeType(dateValue, typeof(T));
            }
            else
                result = (T)Convert.ChangeType(value, typeof(T));
        }
    }
    catch
    {
        result = default(T);
    }
    return result;
}

Upvotes: 3

Mike Rosenblum
Mike Rosenblum

Reputation: 12157

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

Upvotes: 3

Related Questions