COMPARE TO OTHER COMPONENTS

A Comparison between IronXL and Microsoft Office Interop Excel

Published March 28, 2022
Share:

MS Office tools such as Word, Excel, PowerPoint, etc. are very popular and widely-used across all types of businesses. The creation of report files in Excel file format is a feature many users need in their software applications, and there is now a definitive way to include this feature in software applications using the different libraries available.

In this article we are going to discuss and compare how to work with Microsoft Excel files programmatically in C# using the two most popular libraries, IronXL and Microsoft Office Excel Interop.

IronXL and Microsoft Excel Interop both provide the methods to create, edit, and read Excel documents in .NET frameworks. 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 firstly 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 the reading and editing of Microsoft Excel documents in C#. IronXL.Excel is a standalone .NET software library for reading a wide range of spreadsheet formats. It does not require Microsoft Excel to 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 spread sheet library for C#.

Docs & Support

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.

Microsoft Office Excel Interop Objects

The Microsoft.Office.Interop.Excel namespace provides methods for interacting with the Microsoft Excel application in C# and Visual Basic. We can create new Excel workbooks, Excel worksheets, display data in existing sheets, modify existing Excel sheet contents and much more with this namespace.

The classes and interfaces in Microsoft.Office.Interop.Excel provides support for interoperability between the COM object model of Microsoft Excel files and managed applications that automate xls or xlsx files.

The C# programming language includes capabilities that make working with Microsoft Office Interop API objects easier. The new features include named and optional arguments, a new type called dynamic, and the ability to pass arguments to reference parameters in COM methods as if they were value parameters. This makes it a better choice for working with COM and Interop objects.

Note: Microsoft Excel Interop objects need Microsoft Excel to be installed on the computer

The rest of the article goes as follows:

  1. Create a Console Application
  2. IronXL C# Library Installation
  3. Microsoft Office Interop Excel Installation
  4. Create a New Excel Workbook and Sheet
  5. Read Excel files
  6. Working with Range of Values in Excel files
  7. Working with Excel formulas
  8. Licensing
  9. 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 new project”.
  • On the “Create new project” page, select C# in the language drop down list, Windows from the Platforms list, and Console from the “Project types” list.
  • Select Console App (.NET Framework) from the project templates displayed.
Create Project - Console Application
  • Click Next.
  • In the Additional Information screen, specify the Framework version you would like to use. We will use .NET Framework 4.8 in this example.
Create Project - .NET Framework
  • 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. Download the NuGet Package directly.
  3. Manually install with 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.

Select Manage NuGet Package
  • Now, from the browse tab -> search for IronXL.Excel -> Install
Search for IronXL
  • And we are done.
Search Microsoft Excel Interop

3.2. Using the NuGet Package Manager Console

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

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

Type the following command:

PM> Install-Package Microsoft.Office.Interop.Excel

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

3.3. 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 Excel = Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
Imports Excel = Microsoft.Office.Interop.Excel
VB   C#

4. Create a New Excel Workbook and Sheet

A workbook is an Excel file containing multiple worksheets with rows and columns. Both 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. A New Excel Workbook and Sheet using IronXL

It could not be any easier to create a new Excel Workbook using IronXL! 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)
VB   C#

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

And, it’s even easier to create a default Worksheet:

var worksheet = workbook.CreateWorkSheet("IronXL Features");
var worksheet = workbook.CreateWorkSheet("IronXL Features");
Dim worksheet = workbook.CreateWorkSheet("IronXL Features")
VB   C#

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

4.2. A New Excel Workbook and Sheet using Microsoft.Office.Interop.Excel

In order to create an Excel file using Microsoft.Office.Interop, Microsoft Excel needs to be installed. So, we need to check if Microsoft Excel is installed on the host machine — if not, it will simply return an exception that needs to be handled. The following example code samples allow you to check for an Excel application installation and create Excel workbooks and sheets:

Check Microsoft Excel installed:

//Start Excel and get Application object.
Excel.Application xlApp = new Excel.Application();  

//Checks if Excel is installed  
if (xlApp == null)  
{  
    Console.WriteLine("Excel is not installed in the system...");  
    return;  
}
//Start Excel and get Application object.
Excel.Application xlApp = new Excel.Application();  

//Checks if Excel is installed  
if (xlApp == null)  
{  
    Console.WriteLine("Excel is not installed in the system...");  
    return;  
}
'Start Excel and get Application object.
Dim xlApp As New Excel.Application()

'Checks if Excel is installed  
If xlApp Is Nothing Then
	Console.WriteLine("Excel is not installed in the system...")
	Return
End If
VB   C#

Create Workbook and Worksheet:

//Create Workbook and Worksheet
object misValue = System.Reflection.Missing.Value;  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//Create Workbook and Worksheet
object misValue = System.Reflection.Missing.Value;  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
'Create Workbook and Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add(misValue)
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
VB   C#

So far, looking at both the codes, the advantage lies with IronXL over Office Interop Excel. IronXL uses just one line of code to be able to work with Excel files, with no fuss and additional dependencies.

5. Read Excel Files

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 Excel 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")
VB   C#

Each WorkBook can have multiple WorkSheet objects. These represent worksheets in the Excel document. If the workbook contains 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")
VB   C#

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
VB   C#

5.2. Read Excel Files using Microsoft.Office.Interop.Excel

This also uses one line of code to open a workbook and sheet. The code goes as follows:

Excel.Application xlApp = new Excel.Application();  

//Load WorkBook
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  

//Open Sheet for reading
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Application xlApp = new Excel.Application();  

//Load WorkBook
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  

//Open Sheet for reading
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Dim xlApp As New Excel.Application()

'Load WorkBook
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)

'Open Sheet for reading
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
VB   C#

Code for reading the cell values:

//Get the entire range of cells
Excel.Range xlRange = xlWorkSheet.UsedRange;

//Reading first 10 rows of with two columns
for (int rowCount = 1; rowCount <= 10; rowCount++)  
{  
  firstValue = Convert.ToString((xlRange.Cells [rowCount, 1] as Excel.Range).Text);  
  secondValue = Convert.ToString((xlRange.Cells [rowCount, 2] as Excel.Range).Text);  

  Console.WriteLine(firstValue + "\t" + secondValue);  
}
//Get the entire range of cells
Excel.Range xlRange = xlWorkSheet.UsedRange;

//Reading first 10 rows of with two columns
for (int rowCount = 1; rowCount <= 10; rowCount++)  
{  
  firstValue = Convert.ToString((xlRange.Cells [rowCount, 1] as Excel.Range).Text);  
  secondValue = Convert.ToString((xlRange.Cells [rowCount, 2] as Excel.Range).Text);  

  Console.WriteLine(firstValue + "\t" + secondValue);  
}
Imports Microsoft.VisualBasic

'Get the entire range of cells
Dim xlRange As Excel.Range = xlWorkSheet.UsedRange

'Reading first 10 rows of with two columns
For rowCount As Integer = 1 To 10
  firstValue = Convert.ToString((TryCast(xlRange.Cells (rowCount, 1), Excel.Range)).Text)
  secondValue = Convert.ToString((TryCast(xlRange.Cells (rowCount, 2), Excel.Range)).Text)

  Console.WriteLine(firstValue & vbTab & secondValue)
Next rowCount
VB   C#

However, checking the Excel installation and creating its instance is an integral part.

6. Working with Range of Values in Excel Files

6.1. Working with Range of Values Using IronXL

In IronXL, with one line of code, we can get the range of values of particular cells. Then, using a loop, each cell value can be read or edited. The code goes as follows:

WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get range from Excel worksheet
var range = sheet ["A2:A8"];
//This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}
WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

//This is how we get range from Excel worksheet
var range = sheet ["A2:A8"];
//This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}
Dim workbook As WorkBook = WorkBook.Load("test.xls")
Dim sheet As WorkSheet = workbook.WorkSheets.First()

'This is how we get range from Excel worksheet
Dim range = sheet ("A2:A8")
'This is how we can iterate over our range and read or edit any cell
For Each cell In range
	Console.WriteLine(cell.Value)
Next cell
VB   C#

6.2. Working with Range of Values Using Microsoft.Office.Interop.Excel

This also uses one line of code to get the range of cell values. The cells can then be updated with the required values. The code goes as follows:

Excel.Application xlApp = new Excel.Application();  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

//Get range of values and updating their format
var range = xlWorkSheet.get_Range("A1", "D1");
range.NumberFormat = "$0.00";
Excel.Application xlApp = new Excel.Application();  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

//Get range of values and updating their format
var range = xlWorkSheet.get_Range("A1", "D1");
range.NumberFormat = "$0.00";
Dim xlApp As New Excel.Application()
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)

'Get range of values and updating their format
Dim range = xlWorkSheet.get_Range("A1", "D1")
range.NumberFormat = "$0.00"
VB   C#

7. Working with Excel Formulas

Excel formulas constitute the most important part of working with Excel files. Both the libraries provide the facility to work with formulas and apply them to cells with ease.

7.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 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 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();
' 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()
VB   C#

You can also retrieve formulas and their values.

// Get Formulas
// 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 Formulas
// 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 Formulas
' 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()
VB   C#

7.2. Working with Excel Formulas using Microsoft.Office.Interop.Excel

Similarly, after loading the workbook and worksheet, the following code can be used to work on Excel formulas. The code sample goes as follows:

//Get range of values and updating their format
var range1 = xlWorkSheet.get_Range("A1", "D1");
var range2 = xlWorkSheet.get_Range("C2", "C6");

// Set Formula
range1.Formula = "=RAND()*100000";
range2.Formula = "=A2 & \" \" & B2";
//Get range of values and updating their format
var range1 = xlWorkSheet.get_Range("A1", "D1");
var range2 = xlWorkSheet.get_Range("C2", "C6");

// Set Formula
range1.Formula = "=RAND()*100000";
range2.Formula = "=A2 & \" \" & B2";
'Get range of values and updating their format
Dim range1 = xlWorkSheet.get_Range("A1", "D1")
Dim range2 = xlWorkSheet.get_Range("C2", "C6")

' Set Formula
range1.Formula = "=RAND()*100000"
range2.Formula = "=A2 & "" "" & B2"
VB   C#

8. 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.

For Microsoft Office Interop Excel, 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 Office application, Microsoft Office applications must be installed and licensed on the machine where the solution runs. The DLL will always be present on a machine where the Office application is installed, and it will be registered there. At the same time, the primary interop assembly (PIAs) necessary for .NET solutions using the "interop" will also be installed in the GAC.

9. Summary and Conclusion

Summary

Reading and creating Excel (XLS, XLSX, and CSV) files in C# and all other .NET languages is easy using the IronXL software library from Iron Software. IronXL does not require Excel to be installed on your server or Interop. IronXL provides a faster and more intuitive API than Microsoft.Office.Interop.Excel. IronXL works on .NET Core 2, Framework 4.5, Azure, Mono and, Mobile and Xamarin.

Microsoft Office Interop (Excel Automation) is an option when creating/reading Excel files (XLS, XLSX, CSV) from C# or VB.NET applications. It requires all client machines to have the same version of Microsoft Excel installed, and it works only on Windows operating systems.

Conclusion

Here, IronXL has a clear advantage over Microsoft.Office.Interop.Excel as it does not require the Microsoft Office Excel application to be installed on the local or server machine in order to work. Moreover, when using Excel Automation, Microsoft Excel is loaded in the background, using a lot of MB and loading a large number of files and DLLs in comparison to IronXL. IronXL has a faster and more intuitive API than that of Microsoft.Office.Interop.Excel.

Microsoft Office applications (including Excel) were designed as UI applications, and therefore the API is very slow. Microsoft does not recommend using Excel Automation (or any Office Interop) on the server. In this case, with all its robust features, IronXL is the best choice for integration into software applications.

< PREVIOUS
A Comparison between IronXL and Epplus
NEXT >
A Comparison between IronXL and Aspose Cells

Ready to get started? Version: 2024.11 just released

Free NuGet Download Total downloads: 1,094,134 View Licenses >