A Comparison between IronXL and Epplus

In this article, we examine the similarities and differences between the IronXL and EPPlus software.

For most organizations, Microsoft Excel has proven to be an extremely useful tool. Excel files, such as databases, contain data in cells, quite simply making it easy to manage data that needs to be stored.

The.xls and.xlsx file formats are also used by Excel. The C# language can make it tough to manage Excel files. However, IronXL and EPPlus software make it easier to handle these processes.

Microsoft Office is not required when using this software.

Please note that you may read and create Excel files in C# without having to install Microsoft Office. Today, we'll look at some different options that are easy to implement.

What is EPPlus software?

EPPlus is a NuGet-based .NET Framework/.NET Core library for handling Office Open XML spreadsheets. Version 5 includes support for the .NET Framework 3.5 and.NET Core 2.0. EPPlus does not rely on any other libraries, such as Microsoft Excel.

EPPlus has an API that allows you to work with Office Excel documents. EPPlus is a.NET library that reads and writes Office OpenXML-formatted Excel files. This library is available as a package from NuGet.

The library was created with programmers in mind. The goal has always been for a developer who is familiar with Excel or another spreadsheet library to be able to quickly learn the API. Alternatively, as someone put it, "IntelliSense your way to victory!"

EPPlus Installation

To install EPPlus from Visual Studio, go to View > Other Windows > Package Manager Console and type the following command:

PM> Install-Package EPPlus

If you would rather utilize the.NET CLI, run the following command from an elevated command prompt or PowerShell prompt:

PM> dotnet add package EPPlus

EPPlus is a dotnet package that you can add to your project.

What is IronXL?

IronXL is a simple C# and VB Excel API that allows you to read, edit, and create Excel spreadsheet files in .NET at lightning speed. It is not necessary to install Microsoft Office or even Excel Interop. This library can also be used to work with Excel files.

.NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure are all supported by IronXL.

There are a number of different ways to read and write data to and from your spreadsheet.

Adding IronXL using NuGet Package

We can add the IronXL package to your account in one of three ways, so you can pick the one that works best for you.

• Using the Package Manager Console to install IronXL

Use the following command to open the Package Manager Console in your Project:

To access the Package Manager Console, go to Tools => NuGet Package Manager => Package Manager Console.

This will bring you to the Package Manager Console. Then, on the Package Manager terminal, type the following command:

PM > Install-Package IronXL.Excel

• Using the NuGet Package Manager to install IronXL

This is a different approach to getting the NuGet Package Manager installed. You won't need to utilize this approach if you've previously completed installation using the prior method.

To access the NuGet Package Manager, go to Tools > NuGet Package Manager => Select Manage NuGet Packages for Solution from the drop-down menu.

This will launch the NuGet-Solution; select "Browse" and look for IronXL.

In the search bar, type Excel:

IronXL will be installed for you when you click the "Install" button. After installing IronXL you can go to your form and begin developing it.

Creating an Excel file with IronXL

Making a new Excel Workbook with IronXL couldn't be easier! It's only a single line of code! Yes, this is true:

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

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

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

IronXL can create files in XLS (the earlier Excel file version) and XLSX (current and newer Excel file version) formats.

• Set a default worksheet

It's even easier to set up a default worksheet:

var sheet = workbook.CreateWorkSheet("2020 Budget"); The worksheet is represented by "Sheet" in the above code snippet, and you may use it to set cell values and do practically everything Excel can do. You can also code your Excel document to a read-only file and perform delete operations. You can also link your worksheets, just as Excel does.

Let me clarify the difference between a workbook and a worksheet in case you're not sure.

Worksheets are contained in a workbook. This means you can put as many worksheets inside a workbook as you want. I'll explain how to do this in a later article. Rows and columns make up a worksheet. The intersection of a row and a column is known as a cell, and it is this that you will interact within Excel.

Creating an Excel file with Epplus Software AB

EPPlus can be used to create Excel files and perform operations such as creating pivot tables, pivot areas, and even conditional formatting and the changing of fonts. Without further ado, here is the whole source code for converting a normal DataTable to an XLSX Excel file and sending it to the user for download:

public ActionResult ConvertToXLSX()
{
    byte [] fileData = null;

    // replace the GetDataTable() method with your DBMS-fetching code.
    using (DataTable dt = GetDataTable())
    {
        // create an empty spreadsheet
        using (var p = new ExcelPackage())
        {
            // add a worksheet to the spreadsheet
            ExcelWorksheet ws = p.Workbook.Worksheets.Add(dt.TableName);

            // Initialize rows and cols counter: note that they are 1-based!
            var row = 1;
            var col = 1;

            // Create the column names on the first line.
            // In this sample we'll just use the DataTable colum names
            row = 1;
            col = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                col++;
                ws.SetValue(row, col, dc.ColumnName);
            }

            // Insert the DataTable rows to the XLS file
            foreach (DataRow r in dt.Rows)
            {
                row++;
                col = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    col++;
                    ws.SetValue(row, col, r [dc].ToString());
                }

                // alternate light-gray color for uneven rows (3, 5, 7, 9)...
                if (row % 2 != 0)
                {
                    ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                }
            }

            // output the XLSX file
            using (var ms = new MemoryStream())
            {
                p.SaveAs(ms);
                ms.Seek(0, SeekOrigin.Begin);
                fileData = ms.ToArray();
            }
        }
    }

    string fileName = "ConvertedFile.xlsx";
    string contentType = System.Web.MimeMapping.GetMimeMapping(fileName);
    Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
    return File(fileData, contentType);
}

public ActionResult ConvertToXLSX()
{
    byte [] fileData = null;

    // replace the GetDataTable() method with your DBMS-fetching code.
    using (DataTable dt = GetDataTable())
    {
        // create an empty spreadsheet
        using (var p = new ExcelPackage())
        {
            // add a worksheet to the spreadsheet
            ExcelWorksheet ws = p.Workbook.Worksheets.Add(dt.TableName);

            // Initialize rows and cols counter: note that they are 1-based!
            var row = 1;
            var col = 1;

            // Create the column names on the first line.
            // In this sample we'll just use the DataTable colum names
            row = 1;
            col = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                col++;
                ws.SetValue(row, col, dc.ColumnName);
            }

            // Insert the DataTable rows to the XLS file
            foreach (DataRow r in dt.Rows)
            {
                row++;
                col = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    col++;
                    ws.SetValue(row, col, r [dc].ToString());
                }

                // alternate light-gray color for uneven rows (3, 5, 7, 9)...
                if (row % 2 != 0)
                {
                    ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                }
            }

            // output the XLSX file
            using (var ms = new MemoryStream())
            {
                p.SaveAs(ms);
                ms.Seek(0, SeekOrigin.Begin);
                fileData = ms.ToArray();
            }
        }
    }

    string fileName = "ConvertedFile.xlsx";
    string contentType = System.Web.MimeMapping.GetMimeMapping(fileName);
    Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
    return File(fileData, contentType);
}

Public Function ConvertToXLSX() As ActionResult
	Dim fileData() As Byte = Nothing

	' replace the GetDataTable() method with your DBMS-fetching code.
	Using dt As DataTable = GetDataTable()
		' create an empty spreadsheet
		Using p = New ExcelPackage()
			' add a worksheet to the spreadsheet
			Dim ws As ExcelWorksheet = p.Workbook.Worksheets.Add(dt.TableName)

			' Initialize rows and cols counter: note that they are 1-based!
			Dim row = 1
			Dim col = 1

			' Create the column names on the first line.
			' In this sample we'll just use the DataTable colum names
			row = 1
			col = 0
			For Each dc As DataColumn In dt.Columns
				col += 1
				ws.SetValue(row, col, dc.ColumnName)
			Next dc

			' Insert the DataTable rows to the XLS file
			For Each r As DataRow In dt.Rows
				row += 1
				col = 0
				For Each dc As DataColumn In dt.Columns
					col += 1
					ws.SetValue(row, col, r (dc).ToString())
				Next dc

				' alternate light-gray color for uneven rows (3, 5, 7, 9)...
				If row Mod 2 <> 0 Then
					ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid
					ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray)
				End If
			Next r

			' output the XLSX file
			Using ms = New MemoryStream()
				p.SaveAs(ms)
				ms.Seek(0, SeekOrigin.Begin)
				fileData = ms.ToArray()
			End Using
		End Using
	End Using

	Dim fileName As String = "ConvertedFile.xlsx"
	Dim contentType As String = System.Web.MimeMapping.GetMimeMapping(fileName)
	Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName))
	Return File(fileData, contentType)
End Function

As you can see, this is an ActionResult method that may be used on any ASP.NET MVC Controller; if you're not using ASP.NET MVC, simply copy the method content and paste it wherever you need it (classic ASP.NET, Console Application, Windows Forms, and so on).

The code is self-explanatory, with enough comments to help you understand the various processing processes. But first, a quick recap of what we're doing here:

• Using a custom Data Provider method, we get a DataTable object.
• We build an ExcelPackage object, which is EPPlus's primary container for the XLSX file.
• We add an ExcelWorksheet to the ExcelPackage, which will be the worksheet where the data will be entered.
• To create our header row, we iterate the DataTable columns, adding them to the first row of our worksheet.
• We iterate through the DataTable rows, adding each one to our worksheet row by row (beginning with row 2) so that each DataTable row corresponds to a worksheet row.
• We build a MemoryStream to store the ExcelPackage binary data when the DataTable to ExcelPackage conversion is complete and then convert it to a byte array.
• We create the HTML answer and send the XLSX file to the user with a Content-Disposition attachment, causing the browser to download the file automatically.

IronXL wins in this case because the process of creation is very easy — you need just one line of code and you are in; this helps to to save time and with debugging, while EPPlus offers lines of code which are boring to go through and difficult to debug.

How EPPlus Software AB writes Excel Files

EPPlus supports working with Excel files. It is a .net library that reads and writes Excel files.

• Reading Excel Files

To do so, you need to first install the package EPPlus: go to "Tools"-> "NuGet package manager"-> "Manage NuGet for this solution" -> "Install EPPlus" -> "Install EPPlus" -> "Install EPPlus" -> "Install EPPlus" -> "Install EPPlus" -> "Install EPPlus" -> Install EP Search for "EPPlus" in the "Browse" tab, then install the NuGet package.

You can use the code below in your console application "Program.cs" once you've installed the package.

using OfficeOpenXml;
using System;
using System.IO;

namespace ReadExcelInCsharp
{
    class Program
    {
        static void Main(string [] args)
        {
            //provide file path
            FileInfo existingFile = new FileInfo(@"D:\sample_XLSX.xlsx");
            //use EPPlus
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                //get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets [1];
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        //Print data, based on row and columns position
                        Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells [row, col].Value?.ToString().Trim());
                    }
                }
            }
        }
    }
}

using OfficeOpenXml;
using System;
using System.IO;

namespace ReadExcelInCsharp
{
    class Program
    {
        static void Main(string [] args)
        {
            //provide file path
            FileInfo existingFile = new FileInfo(@"D:\sample_XLSX.xlsx");
            //use EPPlus
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                //get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets [1];
                int colCount = worksheet.Dimension.End.Column;  //get Column Count
                int rowCount = worksheet.Dimension.End.Row;     //get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        //Print data, based on row and columns position
                        Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells [row, col].Value?.ToString().Trim());
                    }
                }
            }
        }
    }
}

Imports OfficeOpenXml
Imports System
Imports System.IO

Namespace ReadExcelInCsharp
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			'provide file path
			Dim existingFile As New FileInfo("D:\sample_XLSX.xlsx")
			'use EPPlus
			Using package As New ExcelPackage(existingFile)
				'get the first worksheet in the workbook
				Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets (1)
				Dim colCount As Integer = worksheet.Dimension.End.Column 'get Column Count
				Dim rowCount As Integer = worksheet.Dimension.End.Row 'get row count
				For row As Integer = 1 To rowCount
					For col As Integer = 1 To colCount
						'Print data, based on row and columns position
						Console.WriteLine(" Row:" & row & " column:" & col & " Value:" & worksheet.Cells (row, col).Value?.ToString().Trim())
					Next col
				Next row
			End Using
		End Sub
	End Class
End Namespace

Here's an example of a console application output with a sample excel file (.xlsx) that we're working with. Here is an xlsx file for reading in C# using EPPlus.

The following ways to load data from multiple sources can be accessed using the "cells" property (ExcelRange):

• Read a CSV text file and load the data into a range on a worksheet with LoadFromText and LoadFromTextAsync.
• LoadFromDataReaderAsync and LoadFromDataReader — loads data fields from a DataReader into a range.
• LoadFromDataTable — loads data from a DataTable into a range. It may import data from a variety of sources, including XML (an example is supplied) and databases.
• LoadFromCollection — reflectively loads data from an IEnumerable into a range.
• LoadFromCollection with attributes — reflectively loads data from an IEnumerable into a range or table. Styles, number formats, formulas, and other properties are specified via attributes.
• LoadFromDictionaries — loads data from an IEnumerable of ExpandoObject/dynamic objects (through their IDictionarystring, object> interface) into a range. This is useful for importing JSON data, and there is an example included.
• LoadFromArrays — loads data from an IEnumerable of object [] into a range, with each object array corresponding to a row in the worksheet.

When using these methods you can optionally give a parameter to generate an Excel table. Samples 4 and 5 of the sample project Sample-.NET Framework or Sample-.NET Framework contain more extensive examples.

• Writing Excel Files

Next, let's see whether we can export data to a new Excel file.

Here are some sample data/objects that we'd like to save as an Excel document.

List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

Dim persons As New List(Of UserDetails)() From {
	New UserDetails() With {
		.ID="9999",
		.Name="ABCD",
		.City ="City1",
		.Country="USA"
	},
	New UserDetails() With {
		.ID="8888",
		.Name="PQRS",
		.City ="City2",
		.Country="INDIA"
	},
	New UserDetails() With {
		.ID="7777",
		.Name="XYZZ",
		.City ="City3",
		.Country="CHINA"
	},
	New UserDetails() With {
		.ID="6666",
		.Name="LMNO",
		.City ="City4",
		.Country="UK"
	}
}

To create a new Excel file with the essential information, we must utilize the ExcelPackage class. Writing data to a file and producing a new Excel spreadsheet takes only a few lines of code. Please take note of the one line below that performs the magic of loading DataTables into an Excel sheet.

To keep things simple, I'm generating a new spreadsheet file in the same project folder (the Excel file will be produced in the project's 'bin' folder). The source code is below:

private static void WriteToExcel(string path)
        {
            //Let use below test data for writing it to excel
            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

            // let's convert our object data to Datatable for a simplified logic.
            // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            FileInfo filePath = new FileInfo(path);
            using (var excelPack = new ExcelPackage(filePath))
            {
                var ws = excelPack.Workbook.Worksheets.Add("WriteTest");
                ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
                excelPack.Save();
            }
        }

private static void WriteToExcel(string path)
        {
            //Let use below test data for writing it to excel
            List<UserDetails> persons = new List<UserDetails>()
            {
                new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
                new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
                new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
                new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
           };

            // let's convert our object data to Datatable for a simplified logic.
            // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
            DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
            FileInfo filePath = new FileInfo(path);
            using (var excelPack = new ExcelPackage(filePath))
            {
                var ws = excelPack.Workbook.Worksheets.Add("WriteTest");
                ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
                excelPack.Save();
            }
        }

Private Shared Sub WriteToExcel(ByVal path As String)
			'Let use below test data for writing it to excel
			Dim persons As New List(Of UserDetails)() From {
				New UserDetails() With {
					.ID="9999",
					.Name="ABCD",
					.City ="City1",
					.Country="USA"
				},
				New UserDetails() With {
					.ID="8888",
					.Name="PQRS",
					.City ="City2",
					.Country="INDIA"
				},
				New UserDetails() With {
					.ID="7777",
					.Name="XYZZ",
					.City ="City3",
					.Country="CHINA"
				},
				New UserDetails() With {
					.ID="6666",
					.Name="LMNO",
					.City ="City4",
					.Country="UK"
				}
			}

			' let's convert our object data to Datatable for a simplified logic.
			' Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
			Dim table As DataTable = CType(JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (GetType(DataTable))), DataTable)
			Dim filePath As New FileInfo(path)
			Using excelPack = New ExcelPackage(filePath)
				Dim ws = excelPack.Workbook.Worksheets.Add("WriteTest")
				ws.Cells.LoadFromDataTable(table, True, OfficeOpenXml.Table.TableStyles.Light8)
				excelPack.Save()
			End Using
End Sub

Following the aforementioned API call for data validation, a new Excel file will be created with the above custom object transformation into the appropriate Excel columns and rows, to show the value below.

The above ready-to-use API can be used in the.NET Core console, a test project, or an ASP.NET Core application, and the logic can be changed to suit your needs.

These techniques can be accessed using the "cells" property (ExcelRange):

• ToText and ToTextAsync — creates a CSV string from a range.
• Writing a range to a CSV file with SaveToText and SaveToTextAsync.
• Exporting data from a range to a System using the ToDataTable method.Data. DataTable
• GetValueT> — shows a value with the option of a datatype.
• Value — returns or sets the range's value.

The GetValue and SetValue methods can also be used directly on the worksheet object. (This will provide slightly better results than reading/writing on the range):

• GetValueT> — gets a single cell's value, with the option of specifying a datatype.
• SetValue — changes a single cell's value.

Linq may be used to query data from a worksheet because the cell property implements the IEnumerable interface.

Open and Write Office Open XML Format XLSX with IronXL

IronXL is a NET library that allows C# developers to work with Excel, pivot tables, and other spreadsheet files quickly and easily.

Office Interop isn't required. There are no particular dependencies or the need to install Microsoft Office on Core or Azure.

IronXL is a renowned C# and VB.NET xl spreadsheet library for .NET core and .NET framework.

• Reading Excel Files
• Worksheet to be loaded

An Excel sheet is represented by the WorkBook class. We utilize WorkBook to open an Excel file in C# containing even pivot tables. Load the Excel file and choose its location (.xlsx).

/**
Load WorkBook
anchor-load-a-workbook
**/
var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");

/**
Load WorkBook
anchor-load-a-workbook
**/
var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");

'''
'''Load WorkBook
'''anchor-load-a-workbook
'''*
Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")

WorkSheet objects can be found in numerous WorkBooks. These are the Excel document's worksheets. If the sheet contains worksheets, use the name WorkBook to find them. GetWorkSheet.

var worksheet = workbook.GetWorkSheet("GDPByCountry");

var worksheet = workbook.GetWorkSheet("GDPByCountry");

Dim worksheet = workbook.GetWorkSheet("GDPByCountry")

• Make your own Workbook.

Construct a new WorkBook with the sheet type to generate a new WorkBook in memory.

/**
Create WorkBook
anchor-create-a-workbook
**/
var workbook = new WorkBook(ExcelFileFormat.XLSX);

/**
Create WorkBook
anchor-create-a-workbook
**/
var workbook = new WorkBook(ExcelFileFormat.XLSX);

'''
'''Create WorkBook
'''anchor-create-a-workbook
'''*
Dim workbook As New WorkBook(ExcelFileFormat.XLSX)

For vintage Microsoft Excel spreadsheets, use ExcelFileFormat.XLS (95 and earlier).

Make a WorkSheet if you don't already have one.

There can be numerous WorkSheets in each "WorkBook." A "WorkSheet" is a single datasheet, whereas a "WorkBook" is a collection of WorkSheets. In Excel, this is how one workbook with two worksheets looks.

The WorkBook is the name of a new WorkSheet that you can build.

var worksheet = workbook.CreateWorkSheet("Countries");

var worksheet = workbook.CreateWorkSheet("Countries");

Dim worksheet = workbook.CreateWorkSheet("Countries")

Pass the worksheet's name to CreateWorkSheet.

Obtain the Cellular Range

A two-dimensional collection of "Cell" objects is represented by the "Range" class. It denotes a specific range of Excel cells. Using the string indexer on a WorkSheet object, you can get ranges.

var range = worksheet ["D2:D101"];

var range = worksheet ["D2:D101"];

Dim range = worksheet ("D2:D101")

The argument text can be a cell's coordinate (for example, "A1") or a range of cells from left to right, top to bottom (e.g. "B2:E5"). GetRange can also be called from a WorkSheet.

• Within a Range, Edit Cell Values

The values of cells inside a Range can be read or edited in a variety of ways. Use a For loop if the count is known. You can also do cell styling from here.

/**
Edit Cell Values in Range
anchor-edit-cell-values-within-a-range
**/
//Iterate through the rows
for (var y = 2; y <= 101; y++)
{
    var result = new PersonValidationResult { Row = y };
    results.Add(result);

    //Get all cells for the person
    var cells = worksheet [$"A{y}:E{y}"].ToList();

    //Validate the phone number (1 = B)
    var phoneNumber = cells [1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells [3].Value);

    //Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string)cells [4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

/**
Edit Cell Values in Range
anchor-edit-cell-values-within-a-range
**/
//Iterate through the rows
for (var y = 2; y <= 101; y++)
{
    var result = new PersonValidationResult { Row = y };
    results.Add(result);

    //Get all cells for the person
    var cells = worksheet [$"A{y}:E{y}"].ToList();

    //Validate the phone number (1 = B)
    var phoneNumber = cells [1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells [3].Value);

    //Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string)cells [4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

'''
'''Edit Cell Values in Range
'''anchor-edit-cell-values-within-a-range
'''*
'Iterate through the rows
For y = 2 To 101
	Dim result = New PersonValidationResult With {.Row = y}
	results.Add(result)

	'Get all cells for the person
	Dim cells = worksheet ($"A{y}:E{y}").ToList()

	'Validate the phone number (1 = B)
	Dim phoneNumber = cells (1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	'Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells (3).Value))

	'Get the raw date in the format of Month Day [suffix], Year (4 = E)
	Dim rawDate = CStr(cells (4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next y

Validate Data in Spreadsheets

To validate a sheet of data, use IronXL. The DataValidation sample validates phone numbers with libphonenumber-CSharp, and email addresses and dates with conventional C# APIs.

/**
Validate Spreadsheet Data
anchor-validate-spreadsheet-data
**/
//Iterate through the rows
for (var i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    //Get all cells for the person
    var cells = worksheet [$"A{i}:E{i}"].ToList();

    //Validate the phone number (1 = B)
    var phoneNumber = cells [1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells [3].Value);

    //Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string)cells [4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

/**
Validate Spreadsheet Data
anchor-validate-spreadsheet-data
**/
//Iterate through the rows
for (var i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    //Get all cells for the person
    var cells = worksheet [$"A{i}:E{i}"].ToList();

    //Validate the phone number (1 = B)
    var phoneNumber = cells [1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    //Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells [3].Value);

    //Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string)cells [4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

'''
'''Validate Spreadsheet Data
'''anchor-validate-spreadsheet-data
'''*
'Iterate through the rows
For i = 2 To 101
	Dim result = New PersonValidationResult With {.Row = i}
	results.Add(result)

	'Get all cells for the person
	Dim cells = worksheet ($"A{i}:E{i}").ToList()

	'Validate the phone number (1 = B)
	Dim phoneNumber = cells (1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	'Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells (3).Value))

	'Get the raw date in the format of Month Day [suffix], Year (4 = E)
	Dim rawDate = CStr(cells (4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next i

The code above loops through the spreadsheet's rows, grabbing the cells as a list. Each validated method verifies a cell's value and returns an error if the value is incorrect.

This code creates a new sheet, specifies headers, and produces the error message results so that an incorrect data log can be kept.

var resultsSheet = workbook.CreateWorkSheet("Results");

resultsSheet ["A1"].Value = "Row";
resultsSheet ["B1"].Value = "Valid";
resultsSheet ["C1"].Value = "Phone Error";
resultsSheet ["D1"].Value = "Email Error";
resultsSheet ["E1"].Value = "Date Error";

for (var i = 0; i < results.Count; i++)
{
    var result = results [i];
    resultsSheet [$"A{i + 2}"].Value = result.Row;
    resultsSheet [$"B{i + 2}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet [$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
    resultsSheet [$"D{i + 2}"].Value = result.EmailErrorMessage;
    resultsSheet [$"E{i + 2}"].Value = result.DateErrorMessage;
}

workbook.SaveAs(@"Spreadsheets\\PeopleValidated.xlsx");

var resultsSheet = workbook.CreateWorkSheet("Results");

resultsSheet ["A1"].Value = "Row";
resultsSheet ["B1"].Value = "Valid";
resultsSheet ["C1"].Value = "Phone Error";
resultsSheet ["D1"].Value = "Email Error";
resultsSheet ["E1"].Value = "Date Error";

for (var i = 0; i < results.Count; i++)
{
    var result = results [i];
    resultsSheet [$"A{i + 2}"].Value = result.Row;
    resultsSheet [$"B{i + 2}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet [$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
    resultsSheet [$"D{i + 2}"].Value = result.EmailErrorMessage;
    resultsSheet [$"E{i + 2}"].Value = result.DateErrorMessage;
}

workbook.SaveAs(@"Spreadsheets\\PeopleValidated.xlsx");

Dim resultsSheet = workbook.CreateWorkSheet("Results")

resultsSheet ("A1").Value = "Row"
resultsSheet ("B1").Value = "Valid"
resultsSheet ("C1").Value = "Phone Error"
resultsSheet ("D1").Value = "Email Error"
resultsSheet ("E1").Value = "Date Error"

For i = 0 To results.Count - 1
	Dim result = results (i)
	resultsSheet ($"A{i + 2}").Value = result.Row
	resultsSheet ($"B{i + 2}").Value = If(result.IsValid, "Yes", "No")
	resultsSheet ($"C{i + 2}").Value = result.PhoneNumberErrorMessage
	resultsSheet ($"D{i + 2}").Value = result.EmailErrorMessage
	resultsSheet ($"E{i + 2}").Value = result.DateErrorMessage
Next i

workbook.SaveAs("Spreadsheets\\PeopleValidated.xlsx")

Using Entity Framework to Export Data

Use IronXL to convert an Excel spreadsheet to a database or to export data to a database. The ExcelToDB sample reads a spreadsheet containing GDP by nation and exports it to SQLite.

It creates the database with EntityFramework and then exports the data line-by-line.

The SQLite Entity Framework NuGet packages should be installed.

You can use EntityFramework to construct a model object that can export data to a database.

public class Country
{
    [Key]
    public Guid Key { get; set; }
    public string Name { get; set; }
    public decimal GDP { get; set; }
}

public class Country
{
    [Key]
    public Guid Key { get; set; }
    public string Name { get; set; }
    public decimal GDP { get; set; }
}

Public Class Country
	<Key>
	Public Property Key() As Guid
	Public Property Name() As String
	Public Property GDP() As Decimal
End Class

To use a different database, install the appropriate NuGet package and look for the UseSqLite equivalent ().

/**
Export Data using Entity Framework
anchor-export-data-using-entity-framework
**/
public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }

    public CountryContext()
    {
        //TODO: Make async
        Database.EnsureCreated();
    }

    /// <summary>
    /// Configure context to use Sqlite
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connection = new SqliteConnection($"Data Source=Country.db");
        connection.Open();

        var command = connection.CreateCommand();

        //Create the database if it doesn't already exist
        command.CommandText = $"PRAGMA foreign_keys = ON;";
        command.ExecuteNonQuery();

        optionsBuilder.UseSqlite(connection);

        base.OnConfiguring(optionsBuilder);
    }

}

/**
Export Data using Entity Framework
anchor-export-data-using-entity-framework
**/
public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }

    public CountryContext()
    {
        //TODO: Make async
        Database.EnsureCreated();
    }

    /// <summary>
    /// Configure context to use Sqlite
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connection = new SqliteConnection($"Data Source=Country.db");
        connection.Open();

        var command = connection.CreateCommand();

        //Create the database if it doesn't already exist
        command.CommandText = $"PRAGMA foreign_keys = ON;";
        command.ExecuteNonQuery();

        optionsBuilder.UseSqlite(connection);

        base.OnConfiguring(optionsBuilder);
    }

}

'''
'''Export Data using Entity Framework
'''anchor-export-data-using-entity-framework
'''*
Public Class CountryContext
	Inherits DbContext

	Public Property Countries() As DbSet(Of Country)

	Public Sub New()
		'TODO: Make async
		Database.EnsureCreated()
	End Sub

	''' <summary>
	''' Configure context to use Sqlite
	''' </summary>
	''' <param name="optionsBuilder"></param>
	Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
		Dim connection = New SqliteConnection($"Data Source=Country.db")
		connection.Open()

		Dim command = connection.CreateCommand()

		'Create the database if it doesn't already exist
		command.CommandText = $"PRAGMA foreign_keys = ON;"
		command.ExecuteNonQuery()

		optionsBuilder.UseSqlite(connection)

		MyBase.OnConfiguring(optionsBuilder)
	End Sub

End Class

Produce a CountryContext, then iterate through the range to create each entry before saving the data to the database with SaveAsync.

public async Task ProcessAsync()
{
    //Get the first worksheet
    var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
    var worksheet = workbook.GetWorkSheet("GDPByCountry");

    //Create the database connection
    using (var countryContext = new CountryContext())
    {
        //Iterate through all the cells
        for (var i = 2; i <= 213; i++)
        {
            //Get the range from A-B
            var range = worksheet [$"A{i}:B{i}"].ToList();

            //Create a Country entity to be saved to the database
            var country = new Country
            {
                Name = (string)range [0].Value,
                GDP = (decimal)(double)range [1].Value
            };

            //Add the entity 
            await countryContext.Countries.AddAsync(country);
        }

        //Commit changes to the database
        await countryContext.SaveChangesAsync();
    }
}

public async Task ProcessAsync()
{
    //Get the first worksheet
    var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
    var worksheet = workbook.GetWorkSheet("GDPByCountry");

    //Create the database connection
    using (var countryContext = new CountryContext())
    {
        //Iterate through all the cells
        for (var i = 2; i <= 213; i++)
        {
            //Get the range from A-B
            var range = worksheet [$"A{i}:B{i}"].ToList();

            //Create a Country entity to be saved to the database
            var country = new Country
            {
                Name = (string)range [0].Value,
                GDP = (decimal)(double)range [1].Value
            };

            //Add the entity 
            await countryContext.Countries.AddAsync(country);
        }

        //Commit changes to the database
        await countryContext.SaveChangesAsync();
    }
}

Public Async Function ProcessAsync() As Task
	'Get the first worksheet
	Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
	Dim worksheet = workbook.GetWorkSheet("GDPByCountry")

	'Create the database connection
	Using countryContext As New CountryContext()
		'Iterate through all the cells
		For i = 2 To 213
			'Get the range from A-B
			Dim range = worksheet ($"A{i}:B{i}").ToList()

			'Create a Country entity to be saved to the database
			Dim country As New Country With {
				.Name = CStr(range (0).Value),
				.GDP = CDec(CDbl(range (1).Value))
			}

			'Add the entity 
			Await countryContext.Countries.AddAsync(country)
		Next i

		'Commit changes to the database
		Await countryContext.SaveChangesAsync()
	End Using
End Function

Incorporate a Formula into a Spreadsheet

The Formula property can be used to set the formula of a cell.

//Iterate through all rows with a value
for (var y = 2; y < i; y++)
{
    //Get the C cell
    var cell = sheet [$"C{y}"].First();

    //Set the formula for the Percentage of Total column
    cell.Formula = $"=B{y}/B{i}";
}

//Iterate through all rows with a value
for (var y = 2; y < i; y++)
{
    //Get the C cell
    var cell = sheet [$"C{y}"].First();

    //Set the formula for the Percentage of Total column
    cell.Formula = $"=B{y}/B{i}";
}

'Iterate through all rows with a value
Dim y = 2
Do While y < i
	'Get the C cell
	Dim cell = sheet ($"C{y}").First()

	'Set the formula for the Percentage of Total column
	cell.Formula = $"=B{y}/B{i}"
	y += 1
Loop

The code in column C iterates through each state and calculates a percentage total.

Data from an API can be downloaded to a spreadsheet

RestClient.Net is used in the following call to make a REST call. It downloads JSON and turns it to a RestCountry-type "List." The data from the REST API may then be easily saved to an Excel file by iterating through each country.

/**
Data API to Spreadsheet
anchor-download-data-from-an-api-to-spreadsheet
**/
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();

/**
Data API to Spreadsheet
anchor-download-data-from-an-api-to-spreadsheet
**/
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();

'''
'''Data API to Spreadsheet
'''anchor-download-data-from-an-api-to-spreadsheet
'''*
Dim client As New Client(New Uri("https://restcountries.eu/rest/v2/"))
Dim countries As List(Of RestCountry) = Await client.GetAsync(Of List(Of RestCountry))()

The JSON data from the API looks like this:

The following code loops through the countries and populates the spreadsheet with Name, Population, Region, NumericCode, and Top 3 Languages.

for (var i = 2; i < countries.Count; i++)
{
    var country = countries [i];

    //Set the basic values
    worksheet [$"A{i}"].Value = country.name;
    worksheet [$"B{i}"].Value = country.population;
    worksheet [$"G{i}"].Value = country.region;
    worksheet [$"H{i}"].Value = country.numericCode;

    //Iterate through languages
    for (var x = 0; x < 3; x++)
    {
        if (x > (country.languages.Count - 1)) break;

        var language = country.languages [x];

        //Get the letter for the column
        var columnLetter = GetColumnLetter(4 + x);

        //Set the language name
        worksheet [$"{columnLetter}{i}"].Value = language.name;
    }
}

for (var i = 2; i < countries.Count; i++)
{
    var country = countries [i];

    //Set the basic values
    worksheet [$"A{i}"].Value = country.name;
    worksheet [$"B{i}"].Value = country.population;
    worksheet [$"G{i}"].Value = country.region;
    worksheet [$"H{i}"].Value = country.numericCode;

    //Iterate through languages
    for (var x = 0; x < 3; x++)
    {
        if (x > (country.languages.Count - 1)) break;

        var language = country.languages [x];

        //Get the letter for the column
        var columnLetter = GetColumnLetter(4 + x);

        //Set the language name
        worksheet [$"{columnLetter}{i}"].Value = language.name;
    }
}

For i = 2 To countries.Count - 1
	Dim country = countries (i)

	'Set the basic values
	worksheet ($"A{i}").Value = country.name
	worksheet ($"B{i}").Value = country.population
	worksheet ($"G{i}").Value = country.region
	worksheet ($"H{i}").Value = country.numericCode

	'Iterate through languages
	For x = 0 To 2
		If x > (country.languages.Count - 1) Then
			Exit For
		End If

		Dim language = country.languages (x)

		'Get the letter for the column
		Dim columnLetter = GetColumnLetter(4 + x)

		'Set the language name
		worksheet ($"{columnLetter}{i}").Value = language.name
	Next x
Next i

Open Excel files with IronXL

After launching the Excel file, add the first few lines that read the 1st cell in the 1st sheet, and print.

static void Main(string [] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet ["A1"].StringValue;
    Console.WriteLine(cell);
}

static void Main(string [] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet ["A1"].StringValue;
    Console.WriteLine(cell);
}

Shared Sub Main(ByVal args() As String)
	Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
	Dim sheet = workbook.WorkSheets.First()
	Dim cell = sheet ("A1").StringValue
	Console.WriteLine(cell)
End Sub

Using IronXL, create a new Excel file.

/**
Create Excel File
anchor-create-a-new-excel-file
**/
static void Main(string [] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet ["A1"].Value = "Hello World";
    newWorkSheet ["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet ["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}

/**
Create Excel File
anchor-create-a-new-excel-file
**/
static void Main(string [] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet ["A1"].Value = "Hello World";
    newWorkSheet ["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet ["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}

'''
'''Create Excel File
'''anchor-create-a-new-excel-file
'''*
Shared Sub Main(ByVal args() As String)
	Dim newXLFile = WorkBook.Create(ExcelFileFormat.XLSX)
	newXLFile.Metadata.Title = "IronXL New File"
	Dim newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet")
	newWorkSheet ("A1").Value = "Hello World"
	newWorkSheet ("A2").Style.BottomBorder.SetColor("#ff6600")
	newWorkSheet ("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed
End Sub

After this, you can save either to CSV, JSON, or XML using their respective codes.

For example, to Save to XML “.xml”

To save to XML use SaveAsXml as follows:

newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");

newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");

newXLFile.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")

The result looks like this:

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>

Reading Excel files is easier with IronXL rather than with EPPlus. IronXL has more shortened codes that encompass all that is needed to access all columns, rows, and cells in a workbook, while with EPPlus you will need a specific line of code to read columns and rows.

IronXL is more intelligent when it comes to manipulating Excel documents. It gives you the upper hand to create more sheets at any point in time, as well as read data from multiple worksheets and workbooks, while EPPlus deals with one worksheet at a time. With IronXL, you can also fill a database with the data in your Excel workbook.

EPPlus and IronXL Licensing and Pricing

EPPlus License Model and Price

EPPlus can be used under two license models, either a noncommercial license model or a commercial license model from Polyform.

Commercial Licenses

These are available in both perpetual and subscription-based formats, with terms ranging from one month to two years.

For all licensing categories, support via the support center and upgrades via NuGet are included during the license duration.

EPPlus requires one license per developer. Licenses are issued to a single individual and cannot be shared. As a general guideline, anyone who produces or needs to debug code that directly utilizes EPPlus should have a commercial license.

If you provide EPPlus as a service internally (for example, by exposing its capabilities via an API), your firm must purchase a subscription that covers the number of internal users (developers) who will be using the service.

Subscriptions

You can always use the latest version with a subscription, but you must have a valid license as long as you use EPPlus for development. At the end of the licensing period, the license is automatically invoiced and renewed once the payment is paid. You can cancel your subscription at the end of the licensing period and start a new one whenever you like. Subscriptions are only available for purchase on the internet.

EPPlus can be used in a commercial setting. The license is for one developer per company, with an unlimited number of deployment locations. The number of licenses available for purchase each year can be increased or lowered, and the license can be suspended or canceled at the conclusion of each year.

A 32-day trial period is available as an option.

Pricing: Starts from $299 per year.

You can pay as you go

Price per developer within a single organization, with unlimited deployment locations and Stripe invoicing. The number of licenses available every month can be increased or lowered, and the license can be suspended or canceled at the end of each month.

Pricing: Starts from $29 per month.

Perpetual License

A perpetual license allows you to update to new versions and receive support for a set amount of time. You can then continue to develop software using the versions released during this time without needing to renew your license.

Within the same firm, price per developer with unlimited deployment sites. Use of all EPPlus versions released within the support/upgrades term indefinitely.

A 32-day trial period is available as an option.

Pricing: Starts from $599 per year.

Packages

Perpetual license options with an initial duration of upgrades and support are available. You can then continue to develop software using the versions released throughout this time period without needing to renew your license.

Pricing: Starts from $4295 per year.

Noncommercial license for Polyform

EPPlus is licensed under the Polyform Noncommercial license starting with version 5, which indicates that the code is open-source and can be used for non-commercial uses. You can see more details on their website.

IronXL License Model and Price

Perpetual Licensing: each license is purchased once and does not require renewal.

Free Support & Product Updates: every license comes with a year of free product updates and support from the team behind the product. It is possible to purchase extensions at any moment. Extensions can be viewed.

Immediate Licenses: registered license keys are sent out as soon as payment is received.

Please contact our Iron Software licensing specialists if you have any questions about IronXL for .NET licensing.

All licenses are perpetual and apply to development, staging, and production.

Lite - Allows a single software developer in an organization to utilize the Iron Software in a single place. Iron Software can be used in a single web application, intranet application, or desktop software program. Licenses are non-transferable, and they cannot be shared outside of an organization or an agency/client relationship. This license type, like all other license types, expressly excludes all rights not expressly granted under the Agreement, including OEM redistribution and utilizing the Iron Software as a SaaS without purchasing additional coverage.

Pricing: Starts from $489 per year.

Professional License - Allows a predetermined number of software developers in an organization to utilize Iron Software in a single location, up to a maximum of ten. The Iron Software can be used in as many websites, intranet applications, or desktop software applications as you like. Licenses are non-transferable, and they cannot be shared outside of an organization or an agency/client relationship. This license type, like all other license types, expressly excludes all rights not expressly granted under the Agreement, including OEM redistribution and utilizing the Iron Software as a SaaS without purchasing additional coverage.

Pricing: Starts from $976 per year.

Unlimited License - Allows an unlimited number of software developers in an organization to utilize Iron Software in an unlimited number of locations. The Iron Software can be used in as many websites, intranet applications, or desktop software applications as you like. Licenses are non-transferable, and they cannot be shared outside of an organization or an agency/client relationship. This license type, like all other license types, expressly excludes all rights not expressly granted under the Agreement, including OEM redistribution and utilizing the Iron Software as a SaaS without purchasing additional coverage.

Royalty-Free Redistribution - Allows you to distribute the Iron Software as part of a number of different packaged commercial products (without having to pay royalties) based on the number of projects covered by the base license. Allows for the deployment of Iron Software within SaaS software services, based on the number of projects covered by the base license.

Pricing: Starts from $2939 per year.

Conclusion

In conclusion, IronXL is more practical than EPPlus because it gives you the flexibility to navigate around the Excel table as you require, with shorter lines of codes and more opportunities for exporting including XML, HTML, and JSON. IronXL also allows you to integrate your workbook data into a database. Further, it has an intuitive system that recalculates formulas each time the document is edited, and it provides an Intuitive Ranges setting with a WorkSheet [“A1:B10”] syntax. The sheet functions include formulas that work with Excel and are recalculated every time a sheet is edited. The cell data formats have multiple texts, numbers, formulas, dates, currency, percentages, scientific notation, and time. Their custom formats have different sortings methods such as ranges, columns, and rows. Its cell styling includes a variety of fonts, sizes, background patterns, borders, and alignments.