A Comparison of IronXL and ClosedXML

Today's software applications require the ability to create databases and generate reports using Excel files. There are many libraries available today that allow users to do this without the need for Microsoft Excel.

In this article, we are going to discuss and compare how to work with Microsoft Excel documents programmatically in C# .NET technologies using two of the most popular libraries, IronXL and ClosedXML library for reading, manipulating and writing excel reports.

IronXL and ClosedXML both provide methods to create, edit, and read Excel documents in the .NET framework. The next question is to decide which C# Excel library is best suited to your project. This article will help you decide on the best option for your applications.

Let's look first at what both libraries have to offer, and then move on to the comparison itself.

The IronXL Library

IronXL is a .NET library that facilitates reading and editing Microsoft Excel documents with C#. IronXL.Excel is a standalone .NET software library for reading a wide range of spreadsheet formats. It does not require that Microsoft Excel be installed, nor does it depend on Interop.

IronXL is an intuitive C# API that allows you to read, edit and create Excel spreadsheet files in .NET with lightning-fast performance. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS and Azure.

IronXL is a leading .NET Core and .NET Framework Excel spreadsheet library for C#.

IronXL Feature Set

• Load, read and edit data --- from XLS/XLSX/CSV/TSV
• Saving and exporting --- to XLS/XLSX/CSV/TSV/JSON
• System.Data Objects --- work with Excel Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
• Formulas --- works with Excel formulas. Formulas are recalculated every time a sheet is edited.
• Ranges --- easy to use WorkSheet ["A1:B10"] syntax. Combine and create ranges intuitively.
• Sorting --- sort ranges, columns, and rows.
• Styling --- cell visual styles, font, size, background pattern, border, alignment and number formats.

More Features of IronXL can be explored using this link.

ClosedXML

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface for dealing with the underlying OpenXML Library. The .xlsx is a file extension for an OpenXML underlying API spreadsheet file format used by Microsoft Excel. The .xlsm files support macros. The .xltm are macro-enabled template files. The .xls format is a proprietary binary format while .xlsx is based on Office OpenXML format.

ClosedXML is a .NET library for report generation in Microsoft Excel without requiring Excel to be installed on the machine that's running the code.

ClosedXML Features

ClosedXML has user friendly interface with extensive API functions to handle creation & extraction of content in Excel. With these API calls and pull request, you can modify every little detail in Excel sheet or workbook.\ All the features are listed below:

1. Formulas
2. Validation
3. Hyper-Links
4. Protection (Sheet and Cell level)
5. Conditional Formatting
6. Freeze Panes
7. Tables
8. Ranges
9. Styling
10. Page Setup (Printing)
11. Auto-Filters
12. Comments

The rest of this article continues as follows:

1. Create a Console Application
2. IronXL C# Library Installation
3. ClosedXML Installation
4. Create and Save a new Excel Workbook and Sheet
5. Read Excel File
6. Working with Excel Formulas
7. Licensing
8. Summary and Conclusion

1. Create a Console Application

Use the following steps to create a Console Application:

• Start the Visual Studio 2022 IDE.
• Click on "Create a new project."
• On the "Create a new project" page, select C# in the language dropdown list, Windows from the Platforms list, and Console from the "Project types" list.
• Select Console App (.NET Framework) from the project templates displayed.

Create a Project

• Click Next.
• Name the project "DemoApp" and Click Next.

DemoApp Project

• In the Additional Information screen, specify the Framework version you would like to use. We will use .NET Framework 6.0 in this example.

.NET Framework 6.0

• Click Create to complete the process.

Now the project is created and we are almost ready to test the libraries. However, we still need to install and integrate them into our project. Let's install IronXL first.

2. IronXL C# Library Installation

You can download and install the IronXL library using the following methods:

1. Using Visual Studio with NuGet packages.
2. Manually installing the DLL.

Let's take a closer look at each one.

2.1. Using Visual Studio with NuGet Packages

Visual Studio provides the NuGet Package Manager to install NuGet packages in your projects. You can access it through the Project Menu, or by right-clicking your project in the Solution Explorer.

Package Manager

• Next, from the Browse tab > search for IronXL.Excel > Install

IronXL NuGet Install

• And we are done.

2.2. Manually Installing the DLL

Another way to download and install the IronXL C# Library is to make use of the following steps to install the IronXL NuGet package through the Developer Command Prompt.

• Open the Developer Command Prompt --- usually found in the Visual Studio folder.

Type the following command:

Install-Package IronXL.Excel

• Press Enter.
• This will download and install the package.
• Reload your Visual Studio project and begin using it.

2.4. Add Necessary Using Directives

1. In Solution Explorer, right-click the Program.cs file and then click View Code.
2. Add the following using directives to the top of the code file:

using IronXL;

using IronXL;

Imports IronXL

All done! IronXL is downloaded, installed, and ready to use. However, before that, we should install ClosedXML.

3. ClosedXML Installation

To install package ClosedXML, you can directly download it from NuGet or from NuGet Package Manager/Console in the project.

For directly installing ClosedXML, click on this link.

3.1. Using the NuGet Package Manager/ Console

Open the NuGet Package Manager from project solution explorer and browse ClosedXML and click install.

ClosedXML NuGet Install

Or:

• Open the Developer Command Prompt --- usually found in the Visual Studio folder.

Type the following command:

PM> Install-Package ClosedXML

• Press Enter.
• This will download and install the package.
• Reload your Visual Studio project and begin using it.

3.2. Add Necessary Using Directives

1. In Solution Explorer, right-click the Program.cs file and then click View Code.
2. Add the following using directives to the top of the code file:

using ClosedXML.Excel;

using ClosedXML.Excel;

Imports ClosedXML.Excel

4. Create and Save a New Excel Workbook and Sheets

A workbook is an Excel file containing multiple worksheets with rows and columns. Both the libraries provide the facility to create a new Excel workbook and sheets. Let's have a look at the code step-by-step.

4.1. New Excel Workbook and Sheets using IronXL

It could not be any easier to create a new Excel Workbook using IronXL. It takes just one line of code. Yes, really. Add the following code to your static void main function in the Program.cs file:

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

Both XLS (older Excel file version) and XLSX (current and newer file version) file formats can be created with IronXL.

And, it's even simpler to create a default Worksheet:

var worksheet = workbook.CreateWorkSheet("IronXL Features");

var worksheet = workbook.CreateWorkSheet("IronXL Features");

Dim worksheet = workbook.CreateWorkSheet("IronXL Features")

You can now use the worksheet variable to set cell values and do almost everything an Excel file can do.

//Add data and styles to the new worksheet
worksheet ["A1"].Value = "Hello World";
worksheet ["A2"].Style.BottomBorder.SetColor("#ff6600");
worksheet ["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;

//Add data and styles to the new worksheet
worksheet ["A1"].Value = "Hello World";
worksheet ["A2"].Style.BottomBorder.SetColor("#ff6600");
worksheet ["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;

'Add data and styles to the new worksheet
worksheet ("A1").Value = "Hello World"
worksheet ("A2").Style.BottomBorder.SetColor("#ff6600")
worksheet ("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Double

Save Excel file:

//Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx");

//Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx");

'Save spreadsheet
workbook.SaveAs("NewExcelFile.xlsx")

The output file looks as follows:

NewExcelFile output

4.2. New Excel Workbook and Sheets using ClosedXML

You can also create excel files(.xlsx, .xlsm) easily using ClosedXML. The following code is a typical example which serves to generate a simple Excel file and save it. You can add new sample sheet to your workbook and assign values to cells in your excel application.

// Create a new empty Excel file.
var workbook = new XLWorkbook();

// Create a new worksheet and set cell A1 value to 'Hello world!'.
var worksheet = workbook.Worksheets.Add("ClosedXML Features");
worksheet.Cell("A1").Value = "Hello world!";

// Save to XLSX file.
workbook.SaveAs("Spreadsheet.xlsx");

// Create a new empty Excel file.
var workbook = new XLWorkbook();

// Create a new worksheet and set cell A1 value to 'Hello world!'.
var worksheet = workbook.Worksheets.Add("ClosedXML Features");
worksheet.Cell("A1").Value = "Hello world!";

// Save to XLSX file.
workbook.SaveAs("Spreadsheet.xlsx");

' Create a new empty Excel file.
Dim workbook = New XLWorkbook()

' Create a new worksheet and set cell A1 value to 'Hello world!'.
Dim worksheet = workbook.Worksheets.Add("ClosedXML Features")
worksheet.Cell("A1").Value = "Hello world!"

' Save to XLSX file.
workbook.SaveAs("Spreadsheet.xlsx")

The output file looks as follows:

Spreadsheet File Output

5. Read Excel File (Import Excel File)

Both the libraries can open and read existing Excel documents. Let's have a look at the sample code.

5.1. Read Excel Files using IronXL

The IronXL WorkBook class represents an Excel sheet. To open an Excel file using C#, we use WorkBook.Load and specify the path of the file (.xlsx). The following one-line code is used to open the file for reading:

//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");

//Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets\\sample.xlsx");

'Load WorkBook
Dim workbook = WorkBook.Load("Spreadsheets\\sample.xlsx")

Each WorkBook can have multiple worksheets in the Excel document. If the workbook contains multiple worksheets, retrieve them by name as follows:

//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

//Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

'Open Sheet for reading
Dim worksheet = workbook.GetWorkSheet("sheetnamegoeshere")

Code for reading the cell values:

// Read from Ranges of cells elegantly.
foreach (var cell in worksheet ["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Read from Ranges of cells elegantly.
foreach (var cell in worksheet ["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

' Read from Ranges of cells elegantly.
For Each cell In worksheet ("A2:A10")
	Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell

5.2. Read Excel Files using ClosedXML

ClosedXML allows reading of previously created files from your C# application. You can import excel file using the following code.

// Import Excel File from file's path.
var workbook = new XLWorkbook("SimpleTemplate.xlsx");

// Read worksheet from workbook using sheet number.
var worksheet1 = workbook.Worksheet(1);

// Read cell value from first sheet
var data = worksheet1.Cell("A1").GetValue<string>();

// Display on screen
Console.WriteLine(data);

// Import Excel File from file's path.
var workbook = new XLWorkbook("SimpleTemplate.xlsx");

// Read worksheet from workbook using sheet number.
var worksheet1 = workbook.Worksheet(1);

// Read cell value from first sheet
var data = worksheet1.Cell("A1").GetValue<string>();

// Display on screen
Console.WriteLine(data);

' Import Excel File from file's path.
Dim workbook = New XLWorkbook("SimpleTemplate.xlsx")

' Read worksheet from workbook using sheet number.
Dim worksheet1 = workbook.Worksheet(1)

' Read cell value from first sheet
Dim data = worksheet1.Cell("A1").GetValue(Of String)()

' Display on screen
Console.WriteLine(data)

6. Working with Excel Formulas

Excel formulas are one of the most important features of working with Excel. Both the libraries have a powerful formula calculation engine. They provide the facility to work with formulas and easily apply them to cells.

6.1. Working with Excel Formulas using IronXL

After loading the workbook and worksheet, the following code sample can be used to either make changes to formulas, or be applied to specific cells. The code goes as follows:

// Set Formulas
worksheet ["A1"].Formula = "=Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "=Max(C3:C7)";
 .
// Force recalculating all formula values in all sheets.
workbook.EvaluateAll();

// Set Formulas
worksheet ["A1"].Formula = "=Sum(B8:C12)";
worksheet ["B8"].Formula = "=C9/C11";
worksheet ["G30"].Formula = "=Max(C3:C7)";
 .
// Force recalculating all formula values in all sheets.
workbook.EvaluateAll();

' Set Formulas
worksheet ("A1").Formula = "=Sum(B8:C12)"
worksheet ("B8").Formula = "=C9/C11"
worksheet ("G30").Formula = "=Max(C3:C7)"
'.
' Force recalculating all formula values in all sheets.
workbook.EvaluateAll()

You can also retrieve formulas and their values:

// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet ["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet ["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

// Get the formula's calculated value.  e.g. "52"
string formulaValue = worksheet ["G30"].Value;

//Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = worksheet ["G30"].Formula;

//Save your changes with updated formulas and calculated values.
workbook.Save();

' Get the formula's calculated value.  e.g. "52"
Dim formulaValue As String = worksheet ("G30").Value

'Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = worksheet ("G30").Formula

'Save your changes with updated formulas and calculated values.
workbook.Save()

6.2. Working with Excel Formulas using ClosedXML

ClosedXML does not support all formulas and you'll probably get a nasty error if the formula isn't supported or if there's an error in the formula. Please test your formulas before going to production. Lets have a look at how to use them.

// Set Formulas
worksheet.Cell("A1").Value = "Hello World!";
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";

// You can use Evaluate function to directly calculate formula,
var sum = worksheet.Evaluate("SUM(B1:B7)");

// Force recalculation
worksheet.RecalculateAllFormulas();

// Save Excel file
workbook.SaveAs("Formula Calculation.xlsx");

// Set Formulas
worksheet.Cell("A1").Value = "Hello World!";
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";

// You can use Evaluate function to directly calculate formula,
var sum = worksheet.Evaluate("SUM(B1:B7)");

// Force recalculation
worksheet.RecalculateAllFormulas();

// Save Excel file
workbook.SaveAs("Formula Calculation.xlsx");

' Set Formulas
worksheet.Cell("A1").Value = "Hello World!"
worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)"

' You can use Evaluate function to directly calculate formula,
Dim sum = worksheet.Evaluate("SUM(B1:B7)")

' Force recalculation
worksheet.RecalculateAllFormulas()

' Save Excel file
workbook.SaveAs("Formula Calculation.xlsx")

The property FormulaA1 is with reference to A1 cell. If you are referencing to some other cell you must add it to the Formula property. E.g. FormulaC2.

7. Licensing

IronXL is an openly commercial C# Excel library. It is free for development and can always be licensed for commercial deployment. Licenses are available for single-project use, single developers, agencies, and global corporations as well as SaaS and OEM redistribution. All licenses include a 30-day money-back guarantee, one year of product support and updates, validity for dev/staging/production, and also a permanent license (one-time purchase). The Lite package starts from $749.

IronXL License Packages

For ClosedXML, applications using this DLL file do not require a separate license either for single-use or commercial use. In order for any solution to work with an ClosedXML, just install the publicly available NuGet package "ClosedXML". ClosedXML is licensed under the MIT License. MIT License is a short and simple permissive license with conditions only requiring preservation of copyright and license notices. Licensed works, modifications, and larger works may be distributed under different terms and without source code.

ClosedXML License

8. Summary and Conclusion

Summary

IronXL is a complete library offering everything you need to manipulate an Excel file. IronXL allows developers to read, generate and edit Excel (and other Spreadsheet files) in .NET applications & websites. It provides a fast and natural approach to work with Excel and other Spreadsheet files in C#.

ClosedXML is a .NET library for reading and writing Excel file, which makes it easier for developers to create Excel 2007+ files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). It provides features of OpenXML but still some are not implemented. E.g. Macros, Embedding and Charts.

Conclusion

IronXL is also helpful in other excel operations like cell data formats, sorting, cell styling. It can also work with Excel Spreadsheets as System.Data.DataSet and System.Data.DataTable. It supports console, web server and desktop based applications. It is also supported on all OS platforms.

ClosedXML is a lightweight library for reading, manipulating and writing excel application using OpenXML API which is fast and easier to implement. It helps in all major excel operations like Page Setup, Freeze Panes, Hyperlinks, Tables and Conditional Formatting. It has no overhead or low performance issues.

In comparison, IronXL and ClosedXML both are fast and accurate when it comes to excel spreadsheets. They do not need Microsoft Office installation or license when it comes to creating excel reports. However, IronXL has an advantage over ClosedXML as ClosedXML is not thread-safe. IronXL is easy to use and provides the facility to convert other formats to XLSX, and also from XLSX to other formats such as CSV and HTML. ClosedXML doesn't allow this conversion. This interconversion allows users the flexibility to work with other file formats with ease.

Now you can get five Iron products for the price of just two in lite package for 1 developer($1498) and unlimited for $5,998 and save up to 60%.