[C#] How to Create New Excel Files Using NPOI

Overview

This implementation uses the open-source library “NPOI” to create new .xlsx Excel files in environments where Microsoft Excel is not installed. By creating a wrapper class called MyExcelBook, we hide the complexity of NPOI and allow for intuitive workbook creation, sheet addition, and saving.

Specifications (Input/Output)

• Input: Destination file path, sheet name.
• Output: Excel file in .xlsx format.
• External Library: NPOI (NuGet package).

Implemented Methods

Method Name	Description
Create	Generates an instance for managing the Excel workbook at the specified path.
CreateSheet	Creates a new worksheet with the specified name within the workbook.
Save	Opens a stream and writes the data to the file system to save it.

Basic Usage

First, add the NuGet package to your project:

dotnet add package NPOI

Here is an example of how to use the implemented class:

// Generate instance (specify destination)
var xls = MyExcelBook.Create("example.xlsx");

// Create a sheet
xls.CreateSheet("MySheet");

// Save as a file
xls.Save();

Full Code Example

This code wraps NPOI functionality and is ready to run as a console application.

using System;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

class Program
{
    static void Main()
    {
        try
        {
            // 1. Create and configure the workbook
            Console.WriteLine("Creating Excel file...");
            var xls = MyExcelBook.Create("example.xlsx");

            // 2. Add sheets
            xls.CreateSheet("MySheet");
            xls.CreateSheet("SecondSheet");

            // 3. Save
            xls.Save();
            Console.WriteLine("Done. File saved as 'example.xlsx'.");
        }
        catch (IOException ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

/// <summary>
/// Wrapper class to simplify NPOI Workbook operations
/// </summary>
public sealed class MyExcelBook
{
    private readonly XSSFWorkbook _workbook;
    private string _filePath;

    // Private constructor; creation allowed only via the Create method
    private MyExcelBook()
    {
        _workbook = new XSSFWorkbook();
    }

    /// <summary>
    /// Generates an instance to manage a workbook at the specified path
    /// </summary>
    public static MyExcelBook Create(string filePath)
    {
        return new MyExcelBook
        {
            _filePath = filePath
        };
    }

    /// <summary>
    /// Creates a sheet with the specified name
    /// </summary>
    public void CreateSheet(string sheetName)
    {
        _workbook.CreateSheet(sheetName);
    }

    /// <summary>
    /// Saves the file to the physical disk
    /// </summary>
    public void Save()
    {
        // Create file stream in overwrite mode
        using var stream = new FileStream(_filePath, FileMode.Create, FileAccess.Write);
        _workbook.Write(stream);
    }
}

Customization Points

• Changing File Format: To handle the older .xls format, use HSSFWorkbook instead of XSSFWorkbook and change the using directive to NPOI.HSSF.UserModel.
• Implementing IDisposable: When handling many files or holding instances for a long time, it is recommended to implement the IDisposable interface and call _workbook.Close() to prevent memory leaks.
• Error Handling: If the file is open in another process (like Excel itself) during the Save operation, an exception will occur. Ensure you catch IOException using try-catch blocks.

Important Notes

• File Locking: If a file with the same name is already open in Excel, the Save method will throw an exception stating “The process cannot access the file.” Always close Excel before running the code.
• Sheet Name Constraints: Per Excel specifications, sheet names cannot contain certain characters (like :, \, ?, *, [, ]) and are limited to 31 characters. Validating the name within CreateSheet is safer.
• Memory Consumption: NPOI loads the entire workbook into memory. For massive datasets exceeding hundreds of thousands of rows, consider using the OpenXml SDK, which supports streaming.

Advanced Usage

Adding Cell Value Writing Functionality

Here is an example of extending the method to set values while creating the sheet:

public void SetCellValue(string sheetName, int rowData, int colData, string value)
{
    var sheet = _workbook.GetSheet(sheetName) ?? _workbook.CreateSheet(sheetName);
    var row = sheet.GetRow(rowData) ?? sheet.CreateRow(rowData);
    var cell = row.GetCell(colData) ?? row.CreateCell(colData);
    
    cell.SetCellValue(value);
}

Conclusion

Using NPOI allows you to generate Excel files from C# even in server or Linux environments where Excel is not installed. By creating a wrapper class to hide Workbook management and FileStream logic, your main code remains simple and the file generation logic becomes highly reusable.