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; // 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:
By understanding these fundamentals, you can begin to automate the process of reading and handling data from Excel worksheets in your C# applications.