0

I am writing a C# application that reads data from an Excel file. Everything was running smoothly until I attempted to read from a cell that used a formula.

I am pulling data from the sheet and trying to add the cumulative quantity, so in a loop, I'm using: 

cntr = Cell(row, column);

NOTE: I'm paraphrasing rather than copy my actual code.

Anyways, if the actual cell value contains a number, this works, but if the cell contains a function, it returns the string

"=SUM(A1:A5)"

and I'm not sure how I can execute this in my C# code to retrieve the actual value of that cell.

Complexity
  • 5,682
  • 6
  • 41
  • 84
AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • 1
    As a side note: Interop is really slow and error-prone, you might want to use a library like EPPLUS, this is ages faster than interop – Christian Sauer Jul 03 '14 at 13:56
  • I'll definitely look into that, likely for another project since I'm already so deep into this one. Thanks for the suggestion. – AdamMc331 Jul 03 '14 at 14:14

1 Answers1

2

Try

Cell(a,b).Value

instead of just Cell(a,b).

Also, the following approach should work

Excel.Range objRange = (Excel.Range)objSheet.Cells[rowN,colN];
variableName = objRange.get_Value(System.Missing.Type).ToString();

You may modify it for your datatype

hnk
  • 2,216
  • 1
  • 13
  • 18
  • I tried that approach, but for some reason I'm getting the error that the object does not contain a definition for 'Value'. Perhaps my interop file is wrong? I'm not sure what it's missing, or perhaps I really am missing an assembly reference. – AdamMc331 Jul 03 '14 at 13:53
  • Also, you might want to have a look at this solution posted on SO earlier. Seems to be a similar problem. http://stackoverflow.com/questions/5714724/c-getting-a-cells-value-with-excel-interop – hnk Jul 03 '14 at 14:01
  • Same issue. I'm going to look over my interop file again and see if I am missing something. – AdamMc331 Jul 03 '14 at 14:03
  • 1
    Yup, lemme know if the new edit I posted works. If so that's great, else I'll delete this answer as it doesn't deserve +1 rep if it's not working. – hnk Jul 03 '14 at 14:06
  • 1
    It did. I was making a stupid mistake with my assembly references. I was using the namespace Microsoft.Office.Interop.Excel and had no idea I could change it to say 'using Excel=Microsoft.Office.Interop.Excel'. At first I received the error Excel was not defined. The way I wrote it: variableName = (Cell(row, col) as Excel.Range).Value; Thanks for your help! – AdamMc331 Jul 03 '14 at 14:13