对比 IronXL 和 GemBox.Spreadsheet

IronXL Library

IronXL is a .NET C# library that facilitates reading and editing a wide range of spreadsheet formats. It does not require Microsoft Excel to be installed and it does not require Interop. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.

GemBox.Spreadsheet

GemBox.Spreadsheet is a library that allows you to create, read, write, edit, convert, and print spreadsheet files in your .NET C# applications. It’s up to 250 times faster than Microsoft Excel automation!

1. Create a Console Application

The following steps will help you create a console application:

• Download and install the latest Visual Studio IDE.
• Start Visual Studio and click on “Create new project.”

• Then, select Console App (.NET Framework) C# from the project templates displayed.

  

  Console Application

• Click Next.

• Here, specify the Framework version you would like to use. The latest one is recommended.

  

  Configure Project

• Click on Create to complete the process.

Now the project will be created and made ready to use.

2. IronXL C# Library Installation

IronXL library can be downloaded and installed using the following methods:

1. By using Visual Studio with NuGet Package Manager.
2. Download and install with the DLL Manually.

2.1. Visual Studio with NuGet Package Manager

Visual Studio 2022 provides the NuGet Package Manager to install NuGet packages in your projects. You can access it in multiple ways: through the Project Menu, or by right-clicking your project in the Solution Explorer.

Package Manager

• In the Browse tab, search for "IronXL.Excel" and click on the Install button to add the library.
• After this process has been completed, IronXL has been successfully installed.

Install-Package IronXL.Excel

using IronXL;

using IronXL;

Imports IronXL

3. GemBox.Spreadsheet Installation

Use the following command in NuGet Package Manager to install GemBox.Spreadsheet.

Install-Package GemBox.Spreadsheet

Search GemBox.Spreadsheet

Alternatively, you can download and run the setup installer.

Next, open the program.cs file as before and add the lines of code below the using statement that we added in the previous section:

using GemBox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

using GemBox.Spreadsheet;

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

Imports GemBox.Spreadsheet

' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

4. Create and Save a New Excel Workbook

An Excel Workbook can contain multiple worksheets. Both libraries provide the facility to create Excel workbooks containing one or more worksheets. Let's have a look at how each library accomplishes this.

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

// 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. Reading Excel Files

IronXL and GemBox are both capable of reading data from existing Excel files. Let's look at the sample code for each of the libraries one by one.

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

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. Working with Excel Formulas

Implementing formulas is one of the most important Excel features. Both libraries provide a powerful formula calculation engine. Formulas can be recalculated and saved to cells.

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

// 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. Inter Convert Files — (Excel, XLS, XLSX, XLSb, XLSM, XPS Data Interchange)

Both IronXL and GemBox.Spreadsheet provide the facility to convert between different spreadsheet file types.

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)

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)

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

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

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

8. Licensing

IronXL is a commercial C# Excel library. It is absolutely free for development purposes, but it must be licensed for commercial deployment. All IronXL Licenses are available for use with a single project, single developers, agencies, and global corporations, with or without SaaS and OEM redistribution. Each license includes a 30-day money-back guarantee, along with one year of product support and updates, validity for dev/staging/production, and a perpetual license (one-time purchase). The lite license starts at $799.

GemBox.Spreadsheet is also a commercial library. It can be used for free in both personal and commercial applications. However, it can also be licensed for professional use. Its licenses include usage for individual developers, small teams, and large companies. It provides royalty-free deployment (no server or OEM licenses), 12 months of free technical support, and free bug fixes and new releases. The single-developer package starts from $890.

9. Which .NET Spreadsheet Library Should Be Used?

This article has demonstrated how easy both libraries make it for their users to load, read, edit, and convert Microsoft Excel spreadsheets. Both IronXL and GemBox.Spreadsheet provide uniquely powerful approaches for accomplishing these activities, and are compelling choices in their own right for doing very general read and write automation tasks.

So, which .NET Excel library should developers use in their projects?

Here are some points that readers can consider when choosing between using IronXL or GemBox.Spreadsheet for their next big project:

1. Accessibility. IronXL prioritizes ease of use, accuracy, and speed. The library has been designed to enable users to accomplish common Excel programming tasks (loading, reading, writing, editing, range selection, etc.) quickly, and in the fewest lines of code possible. The website has a large quantity of tutorials, How-To Guides, and Code Examples written to help users get started and accomplish their big goals as quickly and as effortlessly as possible. Moreover, IronXL has a team of engineers on-call 24/5 to help users at a moment's notice with any problem they have.

   Developers may consider using IronXL if support, user-friendliness, and speed of implementation are of utmost importance.

   To note, GemBox.Spreadsheet's website also has a collection of detailed examples available to help developers make the most of their powerful feature sets.

2. Versatility. GemBox.Spreadsheet’s feature set enables developers to accomplish Excel automation tasks that go far beyond the basic ones discussed in this tutorial. Users can draw shapes on Excel worksheets, edit worksheets with custom fonts, create new worksheets with VBA macros, form controls, and chart components, and save Excel documents as PDFs with encryption and digital signatures, among other things. GemBox.Spreadsheet supports over 200 different formulas. In addition, the library has very sophisticated mechanisms for referencing cell ranges.

   GemBox.Spreadsheet is a compelling choice for developers who demand flexibility and power in their spreadsheet management tasks.

3. Interoperability. GemBox.Spreadsheet can work with Excel, LibreOffice, and Open Office spreadsheets, supporting or preserving most of these products’ unique features. For this reason, developers who need spreadsheets to function across these office products may consider GemBox.Spreadsheet as an attractive alternative.

4. Convertibility. IronXL can export data into many conventional open formats (XML, HTML, JSON, CSV, TSV), as well as into any Excel document type. IronXL can also convert spreadsheet data into binary, byte, and stream data, and to System.Data data structures.

   GemBox.Spreadsheet can also convert spreadsheet data to and from System.Data.DataSet and the System.Data.DataTable data types. In addition, the library can import and export as HTML, and can convert data into images and to PDF/A-compliant documents.

   The developer’s choice between both libraries in this case will depend on the data/file types required in his project.

5. Affordability. IronXL and GemBox.Spreadsheet both require premium licenses to be used commercially.

   The deciding factor between IronXL and GemBox.Spreadsheet as far as price is concerned will ultimately come down to budget, team sizes, and long-term development considerations. It is worth noting, however, that IronXL's starting $799 licensing price could be very attractive for developers and teams who are price sensitive.

Try IronXL's free trial to see the efficiency of IronXL. Purchase the complete Iron Suite to get four additional libraries for the price of just two IronXL licenses.