No Office, No Problem: Creating Excel Files in C# without Installing Microsoft Office: A Step-by-Step Guide

Excel files are commonly used for data storage and analysis. In this article, we will explore how to create Excel files in ASP.NET Core using two popular third-party libraries: EPPlus and NPOI. These libraries allow us to generate Excel files programmatically without relying on Microsoft Office. We’ll cover the installation process, and basic usage, and provide examples using the latest ASP.NET Core code standards.

Prerequisites:

Before we begin, ensure that you have the following prerequisites set up on your machine:

1. Visual Studio or Visual Studio Code
2. .NET Core SDK (latest version)

Step 1: Create a New ASP.NET Core Project

Let’s start by creating a new ASP.NET Core project:

1. Open Visual Studio or Visual Studio Code.
2. Create a new ASP.NET Core Web Application project.
3. Choose the desired project template and target framework version.
4. Provide a project name and location, and click “Create” to generate the project structure.

Step 2: Install EPPlus and NPOI NuGet Packages

To use the EPPlus and NPOI libraries in your ASP.NET Core project, install the corresponding NuGet packages:

1. Open the NuGet Package Manager in Visual Studio or the terminal in Visual Studio Code.
2. Run the following commands to install the packages:

dotnet add package EPPlus
dotnet add package NPOI

Step 3: Create an Excel File with EPPlus Now, let’s see how to create an Excel file using EPPlus:

1. Open the desired controller or create a new one (e.g., “ExcelController.cs”).
2. Import the necessary namespaces:

using OfficeOpenXml;
using System.IO;

3. Add an action method that generates the Excel file:

[HttpGet]
public IActionResult GenerateExcelWithEPPlus()
{
    using (ExcelPackage package = new ExcelPackage())
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

        // Set values in the cells
        worksheet.Cells["A1"].Value = "Hello";
        worksheet.Cells["B1"].Value = "World!";

        // Save the Excel package to a stream
        MemoryStream stream = new MemoryStream();
        package.SaveAs(stream);

        // Set the stream position to the beginning
        stream.Position = 0;

        // Return the Excel file as a FileStreamResult
        return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "example.xlsx");
    }
}

4. Build and run the project. Access the following URL in your browser: https://localhost:<port>/Excel/GenerateExcelWithEPPlus. This action method will generate and download the Excel file.

Step 4: Create an Excel File with NPOI Next, let’s explore how to create an Excel file using NPOI:

1. Create another action method in the same or a different controller (e.g., “ExcelController.cs”):

[HttpGet]
public IActionResult GenerateExcelWithNPOI()
{
    HSSFWorkbook workbook = new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");

    // Set values in the cells
    IRow row = sheet.CreateRow(0);
    row.CreateCell(0).SetCellValue("Hello");
    row.CreateCell(1).SetCellValue("World!");

    // Save the Excel workbook to a stream
    MemoryStream stream = new MemoryStream();
    workbook.Write(stream);

    // Set the stream position to the beginning
    stream.Position = 0;

    // Return the Excel file as a FileStreamResult
    return File(stream, "application/vnd.ms-excel", "example.xls");
}

2. Build and run the project. Access the following URL in your browser: https://localhost:<port>/Excel/GenerateExcelWithNPOI. This action method will generate and download the Excel file.

Conclusion:

In this article, we explored how to create Excel files in ASP.NET Core without installing Microsoft Office. We leveraged the power of EPPlus and NPOI libraries to generate Excel files programmatically. By following the step-by-step guide and using the latest ASP.NET Core code standards, you can easily create Excel files in your ASP.NET Core applications.

Note: Remember to handle exceptions, dispose of resources, and customize the code according to your specific requirements and file structures.