对比 IronXL 和 GemBox.Spreadsheet

IronXL功能集

• 从 XLS/XLSX/CSV/TSV 加载、读取和编辑数据
• 导出并保存为不同格式--XLS/XLSX/CSV/TSV/JSON
• System.Data 对象 - 它可作为 System.Data.DataSet 和 System.Data.DataTable 对象与 Excel WorkBook 电子表格配合使用。
• 可使用公式 - 可使用 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 文件
3. 使用Excel公式 7.Inter Convert 文件
4. 授权
5. 结论

2.1.Visual Studio 与 NuGet 包管理器。

Visual Studio 2022 提供了 NuGet 包管理器，用于在项目中安装 NuGet 包。 您可以通过多种方式访问它：通过项目菜单，或在解决方案资源管理器中右键单击您的项目。

软件包管理器

• 在 "浏览 "选项卡中搜索 "IronXL.Excel"，然后单击 "安装 "按钮添加该库。
• 完成这一过程后，IronXL 即已成功安装。

2.2.手动下载并安装 DLL.

下载和安装 IronXL Library 的另一种方法是使用 NuGet 软件包管理器控制台。

• 打开 Developer Command Prompt 控制台。

键入以下命令：

Install-Package IronXL.Excel

• 按回车键。 这将自动下载软件包并安装到您的项目中。

2.4.包含 IronXL 库。

1.打开 Program.cs 文件。 2.在代码文件顶部添加以下代码行：

using IronXL;

using IronXL;

Imports IronXL

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

使用 IronXL.Excel 创建一个新的 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 读取 Excel 文件。

IronXL.Excel 中的 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 文件之间进行转换。