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 套件。 您可以通過多種方式存取這個功能：通過專案選單，或者右鍵點擊解決方案管理器中的專案。

包管理器

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

2.2. 手動下載並安裝 DLL

另一種下載和安裝 IronXL Library 的方法是使用 NuGet 套件管理器控制台。

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

輸入以下指令：

Install-Package IronXL.Excel

• 按下 Enter 鍵。 這將自動在您的專案中下載和安裝該套件。

2.4. 包含 IronXL 程式庫

1. 打開 Program.cs 文件。
2. 在程式碼文件的開頭新增以下程式碼行：

using IronXL;

using IronXL;

Imports IronXL

4.1. 使用IronXL建立新的Excel工作簿和工作表

using 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 在 C# 應用中實現 XLS、XLSX、ODS、CSV 和 HTML 文件之間的轉換。