Published December 2, 2021
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.
How to Use NPOI for Excel in C#
- Install C# library to read Excel with NPOI
- Instantiate XSSFWorkbook object to store Excel file
- Get specific worksheet with
GetSheetAtmethod - Access each row by passing row index to
GetRowmethod - Access each cell in the row with
GetCellmethod
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
| NPOI | IronXL |
|---|---|
| Cell Ranges | Cell Ranges |
| Cell styling (Border, Color, Fill, Font, Number, Alignments) | Cell visual styles Font, Size, Background pattern, Border, Alignment and Number formats. |
| Formula calculation | Formulas |
| Data Validation | Data Validation |
| Conditional formatting | Conditional formatting |
| Images | Images |
| Charts | Charts |
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.UserModelThe 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 SubReading 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 IronXLNotice 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 SubAs 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:
