Reading Excel Files with ClosedXML: A Comparison with IronXL

Introduction

Microsoft Excel is a computer program with rich features that is used to organize, analyze, and display large data. Using it, one can design workbooks made up of rows and columns where numbers can be entered, calculated, and formatted. Excel features formula and function capabilities as well as robust data analysis features like PivotTables, charts, and conditional formatting, making it suitable for straightforward operations like budgeting and advanced operations like statistical analysis or business forecasting.

Excel features integration with other Microsoft Office applications so that sharing of data is simple and collaboration can take place. Excel offers desktop, web, and mobile deployment, which is simple and flexible.

How to use ClosedXML to read an Excel file?

• Create a new console project or any .NET project.
• Install the ClosedXML library.
• Load the existing Excel workbook and select the worksheet.
• Select the cell data from the selected worksheet.
• Display the value on the console or any other.

What is ClosedXML?

ClosedXML is an open-source .NET library that provides a simple way to create Excel reports, read, and write Excel files without installing Microsoft Office. ClosedXML is built on top of the Open XML SDK and features a high-level, easy-to-use API for programming .xlsx files.

ClosedXML enables developers to work with worksheets, cell formatting, formulas, charts, and tables. ClosedXML is also one of the libraries used by .NET and C# developers due to the ease with which it makes handling thread-safe Excel files.

Features of ClosedXML

Excel File Creation and Editing

ClosedXML enables developers to create new .xlsx files easily or modify existing ones without having Microsoft Excel installed. It is based on the OpenXML standard, which means it is light and cross-platform supported.

Worksheet Management

You can insert, rename, or remove worksheets in an Excel workbook. ClosedXML also supports copying worksheets, controlling their visibility, and handling more than one worksheet at a time.

Cell Formatting

ClosedXML allows rich formatting capabilities, such as font styles, colors, borders, number formats, cell alignment, and conditional formatting on the created Excel sheet.

Formulas and Calculations

Formulas can be inserted directly within cells by developers. Although ClosedXML does not calculate formulas, Excel will do so when opened, making it effective for reading Excel data.

Tables and Ranges

ClosedXML enables us to create range data as a table. This improves data management, supports structured references in formulas, and enhances the manipulation of data.

Data Import and Export

It simplifies importing big data from many different sources (such as databases or CSV files, or macro-enabled template files ) into Excel and exporting data from Excel into your application.

Styles and Themes

You can use consistent styles and themes for workbooks or worksheets to allow for a similar presentation in business Excel reports and dashboards, utilizing a command format.

Install Closedxml

Enter the code below on the package manager console to install the ClosedXML package.

Install-Package ClosedXML

Or we can search from the NuGet package manager like below. Then we can select the required package to insert.

Create a New Excel using the ClosedXML library

In this example, we are reading data from the sample sheet.

Below is the sample code to get the data from the Excel sheet.

using ClosedXML.Excel;
using (var workbook = new XLWorkbook("sample.xlsx"))
{
    // Access the first worksheet
    var worksheet = workbook.Worksheet(1);

    // Loop through rows and cells
    foreach (var row in worksheet.RowsUsed())
    {
        foreach (var cell in row.CellsUsed())
        {
            Console.Write(cell.Value.ToString() + "\t");
        }
        Console.Write("\n");
    }
}
Console.ReadKey();

The above C# code employs the ClosedXML library to read and display the contents of an Excel file. The code starts by importing the ClosedXML library. Then we are creating an object for the XLWorkbook class, which opens the target Excel file. Within the using block, it accesses the first worksheet in the workbook by passing the worksheet index as a parameter.

The value of each cell is translated into a string and output to the console, tab-spaced so that the data remains lined up like a table. Once a row is completed, a newline character is written to go to the next line, so each Excel row will display on a new console line.

What is IronXL?

IronXL is a .NET high-speed library for exporting, reading, editing, writing, and generating Excel spreadsheets (XLS, XLSX) and saving csv or TSV files without the use of Microsoft Office or Interop library. IronXL allows programmers to easily work with spreadsheet data like styles, pictures, charts, formulas, and styles.

In addition, reading and writing custom class objects are also provided in IronXL. Because it can import and export data in a variety of forms, such as CSV, JSON, XML, and HTML, it is the ideal option for data automation, problem repairs, and reporting solutions. It supports features like native DataTable and DataSet compatibility, password security, and formula auto-calculation.

IronXL works with cross-platform applications in environments like Windows, macOS, Linux, and Docker, and it supports the .NET Framework and .NET Core with C#, VB.NET, and F#.

Features of IronXL

Excel file Creation and manipulation

IronXL allows developers to read, edit, and create Excel files (.xls, .xlsx, and .csv) using .NET applications without installing Microsoft Excel. It provides an easy and developer-friendly API to manipulate worksheets, making it a preferred library for reading Excel files.

Support for Multiple Excel Formats

IronXL provides support for different Excel file formats like XLS, XLSX, XLSM, CSV, and TSV to support compatibility with new and older Excel files.

Support EXCEL Formula

IronXL accommodates all Excel formulas and functions. It can calculate results within your .NET application. Thus, it's effective in data calculation and dynamic reports.

Data Formatting and Styling

Developers can utilize various formatting elements, such as bold, colors, borders, alignment, number formats, and background styles, to enhance visually appealing spreadsheets.

Chart and Image Insertion

IronXL provides support for inserting charts, images, and shapes onto Excel sheets, making it possible for developers to create professionally appearing, presentation-grade documents.

Install IronXL

Use the code below on the Package Manager console to install the IronXL package.

Install-Package IronXL.Excel

We can also install the library from nuget package manager. The associated IronXL search result from the NuGet package manager is displayed in the image below. On the chosen project, we may choose the required package to be installed.

Excel Creation using IronXL

Below is the sample Excel, which is going to be read using IronXL

Below is the sample code to read an Excel file.

using IronXL;

WorkBook workbook = WorkBook.Load("sample.xlsx");

// Access the first worksheet
WorkSheet sheet = workbook.WorkSheets.First();

// Loop through rows and columns
foreach (var row in sheet.Rows)
{
    foreach (var cell in row.Columns)
    {
        Console.Write(cell.Value.ToString() + "\t");
    }
    Console.WriteLine();
}

Console.ReadKey();

The above C# programming example demonstrates how to read and output data from an Excel file via the IronXL library. The example starts by importing the namespace into the code. Then, an Excel workbook is loaded into the workbook object method, which loads the workbook into memory. It then gets the first sheet of the workbook with the help of the pre-built method.

Finally, the program loops over every row in the sheet using a foreach loop, and then for each row, it loops over the individual cells. The contents of each cell are accessed by using the Text property, and output can be displayed in the console.

About Licencing

Closedxml

ClosedXML is an open-source .NET library licensed under the MIT License, one of the most permissive and developer-friendly licenses available. This allows developers to use, redistribute, and modify the library for commercial and personal applications free of charge without paying a licensing fee.

The MIT License also permits embedding into proprietary software, making ClosedXML a viable choice for organizations that like to automate Excel file processing. But though the license is highly flexible, it is not warranted or supported officially.

IronXL
IronXL operates in a commercial or production environment using a license. The IronXL license also comprises updates and support access, depending on the purchased plan.

This commercial package of license provides enterprise- and professional-grade uses compliance, full access, and priority technical support.

Conclusion

In short, ClosedXML and IronXL are the Excel libraries for .NET applications. ClosedXML is an open-source free software library under the MIT License, and it is most suitable for developers and small-scale applications requiring normal Excel operations like reading, writing, and data formatting, irrespective of file extension. But then there is IronXL, a commercial library that has features such as formula support, image and chart inserting, generating PDF from Excel, and enterprise-level technical support.

Whereas ClosedXML is well-suited for low-cost, open-source environments, IronXL is notable for being the first library to provide enterprise-level performance, stability, and feature set for tasks such as writing out an Excel document, and it is the better option for business and production environments. To learn more about the other Iron Software products, check here.