Reading Data from Excel in C# with Interop

Reading data from Excel worksheets is a common task when working with business data. Here are some ways to achieve this in C# using Excel Interop:

• Accessing specific cells and ranges
• Reading various data types (text, numbers, dates)
• Handling Excel formulas

In this article, we will illustrate how to perform these operations using the Microsoft.Office.Interop.Excel library in C#.

First, let's establish an Excel application instance and open a workbook:

Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Open("C:\\Path\\To\\Your\\File.xlsx");
Worksheet worksheet = workbook.Sheets[1]; // Access the first sheet

Now, we're set to start reading data.

Accessing Cells and Ranges

To access a specific cell:

Range singleCell = worksheet.Cells[1, 1]; // Access cell A1
object cellValue = singleCell.Value; // Get the cell's value

To access a range of cells:

Range range = worksheet.Range["A1", "B2"]; // Access the range A1:B2

Reading Various Data Types

You can read various data types from Excel cells:

// Assuming cell A1 contains a string
string text = worksheet.Cells[1, 1].Value.ToString();
// Assuming cell B1 contains a number
double number = double.Parse(worksheet.Cells[1, 2].Value.ToString());

// Assuming cell C1 contains a date
DateTime date = DateTime.Parse(worksheet.Cells[1, 3].Value.ToString());

Handling Excel Formulas

You can also handle cells with formulas:

// Assuming cell D1 contains a formula
string formula = worksheet.Cells[1, 4].Formula; // Get the formula
object formulaResult = worksheet.Cells[1, 4].Value; // Get the result of the formula

Finally, remember to clean up by closing the workbook and quitting the Excel application:

workbook.Close(false);
excelApp.Quit();

By understanding these fundamentals, you can begin to automate the process of reading and handling data from Excel worksheets in your C# applications.