IronXL 和 ClosedXML 的比较

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: Work with Excel formulas, recalculated every time a sheet is edited.
• Ranges: Easy to use WorkSheet ["A1:B10"] syntax. Combine and create ranges intuitively.
• Sorting: Sort ranges, columns, and rows.
• Styling: Cell visual styles, font, size, background pattern, border, alignment, and number formats.

More Features of IronXL can be explored using this link.

ClosedXML Features

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

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

The rest of this article continues as follows:

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

2.1. Using Visual Studio with NuGet Packages

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

Package Manager

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

IronXL NuGet Install

• And we are done.

2.2. Manually Installing the DLL

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

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

Type the following command:

Install-Package IronXL.Excel

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

2.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 IronXL;

using IronXL;

Imports IronXL

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

3.1. Using the NuGet Package Manager/Console

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

ClosedXML NuGet Install

Or:

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

Type the following command:

Install-Package ClosedXML

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

3.2. Add Necessary Using Directives

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

using ClosedXML.Excel;

using ClosedXML.Excel;

Imports ClosedXML.Excel

4.1. New Excel Workbook and Sheets using IronXL

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

// Create a new Excel workbook using IronXL
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

// Create a new Excel workbook using IronXL
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

' Create a new Excel workbook using IronXL
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

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

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

// Create a new worksheet within the workbook
var worksheet = workbook.CreateWorkSheet("IronXL Features");

// Create a new worksheet within the workbook
var worksheet = workbook.CreateWorkSheet("IronXL Features");

' Create a new worksheet within the workbook
Dim worksheet = workbook.CreateWorkSheet("IronXL Features")

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

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

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

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

Save the Excel file:

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

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

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

The output file looks as follows:

NewExcelFile output

4.2. New Excel Workbook and Sheets using ClosedXML

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

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

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

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

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

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

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

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

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

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

The output file looks as follows:

Spreadsheet File Output

5.1. Read Excel Files using IronXL

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

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

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

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

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

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

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

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

Code for reading the cell values:

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

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

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

5.2. Read Excel Files using ClosedXML

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

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

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

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

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

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

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

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

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

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

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

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

' Display on screen
Console.WriteLine(data)

6.1. Working with Excel Formulas using IronXL

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

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

// Force recalculating all formula values in all sheets
workbook.EvaluateAll();

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

// Force recalculating all formula values in all sheets
workbook.EvaluateAll();

' Set formulas
worksheet("A1").Formula = "=Sum(B8:C12)"
worksheet("B8").Formula = "=C9/C11"
worksheet("G30").Formula = "=Max(C3:C7)"

' Force recalculating all formula values in all sheets
workbook.EvaluateAll()

You can also retrieve formulas and their values:

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

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

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

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

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

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

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

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

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

6.2. Working with Excel Formulas using ClosedXML

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

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

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

// Force recalculation
worksheet.RecalculateAllFormulas();

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

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

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

// Force recalculation
worksheet.RecalculateAllFormulas();

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

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

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

' Force recalculation
worksheet.RecalculateAllFormulas()

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

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

Summary

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

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

Conclusion

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

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

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

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