62

I have excel file with sheet1 that has a value I need to read on row 2 and column 10. Here is my code. 

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
var cell = (Excel.Range)excelWorksheet.Cells[10, 2];

After getting cell object which is Excel.Range, I do not know how to read the content of that cell. I tried converting it into array and looping over it and I tried converting to string array etc. I am sure it is very simple. Is there a direct way to get just one cell value that is string?

crthompson
  • 15,653
  • 6
  • 58
  • 80
DoodleKana
  • 2,106
  • 4
  • 27
  • 45

9 Answers9

91

You need to cast it to a string (not an array of string) since it's a single value.

var cellValue = (string)(excelWorksheet.Cells[10, 2] as Excel.Range).Value;
System Down
  • 6,192
  • 1
  • 30
  • 34
  • if I do it the way you mentioned I get error like this:Cannot cast expression of type 'Microsoft.Office.Interop.Excel.Range' to type string. – DoodleKana Sep 25 '13 at 15:27
  • 2
    I tried that approach you mentioned before but I was getting null always and in VS 2012 when I did .Value it did not come up. Value2 or Value[object] was coming up. So I thought what i was using was somehow wrong and decided to ask here. But I found out my row and column was mixed up through trail and error. It is Cells[row, column] like array. For some reason on the internet different posts said otherwise so that got me confused. – DoodleKana Sep 25 '13 at 21:02
  • I have `using Excel = Microsoft.Office.Interop.Excel` and when I try this solution I get the error `The type 'Range' exists in both 'Microsoft.Office.Interop.Excel, Version[...]' and 'myProject[...]'`. Any ideas on that? – jera Feb 22 '17 at 09:57
  • @jera - I changed `var cellValue = (string)(excelWorksheet.Cells[10, 2] as Excel.Range).Value;` To: `Excel.Range firstCell = excelWorkSheet.get_range("K2","K2"); var cellValue = firstCell.Value2;` Splitting it to two lines avoided that same error for me. – trapspring Apr 04 '17 at 18:44
  • 2
    `excelWorksheet.Cells[1, 1]` is 1 indexed...not 0. – P.Brian.Mackey Jun 28 '17 at 17:28
  • Thank You! After starting this project in net 4.0 discovered that the target was only up to net 3.5... After hours stumbled on this solution. Using net 3.5 with interop 12.0.0.0 in Visual Studio 2010 – Mike Sr Jan 24 '18 at 16:00
23
using Microsoft.Office.Interop.Excel;

string path = "C:\\Projects\\ExcelSingleValue\\Test.xlsx ";

Application excel = new Application();
Workbook wb = excel.Workbooks.Open(path);
Worksheet excelSheet = wb.ActiveSheet;

//Read the first cell
string test = excelSheet.Cells[1, 1].Value.ToString();

wb.Close();

This example used the 'Microsoft Excel 15.0 Object Library' but may be compatible with earlier versions of Interop and other libraries.

David Sopko
  • 5,263
  • 2
  • 38
  • 42
11
 //THIS IS WORKING CODE                        
 Microsoft.Office.Interop.Excel.Range Range_Number,r2;
 Range_Number = wsheet.UsedRange.Find("smth");
 string f_number="";

 r2 = wsheet.Cells;

 int n_c = Range_Number.Column;
 int n_r = Range_Number.Row;
 var number = ((Range)r2[n_r + 1, n_c]).Value;

 f_number = (string)number;
MadPointer
  • 111
  • 1
  • 4
  • 5
    Could you add a bit more explanation to your answer? We try to avoid answers that are just code :) An explanation is more likely to help future readers. – starsplusplus Mar 28 '14 at 16:35
  • Microsoft.Office.Interop.Excel.Range[i,j] returns object(and r2[n_r + 1, n_c] do so to), so we have to cast it to Range to get access to Range properties. Application do not now what is "Value" type, so we use variant(var) to handle this.And we again use cast to get string value. – MadPointer Apr 09 '14 at 13:29
  • 3
    You can [edit](http://stackoverflow.com/posts/22718076/edit) your answer to include that info. – starsplusplus Apr 09 '14 at 13:42
6

It is better to use .Value2() instead of .Value(). This is faster and gives the exact value in the cell. For certain type of data, truncation can be observed when .Value() is used.

Shilpa
  • 189
  • 1
  • 6
  • 1
    The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.value2?view=excel-pia – CodeCatia Jun 02 '21 at 14:26
4

Please try this. Maybe this could help you. It works for me.

string sValue = (range.Cells[_row, _column] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
//_row,_column your column & row number 
//eg: string sValue = (sheet.Cells[i + 9, 12] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
//sValue has your value
Marek S.
  • 108
  • 14
Udaayipp
  • 104
  • 3
4

You have two ways to get the value

using xl = Microsoft.Office.Interop.Excel;

// 1. Using Cells with numbered reference
string cellValue = (excelWorksheet.Cells[10, 2] as xl.Range).Text.ToString();

// 2. Using Range with address reference
string cellValue = excelWorksheet.Range["J2"].Text.ToString();
Chandraprakash
  • 773
  • 1
  • 10
  • 18
3

The issue with reading single Excel Cell in .Net comes from the fact, that the empty cell is evaluated to a Null. Thus, one cannot use its .Value or .Value2 properties, because an error shows up.

To return an empty string, when the cell is Null the Convert.ToString(Cell) can be used in the following way:

Excel.Workbook wkb = Open(excel, filePath);
Excel.Worksheet wk = (Excel.Worksheet)excel.Worksheets.get_Item(1);

for (int i = 1; i < 5; i++)
{
    string a = Convert.ToString(wk.Cells[i, 1].Value2);
    Console.WriteLine(a);
}
Vityata
  • 42,633
  • 8
  • 55
  • 100
1

You can run into a problem when reading cell values from an excel Worksheet. If the cell is empty .Value (or .Value2) will throw null. If you want to eliminate that problem, you need to check if the result is null. something like this:

string strcelltext = Convert.ToString(mySheet.get_Range("A1", "A1").Value2) ?? "";
CodeCatia
  • 127
  • 1
  • 9
0

Here's a solution that may work better in the case you are referencing objWorksheet.UsedRange.

Excel.Worksheet mySheet = ...(load a workbook, etc);
Excel.Range myRange = mySheet.UsedRange;
var values = (myRange.Value as Object[,]);
int rowNumber = 3, columnNumber = 5;
string cellValue = Convert.ToString(values[rowNumber, columnNumber]);
Newclique
  • 494
  • 3
  • 15