CODEWITHSUNDEEP
c#excelvsto
Behrooz Karjoo
Behrooz Karjoo

Reputation: 4292

How to best fetch a cell value from excel using VSTO?

I am trying to get cells from excel into csharp but not sure what's the best variable type to read it into.

If I make the variable a string and the cell value is a double I get a parse error. If I make the variable double then when the cell is a string it wont work.

Here's the code I am running:

 try
 {
        string i = Globals.Sheet1.Cells[7, 7].Value;
        double num;
        if (i == null) return;

        if (double.TryParse(i, out num)) 
        {
             ...
        }
}
catch (Exception e)
{
       MessageBox.Show(e.ToString());
}

Upvotes: 4

Views: 10584

Answers (5)

ΩmegaMan
ΩmegaMan

Reputation: 31606

I prefer to get the text value directly and do not want to deal with the underlying datatype for the most part. I get the text value as TravisWhidden comment mentioned, here is my VSTO C# code to achieve a read from a cell and return a text value regardless of the base object.

This my extension method which works off of the Worksheet page:

using Excel = Microsoft.Office.Interop.Excel;

...

public static string CellGetStringValue(this Excel.Worksheet theSheet,
                                        int row, int column)
{
    var result = string.Empty;

    if (theSheet != null)
    {
        var rng = theSheet.Cells[row, column] as Excel.Range;
                
        if (rng != null)
            result = (string) rng.Text;
    }

    return result;
}

Same but using null propagation and pattern matching

public static string CellGetStringValue(this Excel.Worksheet theSheet, 
                                        int row, int column)
{
    var result = string.Empty;

    if (theSheet?.Cells[row, column] is Excel.Range rng)
         result = (string)rng.Text;

    return result;
}

Upvotes: 1

menxin
menxin

Reputation: 2244

 try
 {

    dynamic mycell = Globals.Sheet1.Cells[7, 7];
    double num;
    if (mycell.Value == null) return; //you can use mycell.Text too.

    if (double.TryParse(mycell.Text, out num)) 
    {
          .
          .
          .
    }
}
catch (Exception e)
{
       MessageBox.Show(e.ToString());
}

Upvotes: 1

Alexandre C.
Alexandre C.

Reputation: 56956

Simply

double x = (double)Globals.Sheet1.Cells[7, 7].Value;

will get you the double value or throw an exception (no need to catch it, it will be displayed properly in a dialog box in Excel if nothing catches it upstream).

Upvotes: 0

cordialgerm
cordialgerm

Reputation: 8503

You could just call ToString() on the object and then do double.TryParse() to see if the value is numeric or text

Upvotes: 0

Will Marcouiller
Will Marcouiller

Reputation: 24132

Make it an object, then find out the right type after you have gotten the value out of the cell.

I don't know about VSTO, but in the Excel Interop assembly, there were a Value2 and a Text property which both returned object, and could be casted through polymorphism to the correct type. Doesn't VSTO provide those?

Upvotes: 4

Related Questions