Introduction
NPOI is a .NET library for reading and writing Microsoft Office files (Excel, Word) without requiring Office to be installed. To read an Excel file, open a FileStream, create an IWorkbook from it (using HSSFWorkbook for .xls or XSSFWorkbook for .xlsx), then iterate through sheets, rows, and cells. NPOI is the .NET port of Apache POI (Java) and works on .NET Framework, .NET Core, and .NET 5+.
Installation
1# .NET CLI
2dotnet add package NPOI
3
4# Package Manager Console
5Install-Package NPOI
6
7# Or for latest version
8dotnet add package NPOI --version 2.7.1
The NPOI NuGet package includes support for both .xls (HSSF) and .xlsx (XSSF) formats.
Reading an .xlsx File (Basic)
1using NPOI.SS.UserModel;
2using NPOI.XSSF.UserModel;
3using System.IO;
4
5// Open the file
6using var stream = new FileStream("data.xlsx", FileMode.Open, FileAccess.Read);
7IWorkbook workbook = new XSSFWorkbook(stream);
8
9// Get the first sheet
10ISheet sheet = workbook.GetSheetAt(0);
11
12// Iterate through rows
13for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
14{
15 IRow row = sheet.GetRow(rowIndex);
16 if (row == null) continue; // Skip empty rows
17
18 for (int colIndex = 0; colIndex < row.LastCellNum; colIndex++)
19 {
20 ICell cell = row.GetCell(colIndex);
21 Console.Write($"{GetCellValue(cell)}\t");
22 }
23 Console.WriteLine();
24}
Reading an .xls File
1using NPOI.HSSF.UserModel;
2using NPOI.SS.UserModel;
3using System.IO;
4
5// .xls files use HSSFWorkbook
6using var stream = new FileStream("data.xls", FileMode.Open, FileAccess.Read);
7IWorkbook workbook = new HSSFWorkbook(stream);
8
9ISheet sheet = workbook.GetSheetAt(0);
10// Rest is identical — use IWorkbook/ISheet interfaces
1using NPOI.SS.UserModel;
2using NPOI.HSSF.UserModel;
3using NPOI.XSSF.UserModel;
4using System.IO;
5
6IWorkbook OpenWorkbook(string filePath)
7{
8 using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
9
10 if (Path.GetExtension(filePath).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
11 return new XSSFWorkbook(stream);
12 else
13 return new HSSFWorkbook(stream);
14}
15
16// Or use WorkbookFactory (auto-detects format)
17using var stream = new FileStream("data.xlsx", FileMode.Open, FileAccess.Read);
18IWorkbook workbook = WorkbookFactory.Create(stream);
WorkbookFactory.Create() examines the file header and creates the correct workbook type automatically.
Handling Cell Types
1string GetCellValue(ICell cell)
2{
3 if (cell == null) return string.Empty;
4
5 switch (cell.CellType)
6 {
7 case CellType.String:
8 return cell.StringCellValue;
9
10 case CellType.Numeric:
11 // Check if it is a date
12 if (DateUtil.IsCellDateFormatted(cell))
13 return cell.DateCellValue.ToString("yyyy-MM-dd");
14 return cell.NumericCellValue.ToString();
15
16 case CellType.Boolean:
17 return cell.BooleanCellValue.ToString();
18
19 case CellType.Formula:
20 // Evaluate the formula and return the result
21 return cell.CellType == CellType.Numeric
22 ? cell.NumericCellValue.ToString()
23 : cell.StringCellValue;
24
25 case CellType.Blank:
26 return string.Empty;
27
28 default:
29 return cell.ToString();
30 }
31}
Always check CellType before accessing cell values. Accessing StringCellValue on a numeric cell throws an exception.
1List<Dictionary<string, string>> ReadExcelAsRecords(string filePath)
2{
3 using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
4 IWorkbook workbook = WorkbookFactory.Create(stream);
5 ISheet sheet = workbook.GetSheetAt(0);
6
7 // First row is headers
8 IRow headerRow = sheet.GetRow(0);
9 var headers = new List<string>();
10 for (int i = 0; i < headerRow.LastCellNum; i++)
11 {
12 headers.Add(GetCellValue(headerRow.GetCell(i)));
13 }
14
15 // Read data rows
16 var records = new List<Dictionary<string, string>>();
17 for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
18 {
19 IRow row = sheet.GetRow(rowIndex);
20 if (row == null) continue;
21
22 var record = new Dictionary<string, string>();
23 for (int colIndex = 0; colIndex < headers.Count; colIndex++)
24 {
25 record[headers[colIndex]] = GetCellValue(row.GetCell(colIndex));
26 }
27 records.Add(record);
28 }
29
30 return records;
31}
32
33// Usage
34var data = ReadExcelAsRecords("employees.xlsx");
35foreach (var row in data)
36{
37 Console.WriteLine($"Name: {row["Name"]}, Age: {row["Age"]}");
38}
Reading a Specific Sheet by Name
1ISheet sheet = workbook.GetSheet("Sales Data");
2if (sheet == null)
3{
4 Console.WriteLine("Sheet 'Sales Data' not found");
5 // List available sheets
6 for (int i = 0; i < workbook.NumberOfSheets; i++)
7 {
8 Console.WriteLine($" Sheet {i}: {workbook.GetSheetName(i)}");
9 }
10}
1using NPOI.SS.UserModel;
2
3IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
4
5ICell cell = sheet.GetRow(0).GetCell(0);
6if (cell.CellType == CellType.Formula)
7{
8 CellValue evaluated = evaluator.Evaluate(cell);
9 switch (evaluated.CellType)
10 {
11 case CellType.Numeric:
12 Console.WriteLine(evaluated.NumberValue);
13 break;
14 case CellType.String:
15 Console.WriteLine(evaluated.StringValue);
16 break;
17 case CellType.Boolean:
18 Console.WriteLine(evaluated.BooleanValue);
19 break;
20 }
21}
Use IFormulaEvaluator to compute formula results. Without evaluation, formula cells return the formula text, not the computed value.
Reading into a DataTable
1using System.Data;
2
3DataTable ExcelToDataTable(string filePath)
4{
5 using var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
6 IWorkbook workbook = WorkbookFactory.Create(stream);
7 ISheet sheet = workbook.GetSheetAt(0);
8
9 var table = new DataTable(sheet.SheetName);
10
11 // Create columns from header row
12 IRow headerRow = sheet.GetRow(0);
13 for (int i = 0; i < headerRow.LastCellNum; i++)
14 {
15 table.Columns.Add(GetCellValue(headerRow.GetCell(i)));
16 }
17
18 // Add data rows
19 for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
20 {
21 IRow row = sheet.GetRow(rowIndex);
22 if (row == null) continue;
23
24 DataRow dataRow = table.NewRow();
25 for (int colIndex = 0; colIndex < table.Columns.Count; colIndex++)
26 {
27 dataRow[colIndex] = GetCellValue(row.GetCell(colIndex));
28 }
29 table.Rows.Add(dataRow);
30 }
31
32 return table;
33}
Common Pitfalls
Not disposing the FileStream: Forgetting using on the FileStream leaves the file locked. Always use using var stream = ... or explicitly call stream.Dispose() to release the file handle.
Null rows and cells: Empty rows return null from GetRow(), and empty cells return null from GetCell(). Always check for null before accessing properties, or use GetCell(index, MissingCellPolicy.CREATE_NULL_AS_BLANK).
Wrong workbook type: Using XSSFWorkbook for a .xls file (or HSSFWorkbook for .xlsx) throws an exception. Use WorkbookFactory.Create() to auto-detect, or check the file extension.
Date cells appear as numbers: Excel stores dates as numeric values. Use DateUtil.IsCellDateFormatted(cell) to check if a numeric cell is actually a date before reading it.
Large files and memory: NPOI loads the entire workbook into memory. For very large .xlsx files (100MB+), consider using the SAX-based event model (XSSFReader) or stream-based reading to reduce memory usage.
Summary
Use XSSFWorkbook for .xlsx and HSSFWorkbook for .xls, or WorkbookFactory.Create() for auto-detection
Always check CellType before accessing cell values — mismatched access throws exceptions
Use DateUtil.IsCellDateFormatted() to distinguish dates from numbers
Use IFormulaEvaluator to compute formula cell results
Check for null rows and cells when iterating — Excel files often have gaps
Dispose FileStream properly with using to avoid file locks
