IronXL 和 GemBox.Spreadsheet 的比較

IronXL 功能集

• 載入、讀取和編輯資料——來自 XLS/XLSX/CSV/TSV 文件
• 支援匯出並儲存為多種格式－XLS/XLSX/CSV/TSV/JSON
• System.Data物件 — 它可與 Excel 工作簿電子表格搭配使用，作為System.Data.DataSet和System.Data.DataTable物件。
• 支援公式 — 支援 Excel 公式。 每次編輯工作表時，公式都會重新計算。
• 支援區域操作 — 其易於使用的工作表語法可讓您直觀地建立和合併區域。
• 篩選與排序 — 範圍、列和行可以排序。
• 設定單元格樣式 — 視覺樣式、字體、大小、背景圖案、邊框、對齊方式和數字格式。

GemBox.電子表格 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. Visual Studio 與 NuGet 套件管理器

Visual Studio 2022 提供了 NuGet 套件管理器，用於在專案中安裝 NuGet 套件。 您可以透過多種方式存取它：透過專案選單，或在解決方案資源管理器中右鍵單擊您的專案。

軟體套件管理器

• 在"瀏覽"標籤中，搜尋"IronXL.Excel"，然後按一下"安裝"按鈕以新增庫。
• 此程序完成後，IronXL 已成功安裝。

2.2. 手動下載並安裝 DLL 文件

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

• 開啟開發者命令提示字元控制台。

輸入以下命令：

Install-Package IronXL.Excel

• 按回車鍵。 這將自動下載並將軟體包安裝到您的專案中。

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 從 C# 應用程式在 XLS、XLSX、ODS、CSV 和 HTML 檔案之間進行轉換。