NPOI is an open-source .NET API for developing applications that work with spreadsheets. It is free to use in .NET projects, including commercial ones, as it is licensed under the Apache License 2.0. This means you can freely use, modify, and distribute NPOI as long as you comply with the license terms. NPOI is simple, easy to use, and can be quickly set up to start working with Excel spreadsheets.

In this article, we’ll explore working with NPOI API to create, read and save Excel XLSX files, though it can work with XLS file format in the same manner. This article contains details about the installation of NPOI API, setting up the development environment, and working with Spreadsheets using NPOI.

  1. Installation guide for NPOI API: The first step to using NPOI API is to install the package. You can install NPOI by downloading the package from the official website or using NuGet package manager in Visual Studio. Once the package is installed, you can start using it in your project. See this NPOI Installation Guide for reference.
  2. Setting up the development environment: In order to use NPOI API, you should have the development environment ready for working with it. You can install Visual Studio on your machine for this purpose.
  3. Creating a new spreadsheet using NPOI API: Now that your environment is set up, you can start working with the API in your project by including a reference to its DLLs.

Working with Spreadsheets

Alright, so now that you have NPOI set up, lets get started with using NPOI API for working with XLSX files. Specifically, we’ll be:

  • Creating a new Spreadsheet using NPOI in C#
  • Reading data from an XLSX file with NPOI
  • Saving an Excel Spreadsheet as XLSX using NPOI

Create a New Spreadsheet using NPOI in C#

The following code snippet demonstrates how to create a new spreadsheet with a single worksheet:

C#

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

// create a new workbook
IWorkbook wb = new XSSFWorkbook();

// create a new worksheet
ISheet ws = wb.CreateSheet("Sheet1");

// create a new row
IRow row = ws.CreateRow(0);

// create a new cell and set its value
ICell cell = row.CreateCell(0);
cell.SetCellValue("Hello World!");

// save the workbook
using (var fileStream = new FileStream("myWorkbook.xlsx", FileMode.Create, FileAccess.Write))
{
    wb.Write(fileStream);
}

Reading data from Spreadsheets using NPOI API

One of the most common tasks when working with spreadsheets is reading data from them. NPOI API provides several ways to read data from spreadsheets. The following code snippet demonstrates how to read data from a cell in a spreadsheet:

C#

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

// load an existing workbook
IWorkbook wb = new XSSFWorkbook("myWorkbook.xlsx");

// get the first worksheet
ISheet ws = wb.GetSheetAt(0);

// get the first row
IRow row = ws.GetRow(0);

// get the first cell
ICell cell = row.GetCell(0);

// get the cell value
string cellValue = cell.StringCellValue;

Writing data to spreadsheets using NPOI API

Another common task when working with spreadsheets is writing data to them. NPOI API provides several ways to write data to spreadsheets. The following code snippet demonstrates how to write data to a cell in a spreadsheet:

C#

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

// load an existing workbook
IWorkbook wb = new XSSFWorkbook("myWorkbook.xlsx");

// get the first worksheet
ISheet ws = wb.GetSheetAt(0);

// get the first row
IRow row = ws.GetRow(0);

// get the first cell
ICell cell = row.CreateCell(1);

// set the cell value
cell.SetCellValue("World!");

// save the workbook
using (var fileStream = new FileStream("myWorkbook.xlsx", FileMode.Create, FileAccess.Write))
{
    wb.Write(fileStream);
}

In the above example, we load an existing workbook and retrieve the first worksheet. We then retrieve the first row and create a new cell at column index 1. We then set the value of the cell and save the workbook.

Updating existing spreadsheets using NPOI API

NPOI API also provides several ways to update existing spreadsheets. The following code snippet demonstrates how to update the value of a cell in a spreadsheet.

Steps: Update the value of a cell in a spreadsheet using NPOI

  1. Load an existing Excel workbook. XSSFWorkbook is used for .xlsx files (Excel 2007 and later)
  2. Get the first sheet in the workbook. Then, get the first row.
  3. Get the first cell in a row.
  4. Set a new value for the cell.
  5. Finally, save the modified workbook.

Always use XSSFWorkbook for .xlsx files. Use HSSFWorkbook for older .xls files.

C#

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

// load an existing workbook
IWorkbook wb = new XSSFWorkbook("myWorkbook.xlsx");

// get the first worksheet
ISheet ws = wb.GetSheetAt(0);

// get the first row
IRow row = ws.GetRow(0);

// get the first cell
ICell cell = row.GetCell(0);

// set the cell value
cell.SetCellValue("Hello NPOI!");

// save the workbook
using (var fileStream = new FileStream("myWorkbook.xlsx", FileMode.Create, FileAccess.Write))
{
    wb.Write(fileStream);
}

In the above example, we load an existing workbook and retrieve the first worksheet. We then retrieve the first row and the first cell and update the value of the cell. Finally, we save the workbook.

The official NPOI documentation can be found on its GitHub repository. You can access it here: GitHub NPOI Repository. It includes the documentation, examples, and code related to NPOI, along with installation instructions and usage guides.

More NPOI Examples for Working with Spreadsheets