[C#] How to Write Values to Excel Cells Using NPOI

Overview

This implementation uses the open-source library “NPOI” to set values in specific cells of an Excel sheet. We use a wrapper class to simplify the NPOI-specific process—creating a row first, then allocating a cell within it—allowing you to efficiently fill data using loops.

Specifications (Input/Output)

• Input: Save path, sheet name, and data to write.
• Output: A .xlsx file containing the entered data.
• Library: NPOI (NuGet package).

Implemented Methods

Method Name	Description
CreateRow	Creates a row at the specified index (0-based) and returns the row object (IRow).
SetCellValue	Creates a cell at the specified column in a row object and sets the value.

Basic Usage

// Create instance
var excel = ExcelWriter.Create("matrix.xlsx");
excel.AddSheet("ScoreData");

// Create the 0th row
var row = excel.CreateRow(0);

// Set values at (0, 0) and (0, 1)
excel.SetCellValue(row, 0, "Alice");
excel.SetCellValue(row, 1, "95");

excel.Save();

Full Code Example

To run this code, you need to execute dotnet add package NPOI.

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

class Program
{
    static void Main()
    {
        try
        {
            // 1. Initialize the Excel operation class
            Console.WriteLine("Generating Excel file...");
            var writer = ExcelWriter.Create("GridData.xlsx");

            // 2. Add a sheet
            writer.AddSheet("MultiplicationTable");

            // 3. Write values using loops
            // In NPOI, you get/create a "Row" first, then operate on "Cells" within it
            for (int r = 0; r < 9; r++)
            {
                // Create a row object (IRow)
                // Row numbers are 0-based (0 = 1st row in Excel)
                IRow currentRow = writer.CreateRow(r);

                for (int c = 0; c < 9; c++)
                {
                    // Write calculation results as strings
                    var value = $"{r + 1} x {c + 1} = {(r + 1) * (c + 1)}";
                    writer.SetCellValue(currentRow, c, value);
                }
            }

            // 4. Save
            writer.Save();
            Console.WriteLine("File saved successfully.");
        }
        catch (IOException ex)
        {
            Console.WriteLine($"File Access Error: {ex.Message}");
        }
    }
}

/// <summary>
/// Class that wraps NPOI cell operations
/// </summary>
public sealed class ExcelWriter
{
    private readonly XSSFWorkbook _workbook;
    private ISheet _currentSheet;
    private string _filePath;

    private ExcelWriter()
    {
        _workbook = new XSSFWorkbook();
    }

    /// <summary>
    /// Creates an instance to save at the specified path
    /// </summary>
    public static ExcelWriter Create(string filePath)
    {
        return new ExcelWriter { _filePath = filePath };
    }

    /// <summary>
    /// Creates a new sheet and sets it as the active sheet
    /// </summary>
    public void AddSheet(string sheetName)
    {
        _currentSheet = _workbook.CreateSheet(sheetName);
    }

    /// <summary>
    /// Creates a row at the specified index and returns the object
    /// </summary>
    /// <param name="rowIndex">Row index (0-based)</param>
    /// <returns>Created row object</returns>
    public IRow CreateRow(int rowIndex)
    {
        if (_currentSheet == null)
        {
            throw new InvalidOperationException("Sheet must be created before adding rows.");
        }
        return _currentSheet.CreateRow(rowIndex);
    }

    /// <summary>
    /// Sets a string value at the specified row and column
    /// </summary>
    /// <param name="row">Target row object</param>
    /// <param name="columnIndex">Column index (0-based)</param>
    /// <param name="value">String to set</param>
    public void SetCellValue(IRow row, int columnIndex, string value)
    {
        // Create cell (assuming new creation here)
        var cell = row.CreateCell(columnIndex);
        cell.SetCellValue(value);
    }

    /// <summary>
    /// Saves the file
    /// </summary>
    public void Save()
    {
        using var fs = new FileStream(_filePath, FileMode.Create, FileAccess.Write);
        _workbook.Write(fs);
    }
}

Customization Points

• Handling Numbers and Dates: Currently, SetCellValue only accepts strings. By creating overloads for double or DateTime and calling cell.SetCellValue(value), values will be treated as numbers in Excel, allowing for calculations.
• Getting Existing Rows: CreateRow creates a new row. If you want to append to an existing row, change the logic to: _currentSheet.GetRow(rowIndex) ?? _currentSheet.CreateRow(rowIndex).

Important Notes

• Reusing Row Objects: Calling CreateRow twice for the same row number will overwrite (clear) previous row data. To add multiple cells to the same row, hold the row object in a variable and reuse it, as shown in the example.
• Index Limits: For Excel 2007+ (.xlsx), the maximum row count is 1,048,576 and the maximum column count is 16,384. Exceeding these might result in files that cannot be opened.
• Performance: NPOI creates objects in memory for every cell. Handling hundreds of thousands of rows may slow down performance due to frequent Garbage Collection (GC).

Advanced Usage

Adding Overloads for Numeric Types

This extension allows you to embed data as calculable numbers instead of just text.

// Add to the ExcelWriter class
public void SetCellValue(IRow row, int columnIndex, double value)
{
    var cell = row.CreateCell(columnIndex);
    
    // Set as numeric (allowing SUM and other formulas in Excel)
    cell.SetCellValue(value); 
}

Conclusion

Writing to cells in NPOI requires following a hierarchy: Workbook -> Sheet -> Row -> Cell. When using loops, creating the row (IRow) in the outer loop and filling cells (ICell) in the inner loop reduces object creation costs and results in more intuitive code.