Manipulating Excel files in .NET applications is a common need, whether for generating reports, importing or exporting data, or for integration with other systems.
With the EPPlus library, it is possible to create, read, and modify Excel files efficiently.
In this article, we will explore how to use EPPlus to manipulate Excel files in C# in a simple and effective way.
What is EPPlus?
EPPlus is a .NET library that allows you to create, read, and modify Excel files without the need to have Excel installed.
It is widely used for tasks such as report generation and automation of data manipulation in Excel spreadsheets.
Installing EPPlus
To start using EPPlus in your C# project, you can install the package via CLI or NuGet:
.NET CLI:
dotnet add package EPPlus
Package Manager Console:
Install-Package EPPlus
EPPlus offers an API with many features for working with Excel spreadsheets, in addition to being efficient and easy to integrate.
For more details on how to use the library, you can access the official documentation.
Reading Spreadsheets: Accessing Cells, Rows, and Columns
Now we will perform the manipulation of an Excel file:
using OfficeOpenXml;
using System.IO;
ExcelPackage.License.SetNonCommercialOrganization("Next Wave Education");
var fileInfo = new FileInfo("C:/Local/File.xlsx");
using (var package = new ExcelPackage(fileInfo))
{
var worksheet = package.Workbook.Worksheets[0];
var value = worksheet.Cells[1, 1].Text;
Console.WriteLine(valor);
}
At the beginning of the code, we define the type of license that will be used.
Since we are using the free (non-commercial) version, we call the “SetNonCommercialOrganization” method to define the license as non-commercial and associate it with the organization “Next Wave Education”. This step is mandatory.
After that, we create an instance of “FileInfo”, where we pass the path of the Excel file we want to manipulate.
Then, we open the file and access the first sheet of our document (Worksheets[0]).
In Excel, this information is at the bottom.
To access a specific cell, we use the property “Cells[Column, Row]”, where the first value refers to the column (in numeric format) and the second value refers to the row.
For example, Cells[1, 1] refers to cell A1.
To capture the value of the cell as text, we use the property “.Text”, which converts the cell content to a string.
This value is then stored in the variable “valor”.
Writing Data: Creating and Saving Excel Files
Now we will learn how to create a new spreadsheet, add data, and save the Excel file.
using (var package = new ExcelPackage(fileInfo))
{
var worksheet = package.Workbook.Worksheets.Add("People");
worksheet.Cells[1, 1].Value = "Name";
worksheet.Cells[1, 2].Value = "Age";
worksheet.Cells[2, 1].Value = "John";
worksheet.Cells[2, 2].Value = 30;
worksheet.Cells[3, 1].Value = "Mary";
worksheet.Cells[3, 2].Value = 25;
package.Save();
}
We create a new spreadsheet using the method “Worksheets.Add(“People”)”, where we pass the name of the new spreadsheet.
This spreadsheet will be created inside the Excel file. The new spreadsheet will be added to the Excel file and will be accessed through the “worksheet” variable.
To add data to the spreadsheet, we use the property “Cells[Column, Row]”.
In this example, we use “Value” to indicate that we want to add a value to that cell.
Above, we are filling in the headers in cells A1 and B1, and the data in cells A2, B2, A3, and B3.
After adding the data, it is necessary to call the “Save()” method to save the changes made to the Excel file.
Advanced Manipulation: Styles, Formulas, and Validations
Now that we have learned how to perform basic manipulations in Excel files, let’s explore some more advanced features that EPPlus offers.
using (var package = new ExcelPackage(fileInfo))
{
var worksheet = package.Workbook.Worksheets[1];
var headerRange = worksheet.Cells[1, 1, 1, 2];
headerRange.Style.Font.Bold = true;
headerRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
headerRange.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
worksheet.Cells[5, 2].Formula = "SUM(B2,B3)";
package.Save();
}
In the code above, the spreadsheet we are manipulating is accessed through the property “Worksheets[1]”, which gives us access to the “People” spreadsheet we created earlier.
After that, we use “Cells[startRow, startColumn, endRow, endColumn]” to identify a range of cells.
In this case, we are selecting a range that goes from cell A1 (1, 1) to cell B1 (1, 2).
After identifying the range of cells, we apply a bold font style to the selected cells.
Then, we define the background fill of the selected cells.
Here, we are doing two things:
PatternType = ExcelFillStyle.Solid: this defines that the fill will be solid, that is, a uniform color, without gradients or patterns.BackgroundColor.SetColor(Color.Yellow): this defines the background color of the selected cells as yellow.
EPPlus also allows you to add formulas in the cells. In the example, we are inserting the formula “SUM(B2,B3)” in cell B5 (5, 2), which calculates the sum of the values from cell B2 and B3.
To add the formula, we use the property “.Formula”. This formula will be evaluated automatically when the spreadsheet is opened in Excel.
Best Practices for Performance and Safe Handling of Large Files
When working with large files, it is important to ensure that your application is efficient and does not excessively consume memory resources.
Here are some tips to improve performance:
- Avoid unnecessary reading of entire spreadsheets: When reading a file, avoid loading entire spreadsheets if you only need a part of the file.
- Use indexes to access cells efficiently.
- Close files appropriately: Make sure to always close files correctly after reading or writing, using the “using” block to ensure that resources are released.
Conclusion
EPPlus is an excellent choice for working with Excel files in C#, especially when looking for an efficient solution without depending on Excel being installed.
The library offers a rich and simple API, which covers a wide range of scenarios, from reading and writing data to advanced manipulation of spreadsheets.