Reading Data from Excel Worksheets in C# with EPPlus

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

  • 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 EPPlus library in C#.

First, we need to load the Excel file into an ExcelPackage instance:

using ExcelPackage package = new ExcelPackage(new FileInfo("C:\\Path\\To\\Your\\File.xlsx"));
ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // Access the first sheet

Accessing Cells and Ranges

To access a specific cell:

var singleCell = worksheet.Cells["A1"]; // Access cell A1
var cellValue = singleCell.Value; // Get the cell's value

To access a range of cells:

var range = worksheet.Cells["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["A1"].Value.ToString();
// Assuming cell B1 contains a number
double number = double.Parse(worksheet.Cells["B1"].Value.ToString());

// Assuming cell C1 contains a date
DateTime date = DateTime.FromOADate(double.Parse(worksheet.Cells["C1"].Value.ToString()));

Handling Excel Formulas

You can also handle cells with formulas:

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

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