IronXL 和 GemBox.Spreadsheet 的比較

IronXL 功能集

• 加載，閱讀和編輯數據 — 從 XLS/XLSX/CSV/TSV
• 匯出和保存不同格式 — 到 XLS/XLSX/CSV/TSV/JSON
• System.Data 對象 — 它將 Excel WorkBook 電子錶格作為 System.Data.DataSet 和 System.Data.DataTable 對象工作。
• 支持公式 — 支持 Excel 公式。 每次編輯工作表時都會重新計算公式。
• 支持範圍 — 其易於使用的 WorkSheet 語法允許您直觀地創建和組合範圍。
• 過濾和排序 — 它可以對範圍、列和行進行排序。
• 單元格樣式 — 視覺樣式、字體、大小、背景圖案、邊框、對齊和數字格式。

GemBox.Spreadsheet API 功能

• 讀取 Excel 文件 (XLSX, XLS, ODS)、文本文件 (CSV, TXT) 和 HTML 文件。
• 創建電子錶格並將其轉換為 XLSX, XLS, ODS, CSV, TXT, HTML, PDF, PDF/A, XPS 和圖像格式。
• 在 WPF、ASP.NET、ASP.NET Core 和 Windows.Forms 應用程式中查看和編輯電子錶格。
• 使用其計算引擎應用單元格公式。
• 創建和編輯工作表、行、列、單元格、公式、圖像、圖表、形狀、文本框、表格、單元格樣式和格式、樞紐分析表。
• 操作頁眉、頁腳和文檔屬性。
• 編輯單元格組、數據輪廓、數據驗證、工作表保護和打印/查看選項。
• 從 System.Data.DataSet 和 System.Data.DataTable 導入和導出電子錶格數據。
• 打印 Excel 電子錶格。

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

1. 創建一個控制台應用程式
2. 安裝 IronXL C# 庫
3. 安裝 GemBox.Spreadsheet
4. 創建一個新的 Excel 工作簿
5. 讀取 Excel 文件
6. 使用 Excel 公式
7. 文件互轉
8. 授權
9. 結論

2.1. 使用 NuGet 程式包管理器的 Visual Studio

Visual Studio 2022 提供了 NuGet 程式包管理器，可以在您的項目中安裝 NuGet 程式包。 您可以通過以下多種方式訪問它：通過項目菜單，或通過在解決方案資源管理器中右鍵單擊您的項目。

class="content-img-align-center">

class="content__image-caption">程式包管理器

• 在瀏覽選項卡中，搜尋 "IronXL.Excel"，然後點擊安裝按鈕以添加庫。
• 完成此過程後，IronXL 已成功安裝。

2.2. 手動下載和安裝 DLL

另一種下載和安裝 IronXL 庫的方法是使用 NuGet 程式包管理器控制台。

• 打開 開發者命令提示符 控制台。

輸入以下命令：

Install-Package IronXL.Excel

• 按下 Enter 鍵。 這將自動在您的項目中下載和安裝程式包。

2.4. 包含 IronXL 庫

1. 打開 Program.cs 文件。
2. 在代碼文件頂部添加以下代碼行：

using IronXL;

using IronXL;

Imports IronXL

4.1. 使用 IronXL 創建和保存新的 Excel 工作簿和工作表

使用 IronXL 創建新的 Excel 工作簿非常簡單！

將以下代碼添加到您的 Program.cs 文件中：

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

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

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

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

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

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

4.2. 使用 GemBox.Spreadsheet 創建新的 Excel 工作簿和工作表

在 GemBox 中創建 Excel 工作簿也非常簡單，如以下代碼片段所示：

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

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

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

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

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

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

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

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

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

5.1. 使用 IronXL 讀取 Excel 文件

IronXL 中的 WorkBook 類代表一個 Excel 表。 要在 C# 中打開和讀取 Excel 文件，我們使用 WorkBook.Load 並指定 Excel 文件（.xlsx）的確切路徑。

以下代碼加載一個電子錶格：

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

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

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

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

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

' 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. 使用 GemBox.Spreadsheet 讀取 Excel 文件

GemBox.Spreadsheet 允許從您的 C# 應用程式中快速讀取 Excel 文件。 以下代碼展示了如何打開和讀取文件。

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

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

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

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

Dim workbook = ExcelFile.Load("SimpleTemplate.xlsx")
Dim worksheet = workbook.Worksheets("sheetnamegoeshere")

For Each cell In worksheet.Cells.GetSubrange("A2", "A20")
	Console.WriteLine("Cell {0} has value '{1}'", cell.Name, cell.Value)
Next cell

6.1. 使用 IronXL 處理 Excel 公式

裝載工作簿和工作表後，可以使用以下代碼對公式進行更改或將新公式應用於特定單元格：

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

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

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

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

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

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

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

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

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

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

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

6.2. 使用 GemBox.Spreadsheet 使用 Excel 公式

GemBox.Spreadsheet 提供了大量 Excel 公式功能，包括數學、統計、邏輯、查找、財務等。 讓我們來看看如何使用它們。

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

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

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

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

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

workbook.Save("Formula Calculation.xlsx")

7.1. 使用 IronXL 轉換電子錶格類型

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

workbook.SaveAs("CsvToExcelConversion.xlsx");

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

workbook.SaveAs("CsvToExcelConversion.xlsx");

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

workbook.SaveAs("CsvToExcelConversion.xlsx")

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

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

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

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

Dim workbook = WorkBook.Load("test.xlsx")

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

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

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

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

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

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

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

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

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

Dim workbook = WorkBook.Load("test.xlsx")

Dim options = New HtmlExportOptions() With {
	.OutputRowNumbers = True,
	.OutputColumnHeaders = True,
	.OutputHiddenColumns = True,
	.OutputHiddenRows = True,
	.OutputLeadingSpacesAsNonBreaking = True
}

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

7.2. 使用 GemBox.Spreadsheet 轉換電子錶格類型

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

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

Dim workbook = ExcelFile.Load("ComplexTemplate.xlsx")
workbook.Save("Convert.pdf", New PdfSaveOptions() With {.SelectionType = SelectionType.EntireFile})

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

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

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

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

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

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

' Load an Excel file into the ExcelFile object.
Dim workbook = ExcelFile.Load("CombinedTemplate.xlsx")

' Create image save options.
Dim imageOptions = New ImageSaveOptions(ImageSaveFormat.Png) With {
	.PageNumber = 0,
	.Width = 1240,
	.CropToContent = True
}

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

CSV 文件到 Excel

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

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

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

同樣，您可以使用 GemBox.Spreadsheet 將不同文件類型（XLS、XLSX、ODS、CSV 和 HTML）轉換。