A Comparison between IronXL and GemBox.Spreadsheet

The ability to work with Excel files to generate reports and build databases has become essential for today's software applications. There are many libraries available that allow users to do this without the need for Microsoft Excel.

In this article, we are going to discuss how to work with Microsoft Excel documents programmatically in C#.NET using two of the most popular Excel Spreadsheet libraries: IronXL and GemBox.Spreadsheet.

IronXL and GemBox.Spreadsheet both provide methods to create, edit, and read Excel documents in .NET applications. So, how do you decide which one is best suited to your project? This article will compare both libraries and help you decide on the best option for your projects.

IronXL Library

IronXL is a .NET C# library that facilitates reading and editing a wide range of spreadsheet formats. It does not require Microsoft Excel to be installed and it does not require Interop. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.

IronXL Feature Set

• Loading, reading, and editing data — from XLS/XLSX/CSV/TSV
• Exporting and saving in different formats — to XLS/XLSX/CSV/TSV/JSON
• System.Data Objects — it works with Excel WorkBook Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
• Works with Formulas — works with Excel formulas. Formulas are recalculated every time a sheet is edited.
• Works with Ranges — its easy-to-use WorkSheet syntax allows you to create and combine ranges intuitively.
• Filtering and Sorting — it can sort ranges, columns, and rows.
• Styling Cells — visual styles, font, size, background pattern, border, alignment, and number formats.

GemBox.Spreadsheet

GemBox.Spreadsheet is a library that allows you to create, read, write, edit, convert, and print spreadsheet files in your .NET C# applications. It’s up to 250 times faster than Microsoft Excel automation!

GemBox.Spreadsheet API Features

• Read Excel files (XLSX, XLS, ODS), text files (CSV, TXT), and HTML files.
• Create spreadsheets and convert them to XLSX, XLS, ODS, CSV, TXT, HTML, PDF, PDF/A, XPS, and image formats.
• View and edit spreadsheets in WPF, ASP.NET, ASP.NET Core, and Windows.Forms applications.
• Apply cell formulas using its calculation engine.
• Create and edit sheets, rows, columns, cells, formulas, images, charts, shapes, text boxes, tables, cell styles and formatting, and pivot tables.
• Manipulate headers, footers, and document properties.
• Edit cell groups, data outlining, data validation, sheet protection, and print/view options.
• Import and export spreadsheet data from/to a System.Data.DataSet and System.Data.DataTable.
• Print Excel spreadsheets.

The rest of this article continues as follows:

1. Create a Console Application
2. IronXL C# Library Installation
3. GemBox.Spreadsheet Installation
4. Create a New Excel Workbook
5. Reading Excel Files
6. Working with Excel Formulas
7. Inter Convert Files
8. Licensing
9. Conclusion

1. Create a Console Application

The following steps will help you create a console application:

• Download and install the latest Visual Studio IDE.
• Start Visual Studio and click on “Create new project.”

• Then, select Console App (.NET Framework) C# from the project templates displayed.

  

  Console Application

• Click Next.

• Here, specify the Framework version you would like to use. The latest one is recommended.

  

  Configure Project

• Click on Create to complete the process.

Now the project will be created and made ready to use.

2. IronXL C# Library Installation

IronXL library can be downloaded and installed using the following methods:

1. By using Visual Studio with NuGet Package Manager.
2. Download and install with the DLL Manually.

2.1. Visual Studio with NuGet Package Manager

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

Package Manager

• In the Browse tab, search for "IronXL.Excel" and click on the Install button to add the library.
• After this process has been completed, IronXL has been successfully installed.

2.2. Download and Install with the DLL Manually

Another method to download and install IronXL Library is to use the NuGet Package Manager Console.

• Open the Developer Command Prompt console.

Type in the following command:

Install-Package IronXL.Excel

• Press the Enter key. This will download and install the package in your project automatically.

2.4. Include the IronXL Library

1. Open the Program.cs file.
2. Add the following line of code to the top of the code file:

using IronXL;

using IronXL;

3. GemBox.Spreadsheet Installation

Use the following command in NuGet Package Manager to install GemBox.Spreadsheet.

Install-Package GemBox.Spreadsheet

Search GemBox.Spreadsheet

Alternatively, you can download and run the setup installer.

Next, open the program.cs file as before and add the lines of code below the using statement that we added in the previous section:

using GemBox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

using GemBox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

4. Create and Save a New Excel Workbook

An Excel Workbook can contain multiple worksheets. Both libraries provide the facility to create Excel workbooks containing one or more worksheets. Let's have a look at how each library accomplishes this.

4.1. New Excel Workbook and Sheets using IronXL

It is very easy to create a new Excel Workbook using IronXL!

Add the following code to your Program.cs file:

var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("IronXL Features");

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

var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("IronXL Features");

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

4.2. New Excel Workbook and Sheets using GemBox.Spreadsheet

Creating Excel Workbooks is also very easy in GemBox, as shown in the following code snippet:

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

// Create a new worksheet and set cell A1 value to 'Hello world!'.
workbook.Worksheets.Add("Sheet 1").Cells["A1"].Value = "Hello world!";

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

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

// Create a new worksheet and set cell A1 value to 'Hello world!'.
workbook.Worksheets.Add("Sheet 1").Cells["A1"].Value = "Hello world!";

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

5. Reading Excel Files

IronXL and GemBox are both capable of reading data from existing Excel files. Let's look at the sample code for each of the libraries one by one.

5.1. Reading Excel Files using IronXL

The WorkBook class in IronXL represents an Excel sheet. To open and read an Excel file in C#, we use WorkBook.Load and specify the exact path of the Excel file (.xlsx).

The following code loads a spreadsheet:

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

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

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

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

We read data from each cell of the worksheet with the next lines of code:

// 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);
}

5.2. Read Excel Files using GemBox.Spreadsheet

GemBox.Spreadsheet allows for quick reading of Excel files from your C# application. The following code illustrates how to open and read a file.

var workbook = ExcelFile.Load("SimpleTemplate.xlsx");
var worksheet = workbook.Worksheets["sheetnamegoeshere"];

foreach (var cell in worksheet.Cells.GetSubrange("A2", "A20"))
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.Name, cell.Value);
}

var workbook = ExcelFile.Load("SimpleTemplate.xlsx");
var worksheet = workbook.Worksheets["sheetnamegoeshere"];

foreach (var cell in worksheet.Cells.GetSubrange("A2", "A20"))
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.Name, cell.Value);
}

6. Working with Excel Formulas

Implementing formulas is one of the most important Excel features. Both libraries provide a powerful formula calculation engine. Formulas can be recalculated and saved to cells.

6.1. Working with Excel Formulas using IronXL

After the workbook and worksheet are loaded, the code below can be used to either make changes to formulas or apply new formulas to specific cells:

// Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate 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 recalculate 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"
var formulaValue = worksheet["G30"].Value;

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

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

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

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

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

6.2. Working with Excel Formulas using GemBox.Spreadsheet

GemBox.Spreadsheet provides the facility for a large set of Excel formula functions, including Mathematical, Statistical, Logical, Lookup, Financial, and many more. Let's look at how to use them.

// Set Formulas 
worksheet.Cells["A2"].Value = "=1 + 1";
worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
worksheet.Cells["A10"].Value = "=SIGN(B9)";
worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

workbook.Save("Formula Calculation.xlsx");

// Set Formulas 
worksheet.Cells["A2"].Value = "=1 + 1";
worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
worksheet.Cells["A10"].Value = "=SIGN(B9)";
worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

workbook.Save("Formula Calculation.xlsx");

7. Inter Convert Files — (Excel, XLS, XLSX, XLSb, XLSM, XPS Data Interchange)

Both IronXL and GemBox.Spreadsheet provide the facility to convert between different spreadsheet file types.

7.1. Convert Spreadsheet Types using IronXL

CSV to Excel Formats

var workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
var ws = workbook.DefaultWorkSheet;

workbook.SaveAs("CsvToExcelConversion.xlsx");

var workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
var ws = workbook.DefaultWorkSheet;

workbook.SaveAs("CsvToExcelConversion.xlsx");

XLSX file to XLSM

var workbook = WorkBook.Load("test.xlsx");

// This is how you can export the workbook to .xlsm format
workbook.SaveAs("test.xlsm");

var workbook = WorkBook.Load("test.xlsx");

// This is how you can export the workbook to .xlsm format
workbook.SaveAs("test.xlsm");

Excel to HTML with options

var workbook = WorkBook.Load("test.xlsx");

var options = new HtmlExportOptions()
{
    // This is how we can make row/column numbers visible in the HTML document
    OutputRowNumbers = true,
    OutputColumnHeaders = true,
    OutputHiddenColumns = true,

    // This is how we can make hidden rows/columns visible in the HTML document
    OutputHiddenRows = true,
    OutputLeadingSpacesAsNonBreaking = true
};

// This is how we can export the workbook to the HTML file
workbook.ExportToHtml("workbook.html", options);

var workbook = WorkBook.Load("test.xlsx");

var options = new HtmlExportOptions()
{
    // This is how we can make row/column numbers visible in the HTML document
    OutputRowNumbers = true,
    OutputColumnHeaders = true,
    OutputHiddenColumns = true,

    // This is how we can make hidden rows/columns visible in the HTML document
    OutputHiddenRows = true,
    OutputLeadingSpacesAsNonBreaking = true
};

// This is how we can export the workbook to the HTML file
workbook.ExportToHtml("workbook.html", options);

7.2. Convert Spreadsheet Types using GemBox.Spreadsheet

XLSX file to PDF

var workbook = ExcelFile.Load("ComplexTemplate.xlsx");
workbook.Save("Convert.pdf", new PdfSaveOptions() { SelectionType = SelectionType.EntireFile });

var workbook = ExcelFile.Load("ComplexTemplate.xlsx");
workbook.Save("Convert.pdf", new PdfSaveOptions() { SelectionType = SelectionType.EntireFile });

XLSX file to images

// Load an Excel file into the ExcelFile object.
var workbook = ExcelFile.Load("CombinedTemplate.xlsx");

// Create image save options.
var imageOptions = new ImageSaveOptions(ImageSaveFormat.Png)
{
    PageNumber = 0, // Select the first Excel page.
    Width = 1240, // Set the image width.
    CropToContent = true // Export only the sheet's content.
};

// Save the ExcelFile object to a PNG file.
workbook.Save("Output.png", imageOptions);

// Load an Excel file into the ExcelFile object.
var workbook = ExcelFile.Load("CombinedTemplate.xlsx");

// Create image save options.
var imageOptions = new ImageSaveOptions(ImageSaveFormat.Png)
{
    PageNumber = 0, // Select the first Excel page.
    Width = 1240, // Set the image width.
    CropToContent = true // Export only the sheet's content.
};

// Save the ExcelFile object to a PNG file.
workbook.Save("Output.png", imageOptions);

CSV file to Excel

ExcelFile.Load("CSVTemplate.csv").Save("Convert.xlsx");

ExcelFile.Load("CSVTemplate.csv").Save("Convert.xlsx");

Similarly, you can convert between XLS, XLSX, ODS, CSV, and HTML files from your C# applications using GemBox.Spreadsheet.

8. Licensing

IronXL is a commercial C# Excel library. It is absolutely free for development purposes, but it must be licensed for commercial deployment. All IronXL Licenses are available for use with a single project, single developers, agencies, and global corporations, with or without SaaS and OEM redistribution. Each license includes a 30-day money-back guarantee, along with one year of product support and updates, validity for dev/staging/production, and a perpetual license (one-time purchase). The lite license starts at $749.

GemBox.Spreadsheet is also a commercial library. It can be used for free in both personal and commercial applications. However, it can also be licensed for professional use. Its licenses include usage for individual developers, small teams, and large companies. It provides royalty-free deployment (no server or OEM licenses), 12 months of free technical support, and free bug fixes and new releases. The single-developer package starts from $890.

9. Which .NET Spreadsheet Library Should Be Used?

This article has demonstrated how easy both libraries make it for their users to load, read, edit, and convert Microsoft Excel spreadsheets. Both IronXL and GemBox.Spreadsheet provide uniquely powerful approaches for accomplishing these activities, and are compelling choices in their own right for doing very general read and write automation tasks.

So, which .NET Excel library should developers use in their projects?

Here are some points that readers can consider when choosing between using IronXL or GemBox.Spreadsheet for their next big project:

1. Accessibility. IronXL prioritizes ease of use, accuracy, and speed. The library has been designed to enable users to accomplish common Excel programming tasks (loading, reading, writing, editing, range selection, etc.) quickly, and in the fewest lines of code possible. The website has a large quantity of tutorials, How-To Guides, and Code Examples written to help users get started and accomplish their big goals as quickly and as effortlessly as possible. Moreover, IronXL has a team of engineers on-call 24/5 to help users at a moment's notice with any problem they have.

   Developers may consider using IronXL if support, user-friendliness, and speed of implementation are of utmost importance.

   To note, GemBox.Spreadsheet's website also has a collection of detailed examples available to help developers make the most of their powerful feature sets.

2. Versatility. GemBox.Spreadsheet’s feature set enables developers to accomplish Excel automation tasks that go far beyond the basic ones discussed in this tutorial. Users can draw shapes on Excel worksheets, edit worksheets with custom fonts, create new worksheets with VBA macros, form controls, and chart components, and save Excel documents as PDFs with encryption and digital signatures, among other things. GemBox.Spreadsheet supports over 200 different formulas. In addition, the library has very sophisticated mechanisms for referencing cell ranges.

   GemBox.Spreadsheet is a compelling choice for developers who demand flexibility and power in their spreadsheet management tasks.

3. Interoperability. GemBox.Spreadsheet can work with Excel, LibreOffice, and Open Office spreadsheets, supporting or preserving most of these products’ unique features. For this reason, developers who need spreadsheets to function across these office products may consider GemBox.Spreadsheet as an attractive alternative.

4. Convertibility. IronXL can export data into many conventional open formats (XML, HTML, JSON, CSV, TSV), as well as into any Excel document type. IronXL can also convert spreadsheet data into binary, byte, and stream data, and to System.Data data structures.

   GemBox.Spreadsheet can also convert spreadsheet data to and from System.Data.DataSet and the System.Data.DataTable data types. In addition, the library can import and export as HTML, and can convert data into images and to PDF/A-compliant documents.

   The developer’s choice between both libraries in this case will depend on the data/file types required in his project.

5. Affordability. IronXL and GemBox.Spreadsheet both require premium licenses to be used commercially.

   The deciding factor between IronXL and GemBox.Spreadsheet as far as price is concerned will ultimately come down to budget, team sizes, and long-term development considerations. It is worth noting, however, that IronXL's starting $749 licensing price could be very attractive for developers and teams who are price sensitive.

Try IronXL's free trial to see the efficiency of IronXL. Purchase the complete Iron Suite to get four additional libraries for the price of just two IronXL licenses.