Creating Excel files in C# without the need for Microsoft Office involves using libraries that can handle Excel file formats. Two commonly used libraries for this purpose are EPPlus and NPOI. Both libraries support creating and manipulating .XLSX files, while NPOI also supports .XLS files. Below, we delve into the steps required to create an Excel file using these libraries, along with a comparison of their features.

EPPlus

EPPlus is an open-source library strictly for working with .XLSX files. It is known for ease of use and comprehensive features that emulate Excel functionalities.

Installation

To use EPPlus, you can install it via NuGet Package Manager Console:

Install-Package EPPlus

Creating an Excel File

Here is an example of how to create an .XLSX file using EPPlus:

1using OfficeOpenXml; // Make sure to add this using directive
2
3class Program
4{
5    static void Main()
6    {
7        // Ensure the license context is set to non-commercial
8        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
9
10        using (var package = new ExcelPackage())
11        {
12            var worksheet = package.Workbook.Worksheets.Add("Sheet1");
13
14            // Add some data
15            worksheet.Cells[1, 1].Value = "Hello";
16            worksheet.Cells[1, 2].Value = "World";
17
18            // Saving the file
19            var fileInfo = new FileInfo(@"path_to_save\example.xlsx");
20            package.SaveAs(fileInfo);
21        }
22    }
23}

Key Features

• Supports .XLSX file format.
• Allows detailed styling and formatting.
• Includes formulas, charts, and data validation.

NPOI

NPOI is a port of the Apache POI project, allowing .NET applications to read and write Microsoft Office formats, including both .XLS (Excel 97-2003) and .XLSX (Excel 2007+).

Installation

To use NPOI, install it via NuGet:

Install-Package NPOI

Creating an Excel File

Below is an example for creating both .XLS and .XLSX files using NPOI:

1using NPOI.SS.UserModel;
2using NPOI.XSSF.UserModel;
3using NPOI.HSSF.UserModel;
4using System.IO;
5
6class Program
7{
8    static void Main()
9    {
10        // Creating a workbook
11        IWorkbook workbook = new XSSFWorkbook(); // Use HSSFWorkbook for .XLS
12        ISheet sheet = workbook.CreateSheet("Sheet1");
13
14        // Creating a row and adding some data
15        IRow row = sheet.CreateRow(0);
16        row.CreateCell(0).SetCellValue("Hello");
17        row.CreateCell(1).SetCellValue("World");
18
19        // Saving .XLSX
20        using (var fileData = new FileStream(@"path_to_save\example.xlsx", FileMode.Create))
21        {
22            workbook.Write(fileData);
23        }
24
25        // To save as .XLS use HSSFWorkbook and above process
26    }
27}

Key Features

• Supports both .XLS and .XLSX.
• Comprehensive support for Excel features.
• Wider compatibility with older Excel versions.

Comparison Table

Additional Considerations

• Performance: Both libraries are performant, but EPPlus is often selected for tasks involving the latest Excel functionalities.
• Licensing: Both libraries are open-source, but you should verify the license agreements, especially for commercial use.
• Community Support: Both have active communities, with EPPlus being slightly more popular due to its extensive feature set for newer Excel formats.
• Features: EPPlus is usually preferred for advanced features like spark lines and more modern Excel functions, while NPOI is ideal for applications needing backward compatibility with .XLS.

Conclusion

Selecting between EPPlus and NPOI depends on your specific needs, such as backward compatibility and required features. Both libraries provide a robust pathway to manipulate Excel files without needing to install Microsoft Office, expanding the capabilities of C# applications. By understanding their differences and features, you can choose the right tool for your project.