A Comparison of IronXL and NPOI

What is NPOI?

NPOI is the .NET version of the POI Java project at http://poi.apache.org/. POI is an open-source project that can help you read/write xls, doc, ppt files. It has a wide range of applications.

For example, you can use it to:

• Generate an Excel report without Microsoft Office suite installed on your server and more efficient than calling Microsoft Excel ActiveX in the background
• Extract text from Office documents to help you implement a full-text indexing feature (most of the time this feature is used to create search engines)
• Extract images from Office documents
• Generate Excel sheets that contain formulas

NPOI and Excel

NPOI is a C# port of the POI Java project by Apache. It is free and open-source. Also, it doesn't need Interop, meaning that users will not need to have Excel installed to have the developer's app work with it.

IronXL and Excel

IronXL is an Excel API for VB and C#. With IronXL you can read, edit, and create Excel spreadsheet files in .NET.

Common Library Features for the NPOI and IronXL Excel Libraries

Table 1 - Feature Comparison

Installation of IronXL and NPOI

You can install both libraries by downloading them manually, via NuGet, or with the NuGet Package Manager in Visual Studio. Here is a quick overview.

NPOI Installation

Installing NPOI with NuGet

To install NPOI through NuGet, open the Visual Studio developer command prompt and enter the following:

Install-Package NPOI -Version x.x.x

Figure 1 - NuGet NPOI Installation

Visual Studio NuGet Package Manager and NPOI

Use the following steps to install IronXL or NPOI via the NuGet Package Manager in Visual Studio:

• Right-click the project in the Solution Explorer
• Select Manage NuGet Packages
• Browse for your Package
• Click Install

Figure 2 - NuGet Package Manager for NPOI

IronXL Installation

Downloading IronXL

To download IronXL, navigate to the following URL and click the "Download" button.

Figure 3 - Download IronXL

Installing IronXL with NuGet

To install IronXL through NuGet, open the Visual Studio developer command prompt and enter the following:

Install-Package IronXL.Excel -Version x.x.x

Figure 4 - NuGet IronXL Installation

Visual Studio NuGet Package Manager and IronXL

Use the following steps to install IronXL via the NuGet Package Manager in Visual Studio:

• Right-click the project in the Solution Explorer
• Select Manage NuGet Packages
• Browse for your Package
• Click Install

Figure 5 - NuGet Package Manager for IronXL

Reading from and writing to an Excel file with NPOI and IronXL

Reading an Excel file with NPOI

The following code demonstrates how to read an Excel file and display its contents with NPOI. Add the following code and include the necessary namespaces:

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
using System.Collections.Generic;

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
using System.Collections.Generic;

Imports NPOI.SS.UserModel

Imports NPOI.XSSF.UserModel

Imports System.Data

Imports System.IO

Imports System.Collections.Generic

The below code reads an existing Excel file and displays it inside a data grid view.

public void ReadExcelNPOI()
{
    DataTable dtTable = new DataTable();
    List<string> lstRows = new List<string>();
    ISheet objWorksheet;
    string strPath = @"c:\temp\NPOI_Test.XLSX";

    // Use FileStream to open the Excel file
    using (var fStream = new FileStream(strPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        fStream.Position = 0;
        XSSFWorkbook objWorkbook = new XSSFWorkbook(fStream);
        objWorksheet = objWorkbook.GetSheetAt(0);
        IRow objHeader = objWorksheet.GetRow(0);
        int countCells = objHeader.LastCellNum;

        // Add columns to the DataTable based on the header row of Excel
        for (int j = 0; j < countCells; j++)
        {
            ICell objCell = objHeader.GetCell(j);
            if (objCell == null 

 string.IsNullOrWhiteSpace(objCell.ToString())) continue;
            {
                dtTable.Columns.Add(objCell.ToString());
            }
        }

        // Add rows to the DataTable, looping through each row and cell
        for (int i = (objWorksheet.FirstRowNum + 1); i <= objWorksheet.LastRowNum; i++)
        {
            IRow objRow = objWorksheet.GetRow(i);
            if (objRow == null 

 objRow.Cells.All(d => d.CellType == CellType.Blank)) continue;

            for (int j = objRow.FirstCellNum; j < countCells; j++)
            {
                ICell cell = objRow.GetCell(j);
                if (cell != null && !string.IsNullOrEmpty(cell.ToString()) && !string.IsNullOrWhiteSpace(cell.ToString()))
                {
                    lstRows.Add(cell.ToString());
                }
            }

            if (lstRows.Count > 0)
                dtTable.Rows.Add(lstRows.ToArray());

            lstRows.Clear();
        }
    }

    // Assuming dataGridView1 is a DataGridView control on a Form
    dataGridView1.DataSource = dtTable;
}

private void button1_Click(object sender, EventArgs e)
{
    ReadExcelNPOI();
}

public void ReadExcelNPOI()
{
    DataTable dtTable = new DataTable();
    List<string> lstRows = new List<string>();
    ISheet objWorksheet;
    string strPath = @"c:\temp\NPOI_Test.XLSX";

    // Use FileStream to open the Excel file
    using (var fStream = new FileStream(strPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        fStream.Position = 0;
        XSSFWorkbook objWorkbook = new XSSFWorkbook(fStream);
        objWorksheet = objWorkbook.GetSheetAt(0);
        IRow objHeader = objWorksheet.GetRow(0);
        int countCells = objHeader.LastCellNum;

        // Add columns to the DataTable based on the header row of Excel
        for (int j = 0; j < countCells; j++)
        {
            ICell objCell = objHeader.GetCell(j);
            if (objCell == null 

 string.IsNullOrWhiteSpace(objCell.ToString())) continue;
            {
                dtTable.Columns.Add(objCell.ToString());
            }
        }

        // Add rows to the DataTable, looping through each row and cell
        for (int i = (objWorksheet.FirstRowNum + 1); i <= objWorksheet.LastRowNum; i++)
        {
            IRow objRow = objWorksheet.GetRow(i);
            if (objRow == null 

 objRow.Cells.All(d => d.CellType == CellType.Blank)) continue;

            for (int j = objRow.FirstCellNum; j < countCells; j++)
            {
                ICell cell = objRow.GetCell(j);
                if (cell != null && !string.IsNullOrEmpty(cell.ToString()) && !string.IsNullOrWhiteSpace(cell.ToString()))
                {
                    lstRows.Add(cell.ToString());
                }
            }

            if (lstRows.Count > 0)
                dtTable.Rows.Add(lstRows.ToArray());

            lstRows.Clear();
        }
    }

    // Assuming dataGridView1 is a DataGridView control on a Form
    dataGridView1.DataSource = dtTable;
}

private void button1_Click(object sender, EventArgs e)
{
    ReadExcelNPOI();
}

Public Sub ReadExcelNPOI()

	Dim dtTable As New DataTable()

	Dim lstRows As New List(Of String)()

	Dim objWorksheet As ISheet

	Dim strPath As String = "c:\temp\NPOI_Test.XLSX"



	' Use FileStream to open the Excel file

	Using fStream = New FileStream(strPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)

		fStream.Position = 0

		Dim objWorkbook As New XSSFWorkbook(fStream)

		objWorksheet = objWorkbook.GetSheetAt(0)

		Dim objHeader As IRow = objWorksheet.GetRow(0)

		Dim countCells As Integer = objHeader.LastCellNum



		' Add columns to the DataTable based on the header row of Excel

		For j As Integer = 0 To countCells - 1

			Dim objCell As ICell = objHeader.GetCell(j)

			If objCell Is Nothing OrElse String.IsNullOrWhiteSpace(DirectCast(objCell, Object).ToString()) Then

				Continue For

			End If

			If True Then

				dtTable.Columns.Add(DirectCast(objCell, Object).ToString())

			End If

		Next j



		' Add rows to the DataTable, looping through each row and cell

		For i As Integer = (objWorksheet.FirstRowNum + 1) To objWorksheet.LastRowNum

			Dim objRow As IRow = objWorksheet.GetRow(i)

			If objRow Is Nothing OrElse objRow.Cells.All(Function(d) d.CellType = CellType.Blank) Then

				Continue For

			End If



			For j As Integer = objRow.FirstCellNum To countCells - 1

				Dim cell As ICell = objRow.GetCell(j)

				If cell IsNot Nothing AndAlso Not String.IsNullOrEmpty(DirectCast(cell, Object).ToString()) AndAlso Not String.IsNullOrWhiteSpace(DirectCast(cell, Object).ToString()) Then

					lstRows.Add(DirectCast(cell, Object).ToString())

				End If

			Next j



			If lstRows.Count > 0 Then

				dtTable.Rows.Add(lstRows.ToArray())

			End If



			lstRows.Clear()

		Next i

	End Using



	' Assuming dataGridView1 is a DataGridView control on a Form

	dataGridView1.DataSource = dtTable

End Sub



Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)

	ReadExcelNPOI()

End Sub

Reading an Excel file with IronXL

The following code demonstrates how to read an Excel file and display it inside a data grid view with IronXL. Add the following code and include the namespace:

using IronXL;
using System.Data;

using IronXL;
using System.Data;

Imports IronXL

Imports System.Data

Notice the inclusion of IronXL. This is necessary for IronXL to work. Add the next few lines:

private void button2_Click(object sender, EventArgs e)
{
    // Load the Excel workbook
    string strPath = @"c:\temp\NPOI_Test.XLSX";
    WorkBook workbook = WorkBook.Load(strPath);

    // Access the default worksheet
    WorkSheet sheet = workbook.DefaultWorkSheet;

    // Convert the worksheet to a DataTable
    var dtTable = sheet.ToDataTable(true);

    // Assuming dataGridView1 is a DataGridView control on a Form
    dataGridView1.DataSource = dtTable;
}

private void button2_Click(object sender, EventArgs e)
{
    // Load the Excel workbook
    string strPath = @"c:\temp\NPOI_Test.XLSX";
    WorkBook workbook = WorkBook.Load(strPath);

    // Access the default worksheet
    WorkSheet sheet = workbook.DefaultWorkSheet;

    // Convert the worksheet to a DataTable
    var dtTable = sheet.ToDataTable(true);

    // Assuming dataGridView1 is a DataGridView control on a Form
    dataGridView1.DataSource = dtTable;
}

Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs)

	' Load the Excel workbook

	Dim strPath As String = "c:\temp\NPOI_Test.XLSX"

	Dim workbook As WorkBook = WorkBook.Load(strPath)



	' Access the default worksheet

	Dim sheet As WorkSheet = workbook.DefaultWorkSheet



	' Convert the worksheet to a DataTable

	Dim dtTable = sheet.ToDataTable(True)



	' Assuming dataGridView1 is a DataGridView control on a Form

	dataGridView1.DataSource = dtTable

End Sub

As you can see, this all adds up to a lot less work.

Conclusion

So why IronXL? As you see, IronXL has an easy to use API, that keeps your code clean. Libraries that use IronXL boast clean, readable code which is an investment that pays off by reducing time and effort needed to understand what the code does, allowing it to be changed without breaking things.

IronXL is more intuitive all-around while being as powerful as NPOI. And, of course, with a license key you get access to the most important advantage of paid software: you get our professional support, consistent and reliable update schedules, and ability to request features from Iron Software support agents who are happy to assist you. This is something open source just can’t provide.

Downloads

This project is available on GitHub:

IronXL vs NPOI Example