Working with Excel files in .Net

Using Excel files in software applications is common for several reasons, as they provide a practical and versatile solution for managing, analyzing, and sharing data.?

There are many reason that you may want to add Excel manipulation capabilities in your software among them:

• Excel is a universal format: Almost every organization uses Excel, and it is compatible with other office software like Google Sheets and LibreOffice. This makes Excel files a common medium for data exchange between businesses, departments, and teams.
• Widespread Knowledge: Most users are already familiar with Excel, reducing the learning curve when dealing with data-related tasks. Using Excel as a data format makes it easier for non-technical users to view, manipulate, and analyze data.
• Rich Features for Data Management: Excel supports a tabular structure, formulas and calculations and graphs and charts.

In this article I would like to talk about the available packages in .Net to work with Excel and I will briefly explain one of them.

You can find the complete source of the example in my GitHub in the link bellow:

https://github.com/amirdoosti6060/WorkingWithExcelFiles

Available packages and libraries to work with Excel

Here is a list of some packages and libraries for working with Excel files in .Net that I know.?

Open-Source packages

NPOI:

• License: Apache License 2.0 (Open Source)
• Overview: NPOI is a .NET library that allows you to read and write Microsoft Office formats, including Excel (.xls and .xlsx), Word, and PowerPoint. It is a port of the Apache POI project, which is Java-based.
• Installation: NuGet package manager
• Key Features:
• Pros:
• Cons:

ClosedXML:

• License: MIT License (Open Source)
• Overview: ClosedXML is a .NET library that makes working with Excel files simpler than NPOI. It’s an abstraction over OpenXml for handling .xlsx files.
• Installation: NuGet package manager
• Some Features:
• Pros:
• Cons:

ExcelDataReader (Read-Only):

• License: MIT License (Open Source)
• Overview: This library is specifically for reading Excel files. It supports both .xls and .xlsx formats and is optimized for reading large datasets efficiently.
• Installation: NuGet package manager
• Some Features:
• Pros:
• Cons:

Commercial Libraries (For Enterprise Use)

Aspose.Cells:

• License: Commercial (Paid)
• Overview: Aspose.Cells is a highly advanced library for working with Excel files. It supports a wide range of features, including very large files, charts, pivot tables, and more. It’s one of the most feature-complete Excel libraries for .NET.
• Installation: NuGet package manager
• Some Features:
• Pros:
• Cons:

Syncfusion XlsIO:

• License: Commercial (Free for small companies and individual developers through the community license).
• Overview: Syncfusion’s Excel library is another robust solution for working with Excel files in .NET. It can handle very large files, supports advanced Excel features, and is optimized for performance.
• Installation: NuGet package manager
• Some Features:
• Pros:
• Cons:

EPPlus:

• License: Since version 5 EPPLus has dual license mode: Noncommercial License and commercial License?
• Overview: EPPlus is a popular, high-performance library for reading and writing Excel files (both .xlsx and .xlsm formats) in .NET applications. It is widely used because it simplifies Excel manipulation without the need for Microsoft Office to be installed. EPPlus works with Excel’s OpenXML standard and provides a comprehensive API to manage Excel documents programmatically.
• Installation: NuGet package manager
• Some Features: Support Chart creation, Pivot table, Data Validation, Formula calculation, Excel styling and formatting and Worksheet protection
• Pros:
• Cons:

For this article I’ve chosen EPPlus and I would like to have a close look to it.

EPPLus

As a developer using the EPPlus library, which is primarily used for reading from and writing to Excel files, you should understand its core concepts, key classes, and common use cases. EPPlus simplifies working with Excel documents in C# and .NET by providing a robust API for managing spreadsheets without requiring Excel to be installed on the system.

Main points about EPPlus:

1. File Format Support: EPPlus works with Excel files in the .xlsx format (Excel 2007 and newer). It does not support the older .xls format natively.
2. Memory Management: EPPlus loads the entire Excel file into memory. If dealing with large files, be mindful of the memory footprint.
3. Thread Safety: EPPlus is not thread-safe, so avoid sharing ExcelPackage objects across threads.

Core Classes and Components in EPPlus:

1. ExcelPackage:

• This is the main entry point for working with Excel files. It represents an Excel workbook and provides access to its worksheets, properties, and content.
• Usage: Reading from or writing to an Excel file, handling multiple worksheets, saving the document, etc.

using (var package = new ExcelPackage(new FileInfo("path-to-file.xlsx")))
{
    var worksheet = package.Workbook.Worksheets[0]; // Access first worksheet

    // Work with data...
    package.Save(); // Save the changes
}        

2. ExcelWorkbook:

• Represents the Excel workbook (collection of worksheets) in the ExcelPackage.
• Accessed through the Workbook property of the ExcelPackage class.

ExcelWorkbook workbook = package.Workbook;        

3. ExcelWorksheet:

• Represents a single worksheet in the Excel workbook. You can access worksheets by name or index, and it provides methods to read and write data.
• You can create new worksheets or modify existing ones.

var worksheet = package.Workbook.Worksheets.Add("NewSheet");
worksheet.Cells[1, 1].Value = "Hello, World!";        

4. ExcelRange:

• Represents a range of cells within a worksheet. This is where most of your work with cell values and formulas happens.
• You can get or set values, styles, and formulas for single cells or ranges of cells.

worksheet.Cells["A1"].Value = "EPPlus";
worksheet.Cells[2, 1].Value = DateTime.Now;        

5. ExcelCell:

• Each cell in the ExcelRange is an ExcelCell. The Value, Formula, and other properties can be set to manipulate cell data.

6. ExcelTable:

• EPPlus supports creating and manipulating Excel tables, which is useful for handling structured data.
• You can define a table from a range and manage styles, filters, and formulas.

var range = worksheet.Cells["A1:B10"];
worksheet.Tables.Add(range, "MyTable");        

7. ExcelNamedRange:

• Represents a named range within a workbook. Named ranges are useful for referring to specific ranges without hardcoding cell addresses.

var namedRange = package.Workbook.Names.Add("MyRange", worksheet.Cells["A1:A10"]);        

8. ExcelChart:

• EPPlus supports creating various types of charts (e.g., line, bar, pie) directly within an Excel sheet.
• Charts are created from data ranges and can be customized with different styles and settings.

var chart = worksheet.Drawings.AddChart("SalesChart", eChartType.ColumnClustered);
chart.SetPosition(5, 0, 5, 0); // Position of the chart
chart.Series.Add(worksheet.Cells["B2:B5"], worksheet.Cells["A2:A5"]); // Data for chart        

9. ExcelStyles:

• EPPlus provides extensive support for Excel styles (fonts, colors, borders, etc.). You can apply styles to individual cells, rows, or columns.

worksheet.Cells["A1"].Style.Font.Bold = true;
worksheet.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A1"].Style.Fill.BackgroundColor.SetColor(Color.LightBlue);        

10. ExcelComment:

• You can add comments to cells, which appear in Excel as notes or annotations.

worksheet.Cells["A1"].AddComment("This is a comment", "AuthorName");        

Additional Features of EPPlus:

1. Formulas:

• EPPlus supports writing Excel formulas in cells, just as you would in Excel itself.

worksheet.Cells["A3"].Formula = "SUM(A1:A2)";        

2. Data Validation:

• EPPlus allows you to set data validation rules on cells, such as restricting input to certain types or ranges of values.

var validation = worksheet.DataValidations.AddListValidation("A1");
validation.Formula.Values.Add("Option 1");
validation.Formula.Values.Add("Option 2");        

3. Conditional Formatting:

• You can apply conditional formatting rules to cells, such as changing the background color based on cell value.

var condition = worksheet.ConditionalFormatting.AddThreeColorScale(worksheet.Cells["A1:A10"]);        

4. Pivot Tables:

• EPPlus has support for creating and managing pivot tables, which are useful for summarizing and analyzing large datasets.

var pivotTable = worksheet.PivotTables.Add(worksheet.Cells["D3"], worksheet.Cells["A1:B10"], "PivotTable");        

5. Encryption and Protection:

• EPPlus supports Excel file encryption, password protection, and worksheet protection.

package.SaveAs(new FileInfo("encrypted.xlsx"), "password");        

Example

In the following sample, I tried to show you some of the features of EPPlus like reading, writing, styling, creating a chart, working with formulas and managing worksheets. You can find complete code in my github.

Before running this example, ensure you have installed the EPPlus NuGet package in your project:

111Install-Package EPPlus        

Code:

using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System.Drawing;

Console.WriteLine("Working with Excel file ...");

// License requirement for EPPlus 5 and above
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

// Define the file path for saving the Excel file
var filePath = new FileInfo("EPPlusExample.xlsx");

if (File.Exists(filePath.FullName))
     File.Delete(filePath.FullName);

using (var package = new ExcelPackage(filePath))
{
     Console.WriteLine("Creating Excel File ...");

     // Create a new worksheet
     var worksheet = package.Workbook.Worksheets.Add("Employee Data");

     // Add some headers
     worksheet.Cells["A1"].Value = "ID";

     worksheet.Cells["B1"].Value = "Name";
     worksheet.Cells["C1"].Value = "Position";
     worksheet.Cells["D1"].Value = "Salary";
     worksheet.Cells["E1"].Value = "Tax";
     worksheet.Cells["F1"].Value = "Net Salary";

     // Apply some styling to the headers
     using (var range = worksheet.Cells["A1:F1"])
     {
          range.Style.Font.Bold = true;
          range.Style.Fill.PatternType = ExcelFillStyle.Solid;
          range.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
          range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
          range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
     }

     // Add employee data
     var employees = new[]
     {
             new { Id = 1, Name = "John Doe", Position = "Manager", Salary = 5500 },
             new { Id = 2, Name = "Jane Smith", Position = "Engineer", Salary = 4500 },
             new { Id = 3, Name = "Sam Brown", Position = "Technician", Salary = 3000 }
     };

     int row = 2;

     foreach (var employee in employees)
     {
          worksheet.Cells[row, 1].Value = employee.Id;
          worksheet.Cells[row, 2].Value = employee.Name;
          worksheet.Cells[row, 3].Value = employee.Position;
          worksheet.Cells[row, 4].Value = employee.Salary;

          // Add a formula for tax (10% of salary) and net salary
          worksheet.Cells[row, 5].Formula = $"D{row}*0.1";
          worksheet.Cells[row, 6].Formula = $"D{row}-E{row}";
          row++;
     }

     // Apply some currency format to salary, tax, and net salary columns
     using (var range = worksheet.Cells["D2:F4"])
     {
          range.Style.Numberformat.Format = "$#,##0.00";
          range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
     }

     // AutoFit columns for better display
     worksheet.Cells.AutoFitColumns();

     // Add a chart to represent salary data
     var chart = worksheet.Drawings.AddChart("SalaryChart", eChartType.ColumnClustered);

     chart.Title.Text = "Employee Salary Data";
     chart.SetPosition(6, 0, 1, 0);
     chart.SetSize(600, 300);

     // Add series to the chart
     var series = chart.Series.Add(worksheet.Cells["D2:D4"], worksheet.Cells["B2:B4"]);

     series.Header = "Salary";
     // Save the workbook

     package.Save();
     Console.WriteLine("Excel file created successfully!");
     Console.WriteLine($"File Path: {filePath.FullName}");
}

// Read the data back from the file
using (var package = new ExcelPackage(filePath))
{
     var worksheet = package.Workbook.Worksheets["Employee Data"];

     Console.WriteLine("Reading from the Excel file...");

     for (int row = 2; row <= 4; row++)
     {
          var id = worksheet.Cells[row, 1].Text;
          var name = worksheet.Cells[row, 2].Text;
          var position = worksheet.Cells[row, 3].Text;
          var salary = worksheet.Cells[row, 4].Text;

          Console.WriteLine($"ID: {id}, Name: {name}, Position: {position}, Salary: {salary}");
     }
}        

Conclusion

As a developer, being able to work with Excel files from within your program can be very useful and reassuring. EPPlus offers a wide range of functionality for working with Excel files programmatically in C#. Understanding its core classes like ExcelPackage, ExcelWorksheet, and ExcelRange will allow you to efficiently create, modify, and analyze Excel documents within your .NET applications.

#csharp #dotnet #excel #spreadsheet #eeplus #npoi #closedxml #exceldatareader