[C#] How to Modify and Overwrite Specific Excel Cell Values Using NPOI

Overview

This implementation reads an existing Excel file (.xlsx) and modifies specific cell values or writes new ones. It includes logic to safely edit files by creating row or cell objects if they do not exist, or retrieving existing objects if they do.

Specifications (Input/Output)

• Input: Target file path, sheet index, and the specific row, column, and value to change.
• Output: An updated Excel file (overwritten).
• Library: NPOI (NuGet package).

Method List

Method	Category	Description
GetRow	Retrieval	Retrieves the row object for the specified row number. Returns null if it does not exist.
CreateRow	Addition	Creates a new row at the specified row number.
SetValue	Modification	Sets a value at the specified row and column. Automatically creates the cell if it does not exist.
Save	Saving	Writes the changes back to the file (overwrite).

Basic Usage

// 1. Open an existing file
var book = MyExcelBook.Open("example.xlsx");
book.SelectSheet(0);

// 2. Get the row (create if it doesn't exist)
var row = book.GetRow(1) ?? book.CreateRow(1);

// 3. Change value (Cell B2)
book.SetValue(row, 1, "Updated Value");

// 4. Save
book.Save();

Full Code Example

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

class Program
{
    static void Main()
    {
        string filePath = "example.xlsx";

        // Verify that the file exists before proceeding
        if (!File.Exists(filePath))
        {
            Console.WriteLine("Target file not found.");
            return;
        }

        try
        {
            Console.WriteLine("Opening Excel file...");
            var xls = MyExcelBook.Open(filePath);
            
            // Select the first sheet
            xls.SelectSheet(0);

            // Case 1: Update values in an existing row (Index 1 = Row 2)
            // Use the "Get or Create" pattern in case the row does not exist
            var row1 = xls.GetRow(1) ?? xls.CreateRow(1);
            xls.SetValue(row1, 0, "Update Item"); // Column A
            Console.WriteLine("Row 1 updated.");

            // Case 2: Add a new row at a distance (Index 5 = Row 6)
            var row5 = xls.GetRow(5) ?? xls.CreateRow(5);
            xls.SetValue(row5, 2, "New Item");    // Column C
            Console.WriteLine("Row 5 added.");

            // Save
            xls.Save();
            Console.WriteLine("File saved successfully.");
        }
        catch (IOException ex)
        {
            Console.WriteLine($"File Access Error: {ex.Message}");
            Console.WriteLine("Ensure the Excel file is closed.");
        }
    }
}

/// <summary>
/// Excel editing class using NPOI
/// </summary>
public sealed class MyExcelBook
{
    private XSSFWorkbook _workbook;
    private ISheet _sheet;
    private string _filePath;

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

    /// <summary>
    /// Opens an existing Excel file.
    /// </summary>
    public static MyExcelBook Open(string filePath)
    {
        var obj = new MyExcelBook();
        obj._filePath = filePath;

        // Open in read mode and load into memory
        using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        obj._workbook = new XSSFWorkbook(stream);

        return obj;
    }

    /// <summary>
    /// Selects the target sheet by index.
    /// </summary>
    public void SelectSheet(int index)
    {
        _sheet = _workbook.GetSheetAt(index);
    }

    /// <summary>
    /// Retrieves the specified row (returns null if it does not exist).
    /// </summary>
    public IRow GetRow(int rowIndex)
    {
        return _sheet.GetRow(rowIndex);
    }

    /// <summary>
    /// Creates a new specified row.
    /// </summary>
    public IRow CreateRow(int rowIndex)
    {
        return _sheet.CreateRow(rowIndex);
    }

    /// <summary>
    /// Sets a value at the specified row and column (creates the cell if missing).
    /// </summary>
    public void SetValue(IRow row, int colIndex, string value)
    {
        // Get cell, or create it if it doesn't exist
        ICell cell = row.GetCell(colIndex) ?? row.CreateCell(colIndex);
        
        // Set value
        cell.SetCellValue(value);
    }

    /// <summary>
    /// Overwrites and saves the file.
    /// </summary>
    public void Save()
    {
        // Using FileMode.Create overwrites the existing file
        using var stream = new FileStream(_filePath, FileMode.Create, FileAccess.Write);
        _workbook.Write(stream);
    }
}

Customization Points

• Numeric Type Support: You can create overloads for the SetValue method to accept int or double. This allows saving data as numbers rather than strings, which is essential for calculations.
• Backup Saving: While the Save method overwrites the file at _filePath, you can modify the path to save the file under a different name as a backup.
• Maintaining Cell Styles: Since CreateCell generates a new cell, any existing borders or background colors may be lost. To maintain styles, you would need to implement style copying logic, such as using sourceRow.CopyRowTo when creating rows.

Important Notes

• File Locking: If you run this program while the target file is open in Excel, an IOException will occur when the Save method attempts to create the FileStream. Always close the Excel file before execution.
• Row Conflicts: The CreateRow method will overwrite an existing row with a “new empty row,” even if data already exists there. To edit while preserving existing data, always check for existence with GetRow and only call CreateRow if the result is null.
• Memory Usage: XSSFWorkbook loads the entire file into memory. Use caution regarding memory limits when editing Excel files that are several megabytes or larger.

Advanced Usage

Safe Row Retrieval Extension Method

This is an application of the common pattern “get if it exists, otherwise create.”

// Add to the class
public IRow GetOrCreateRow(int rowIndex)
{
    return _sheet.GetRow(rowIndex) ?? _sheet.CreateRow(rowIndex);
}

// Simplifies the calling side
var row = xls.GetOrCreateRow(1);
xls.SetValue(row, 0, "Simple update");

Conclusion

When changing values in Excel, checking whether the row or cell already exists is a mandatory step. In NPOI, non-existent rows or cells return null. By using the ?? operator to implement logic that “creates if retrieval fails,” you can create robust code that handles both new entries and updates effectively.