Introduction
Excel is one of the most widely used spreadsheet programs, and it's no surprise that many businesses and organizations use it to store and manage data. As a .NET developer, you may find yourself in a situation to work with Excel files in your application.
In this blog post, we'll explore the library "EPPlus" using which you can work with Excel files in C#.NET.
What is EPPLus?
EPPlus is a .NET Framework/.NET Core library for managing Office Open XML spreadsheets, distributed via NuGet.
Version 5 of this library supports .NET Framework (3.5 or higher) and also the .NET Core (2.0 or higher). It is currently a single DLL and has no dependencies on any other library such as Microsoft Excel Interop DLLs. Due to this, it is very easy to use this library.
You can read more on EPPlus here.
How to use EPPlus?
Now that we have seen what EPPlus is, let's see the step-by-step guide to learn how to use it in any .NET Application.
Step 1 - Create a new Application
For the purpose of this blog, I will be showing a demo of this library with the Console application. But you can use this library with any other type of .NET Application with the same steps.
Let's create a new Console Application.
Step 2 - Add NuGet Reference to the Package EPPlus
Next, we will open the NuGet Package manager of the application and search for the package "EPPlus" and install it.
You can also install the EPPlus package by running the below command in Package Manager Console.
PM> Install-Package EPPlus
Step 3 - Configure and Initialize EPPlus
Due to EPPlus licensing terms, before we can use EPPlus, we need to set the property ExcelPackage.LicenseContext.
This can have two different values: NonCommercial if you use EPPlus for noncommercial purposes or Commercial if you have a commercial license.
If the LicenseContext is not set EPPlus will throw a LicenseException, this happens only when a debugger is attached.
There are multiple options you can set the license information for EPPlus.
-
Via Code
-
Via appSettings.json
-
Via App.Config
For this tutorial, we will set it via code.
Let's create a new class in the project called ExcelHelper and add the below code to it.
using OfficeOpenXml;
namespace ReadWriteExcelDemo
{
public class ExcelHelper
{
public ExcelHelper()
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
}
}
}
We will be doing all our excel operations through this class. As you can see above, we have provided the license information in the constructor of the class. Now we are ready to perform excel operations. So, let's explore different excel operations we can perform with EPPlus.
Handling Excel Files
For the tutorial purpose, let's create a small excel file that will contain a list of Employees having Id, FirstName, LastName, and mobile number and save this file in your local machine. I named it Employees.xlsx.
In the below sections, we will use this excel file to demonstrate reading from excel and writing to excel workflows using C#.NET and EPPlus.
Reading Excel data
First, we will see how to read the data from excel. For this let's create a new class named Employee which will be used to represent employees added as a row in the above excel.
namespace ReadWriteExcelDemo
{
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string MobileNumber { get; set; }
}
}
Now let's create a function named ReadEmployeeDataFromExcel in the class ExcelHelper and add the below code to it.
public IList<Employee> ReadEmployeeDataFromExcel(string filePath)
{
FileInfo fileInfo = new FileInfo(filePath);
var employees = new List<Employee>();
using (var package = new ExcelPackage(fileInfo))
{
//get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int colCount = worksheet.Dimension.End.Column; //get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
//We will start from row 2 since row 1 is a header row.
for (int row = 2; row <= rowCount; row++)
{
employees.Add(new Employee()
{
Id = Convert.ToInt32(worksheet.Cells[$"A{row}"].Value),
FirstName = worksheet.Cells[$"B{row}"].Value?.ToString(),
LastName = worksheet.Cells[$"C{row}"].Value?.ToString(),
MobileNumber = worksheet.Cells[$"D{row}"].Value?.ToString()
});
}
}
return employees;
}
Let's see what we have done in this function. First We have loaded the excel file using ExcelPackage class of EPPlus. We have initialized it in the using block so that the instance is automatically disposed of after the use.
Now let's call this method from the program.cs and try to print the employee information on the console. I am using .NET 6, so this code will directly go into the program.cs file. If you are using an older version, please add it to the main function.
var excelHelper = new ExcelHelper();
var employees = excelHelper.ReadEmployeeDataFromExcel("C:\\TestData\\Employees.xlsx");
foreach (var employee in employees)
{
Console.WriteLine($"Id: {employee.Id}, FirstName: {employee.FirstName}, LastName: {employee.LastName}, Mobile: {employee.MobileNumber}");
}
Console.Read();
Now run your code, and the employee data will be displayed on the console.
Writing Data In Excel
Let's see now how to write data in excel. We will use the same excel file and try to add a new employee row in the above excel.
Add a new function AddEmployeeData in the class ExcelHelper and add the below code to it.
public void AddEmployeeData(string filePath, Employee employee)
{
FileInfo fileInfo = new FileInfo(filePath);
using (var package = new ExcelPackage(fileInfo))
{
//get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
int colCount = worksheet.Dimension.End.Column;//get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
//Add new data to the excel row
worksheet.Cells[$"A{rowCount + 1}"].Value = employee.Id;
worksheet.Cells[$"B{rowCount + 1}"].Value = employee.FirstName;
worksheet.Cells[$"C{rowCount + 1}"].Value = employee.LastName;
worksheet.Cells[$"D{rowCount + 1}"].Value = employee.MobileNumber;
package.Save();
}
}
In this function, we have added the code to dump the data from the employee object to excel as a new row.
Now let's call this write function in the main function. Update program.cs with the below code.
using ReadWriteExcelDemo;
var excelHelper = new ExcelHelper();
Console.WriteLine("Writing new employee");
//Add new employee
excelHelper.AddEmployeeData("C:\\TestData\\Employees.xlsx", new Employee()
{
Id = 5,
FirstName = "Suresh",
LastName = "Raina",
MobileNumber = "91-1111111115"
});
Console.WriteLine("Employee Data Saved Successfully!");
Console.WriteLine();
Console.WriteLine("Reading Employee Data");
var employees = excelHelper.ReadEmployeeDataFromExcel("C:\\TestData\\Employees.xlsx");
foreach (var employee in employees)
{
Console.WriteLine($"Id: {employee.Id}, FirstName: {employee.FirstName}, LastName: {employee.LastName}, Mobile: {employee.MobileNumber}");
}
Console.Read();
As you can see above, we have first added new employee data by calling the function AddEmployeeData of ExcelHelper class. And then I retrieved the updated employee list by calling the function ReadEmployeeDataFromExcel of the ExcelHelper and printed that data on the console.
Now Run your application and you should be able to see the new employee data.
You can also see the excel file updated with the new record.
Conclusion
This is a very basic example that we saw using which you read and write the data in excel. But you can definitely do more with this. You can find the entire code for this tutorial on GitHub here.
Thank you for taking the time to read this blog post. We hope that you found the information provided in the blog to be helpful and informative. For similar content, please check out our other blogs.
We appreciate your support and feedback, and we would love to hear from you if you have any questions or comments about the blog. If you have any specific topic you want us to cover in the future, please feel free to let us know.
Once again, thank you for reading our blog, and we look forward to your continued support.