IronXL 與 ClosedXML 的比較

IronXL 功能集

• 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. 直觀地組合和創建範圍。
• 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

本文接下來的部分內容如下：

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 提供 NuGet 包管理器來在專案中安裝 NuGet 套件。 您可以通過專案選單訪問它，或者在方案資源管理器中右鍵單擊您的項目。

Package Manager

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

IronXL NuGet Install

• 完成。

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.

• 打開 開發命令提示 —— 通常在 Visual Studio 資料夾中找到。

輸入以下命令：

Install-Package IronXL.Excel

• 按 Enter 鍵。
• 這將會下載並安裝套件。
• 重新加載您的 Visual Studio 專案並開始使用。

2.3. Add Necessary Using Directives

1. In Solution Explorer, right-click the Program.cs file and then click View Code.
2. 在代碼文件的頂部添加以下命名空間指令：

using IronXL;

using IronXL;

Imports IronXL

完成了！ IronXL 已經下載並安裝完成，準備就緒。 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

或者：

• 打開 開發命令提示 —— 通常在 Visual Studio 資料夾中找到。

輸入以下命令：

Install-Package ClosedXML

• 按 Enter 鍵。
• 這將會下載並安裝套件。
• 重新加載您的 Visual Studio 專案並開始使用。

3.2. Add Necessary Using Directives

1. In Solution Explorer, right-click the Program.cs file and then click View Code.
2. 在代碼文件的頂部添加以下命名空間指令：

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. 是的，真的。 將以下代碼添加到 Program.cs 文件中的靜態 void 主函數：

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

IronXL 可以創建 XLS (較舊的 Excel 文件版本) 和 XLSX (當前且較新的文件版本) 文件格式。

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")

您現在可以使用工作表變量來設置單元格值以及執行除保存以外的幾乎所有操作。

// 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. 使用 IronXL 讀取 Excel 文件

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). 以下一行代碼用於打開文件以供閱讀：

// 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")

讀取單元格值的代碼：

// 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. 使用 IronXL 處理 Excel 公式

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()

您還可以檢索公式及其值：

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

總結

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.

結論

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

[{i:(ClosedXML is a registered trademark of its respective owner. This site is not affiliated with, endorsed by, or sponsored by ClosedXML. 所有產品名稱、徽標和品牌均為其各自所有者的財產。 比較僅供參考，反映撰寫時公開可用的信息。