A Comparison of IronXL and NPOI

As you may know, Excel is probably the most used spreadsheet application in the world. Users include both developers and the general population, but it is developers who will probably be more engaged by this article. Initially, there weren’t many options for developers to work with Excel across their various applications. However, there were VBA (Visual Basic for Applications) built into the Office Suite with which you could manipulate any Office product according to your needs.

Developers used VBA in Excel to customize their interactions with Excel and its data, even its visual capabilities. But, eventually this wasn’t enough. Developers wanted more, and so they began using Office.Interop.

In this article, I will compare two very good Excel libraries that do not depend on Office.Interop to work with Excel. They are:

• IronXL
• NPOI

You will see and learn what each Excel library can do, and then compare the two through code examples and step-by-step tutorials. Although this article cannot cover all features of both Excel libraries, it will explain the most common and most popular uses of each.

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 & 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:

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:

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 NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel

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";
            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;

                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());
                    }
                }
                for (int i = (objWorksheet.FirstRowNum + 1); i <= objWorksheet.LastRowNum; i++)
                {
                    IRow objRow = objWorksheet.GetRow(i);
                    if (objRow == null) continue;
                    if (objRow.Cells.All(d => d.CellType == CellType.Blank)) continue;
                    for (int j = objRow.FirstCellNum; j < countCells; j++)
                    {
                        if (objRow.GetCell(j) != null)
                        {
                            if (!string.IsNullOrEmpty(objRow.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(objRow.GetCell(j).ToString()))
                            {
                                lstRows.Add(objRow.GetCell(j).ToString());
                            }
                        }
                    }

                    if (lstRows.Count > 0)
                        dtTable.Rows.Add(lstRows.ToArray());
                    lstRows.Clear();
                }
            }
            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";
            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;

                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());
                    }
                }
                for (int i = (objWorksheet.FirstRowNum + 1); i <= objWorksheet.LastRowNum; i++)
                {
                    IRow objRow = objWorksheet.GetRow(i);
                    if (objRow == null) continue;
                    if (objRow.Cells.All(d => d.CellType == CellType.Blank)) continue;
                    for (int j = objRow.FirstCellNum; j < countCells; j++)
                    {
                        if (objRow.GetCell(j) != null)
                        {
                            if (!string.IsNullOrEmpty(objRow.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(objRow.GetCell(j).ToString()))
                            {
                                lstRows.Add(objRow.GetCell(j).ToString());
                            }
                        }
                    }

                    if (lstRows.Count > 0)
                        dtTable.Rows.Add(lstRows.ToArray());
                    lstRows.Clear();
                }
            }
            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"
			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

				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
				For i As Integer = (objWorksheet.FirstRowNum + 1) To objWorksheet.LastRowNum
					Dim objRow As IRow = objWorksheet.GetRow(i)
					If objRow Is Nothing Then
						Continue For
					End If
					If objRow.Cells.All(Function(d) d.CellType = CellType.Blank) Then
						Continue For
					End If
					For j As Integer = objRow.FirstCellNum To countCells - 1
						If objRow.GetCell(j) IsNot Nothing Then
							If Not String.IsNullOrEmpty(objRow.GetCell(j).ToString()) AndAlso Not String.IsNullOrWhiteSpace(objRow.GetCell(j).ToString()) Then
								lstRows.Add(objRow.GetCell(j).ToString())
							End If
						End If
					Next j

					If lstRows.Count > 0 Then
						dtTable.Rows.Add(lstRows.ToArray())
					End If
					lstRows.Clear()
				Next i
			End Using
			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 IronXL;

Imports IronXL

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)
        {
            string strPath = @"c:\temp\NPOI_Test.XLSX";
            WorkBook workbook = WorkBook.Load(strPath);
            WorkSheet sheet = workbook.DefaultWorkSheet;
            var dtTable = sheet.ToDataTable(true);
            dataGridView1.DataSource = dtTable;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string strPath = @"c:\temp\NPOI_Test.XLSX";
            WorkBook workbook = WorkBook.Load(strPath);
            WorkSheet sheet = workbook.DefaultWorkSheet;
            var dtTable = sheet.ToDataTable(true);
            dataGridView1.DataSource = dtTable;
        }

Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs)
			Dim strPath As String = "c:\temp\NPOI_Test.XLSX"
			Dim workbook As WorkBook = WorkBook.Load(strPath)
			Dim sheet As WorkSheet = workbook.DefaultWorkSheet
			Dim dtTable = sheet.ToDataTable(True)
			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 is an investment that pays off by reducing time and effort needed to understand what the code allows 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