Working with C# and Excel has been discussed on this site before with tips on preventing Excel.exe from hanging and converting integers into Excel column headings. Today's post is a very brief example on how we can extract data from specific cells of an Excel worksheet.
Obtaining a value from a specific cell
Assuming we have a value in cell B16 that we wish to have out as a string in our C# code, we can use the following code:
String myString = ((Range)worksheet.Cells[16, 2]).Value2.ToString();
- Range comes from the Excel interop namespace ('Microsoft.Office.Interop')
- worksheet refers to the Excel worksheet you're working on (see example below)
Obtaining a range of values from a range of cells
Assuming we have values in the range B16 to C20 inclusive, we can create a new range to obtain the values from into an array of objects, like so:
Range range = worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[20, 3]];
myObjects = range.Value;
Once you have the array of objects you can then cast them to other data types in the usual manner.
Here's a quick example of setting up an Excel application, opening a worksheet and obtaining a value using both of the above methods.
Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Open(@"C:\MyWorkbook.xlsx");
Worksheet worksheet = workbook.ActiveSheet;
String b16 = ((Range)worksheet.Cells[16, 2]).Value2.ToString();
object[,] objects = ((Range)worksheet.Range[worksheet.Cells[16, 2], worksheet.Cells[20, 3]]).Value;