IronXL 和 Microsoft Office Interop Excel 的比较

IronXL Feature Set

• Load, Read, and Edit Data — from XLS/XLSX/CSV/TSV
• Saving and Exporting — to XLS/XLSX/CSV/TSV/JSON
• System.Data Objects — Work with Excel Spreadsheets as System.Data.DataSet and System.Data.DataTable objects.
• Formulas — works with Excel formulas. Formulas are recalculated every time a sheet is edited.
• Ranges — Easy-to-use WorkSheet ["A1:B10"] syntax. Combine and create ranges intuitively.
• Sorting — Sort Ranges, Columns, and Rows.
• Styling — Cell visual styles, font, size, background pattern, border, alignment, and number formats.

2.1. Using Visual Studio with NuGet packages

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

• Now, from the browse tab -> search for IronXL.Excel -> Install

• And we are done.

3.2. Using the NuGet Package Manager Console

Another way to download and install the Microsoft.Office.Interop.Excel C# Library is to make use of the following steps to install the NuGet package through the Developer Command Prompt.

1. Open the Developer Command Prompt — usually found in the Visual Studio folder.

2. Type the following command:

   PM> Install-Package Microsoft.Office.Interop.Excel

   PM> Install-Package Microsoft.Office.Interop.Excel

   SHELL
3. Press Enter.
4. This will download and install the package.
5. Reload your Visual Studio project and begin using it.

3.3. Add necessary using directives

1. In Solution Explorer, right-click the Program.cs file and then click View Code.

2. Add the following using directive to the top of the code file:

   using Excel = Microsoft.Office.Interop.Excel;

   using Excel = Microsoft.Office.Interop.Excel;

   Imports Excel = Microsoft.Office.Interop.Excel

   $vbLabelText   $csharpLabel

4.1. A New Excel Workbook and Sheet using IronXL

It could not be easier to create a new Excel Workbook using IronXL! Just one line of code! Yes, really. Add the following code to your static void Main function in the Program.cs file:

// Create a new workbook in XLSX format
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

// Create a new workbook in XLSX format
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

' Create a new workbook in XLSX format
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

Both XLS (older Excel file version) and XLSX (current and newer file version) file formats can be created with IronXL.

And, it’s even easier to create a default Worksheet:

// Create a new worksheet
var worksheet = workbook.CreateWorkSheet("IronXL Features");

// Create a new worksheet
var worksheet = workbook.CreateWorkSheet("IronXL Features");

' Create a new worksheet
Dim worksheet = workbook.CreateWorkSheet("IronXL Features")

You can now use the worksheet variable to set cell values and do almost everything an Excel file can do.

4.2. A New Excel Workbook and Sheet using Microsoft.Office.Interop.Excel

In order to create an Excel file using Microsoft.Office.Interop, Microsoft Excel needs to be installed. So, we need to check if Microsoft Excel is installed on the host machine — if not, it will simply return an exception that needs to be handled. The following example code samples allow you to check for an Excel application installation and create Excel workbooks and sheets:

Check Microsoft Excel installed:

// Start Excel and get Application object.
Excel.Application xlApp = new Excel.Application();  

// Check if Excel is installed  
if (xlApp == null)  
{  
    Console.WriteLine("Excel is not installed in the system...");
    return;  
}

// Start Excel and get Application object.
Excel.Application xlApp = new Excel.Application();  

// Check if Excel is installed  
if (xlApp == null)  
{  
    Console.WriteLine("Excel is not installed in the system...");
    return;  
}

' Start Excel and get Application object.
Dim xlApp As New Excel.Application()

' Check if Excel is installed  
If xlApp Is Nothing Then
	Console.WriteLine("Excel is not installed in the system...")
	Return
End If

Create Workbook and Worksheet:

// Create Workbook and Worksheet
object misValue = System.Reflection.Missing.Value;  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// Create Workbook and Worksheet
object misValue = System.Reflection.Missing.Value;  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

' Create Workbook and Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add(misValue)
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)

So far, looking at both the codes, the advantage lies with IronXL over Office Interop Excel. IronXL uses just one line of code to be able to work with Excel files, with no fuss and additional dependencies.

5.1. Read Excel Files using IronXL

The IronXL WorkBook class represents an Excel sheet. To open an Excel File using C#, we use WorkBook.Load and specify the path of the Excel file (.xlsx). The following one-line code is used to open the file for reading:

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

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

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

Each WorkBook can have multiple WorkSheet objects. These represent worksheets in the Excel document. If the workbook contains worksheets, retrieve them by name as follows:

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

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

' Open Sheet for reading
Dim worksheet = workbook.GetWorkSheet("sheetnamegoeshere")

Code for reading the cell values:

// 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. Read Excel Files using Microsoft.Office.Interop.Excel

This also uses one line of code to open a workbook and sheet. The code goes as follows:

Excel.Application xlApp = new Excel.Application();  

// Load WorkBook
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  

// Open Sheet for reading
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Excel.Application xlApp = new Excel.Application();  

// Load WorkBook
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  

// Open Sheet for reading
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Dim xlApp As New Excel.Application()

' Load WorkBook
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)

' Open Sheet for reading
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)

Code for reading the cell values:

// Get the entire range of cells
Excel.Range xlRange = xlWorkSheet.UsedRange;

// Reading first 10 rows with two columns
for (int rowCount = 1; rowCount <= 10; rowCount++)  
{  
    string firstValue = Convert.ToString((xlRange.Cells[rowCount, 1] as Excel.Range).Text);  
    string secondValue = Convert.ToString((xlRange.Cells[rowCount, 2] as Excel.Range).Text);  

    Console.WriteLine(firstValue + "\t" + secondValue);  
}

// Get the entire range of cells
Excel.Range xlRange = xlWorkSheet.UsedRange;

// Reading first 10 rows with two columns
for (int rowCount = 1; rowCount <= 10; rowCount++)  
{  
    string firstValue = Convert.ToString((xlRange.Cells[rowCount, 1] as Excel.Range).Text);  
    string secondValue = Convert.ToString((xlRange.Cells[rowCount, 2] as Excel.Range).Text);  

    Console.WriteLine(firstValue + "\t" + secondValue);  
}

Imports Microsoft.VisualBasic

' Get the entire range of cells
Dim xlRange As Excel.Range = xlWorkSheet.UsedRange

' Reading first 10 rows with two columns
For rowCount As Integer = 1 To 10
	Dim firstValue As String = Convert.ToString((TryCast(xlRange.Cells(rowCount, 1), Excel.Range)).Text)
	Dim secondValue As String = Convert.ToString((TryCast(xlRange.Cells(rowCount, 2), Excel.Range)).Text)

	Console.WriteLine(firstValue & vbTab & secondValue)
Next rowCount

However, checking the Excel installation and creating its instance is an integral part.

6.1. Working with Range of Values Using IronXL

In IronXL, with one line of code, we can get the range of values of particular cells. Then, using a loop, each cell value can be read or edited. The code goes as follows:

// Load the workbook and get the first worksheet
WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

// This is how we get range from Excel worksheet
var range = sheet["A2:A8"];
// This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}

// Load the workbook and get the first worksheet
WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.WorkSheets.First();

// This is how we get range from Excel worksheet
var range = sheet["A2:A8"];
// This is how we can iterate over our range and read or edit any cell
foreach (var cell in range)
{
    Console.WriteLine(cell.Value);
}

' Load the workbook and get the first worksheet
Dim workbook As WorkBook = WorkBook.Load("test.xls")
Dim sheet As WorkSheet = workbook.WorkSheets.First()

' This is how we get range from Excel worksheet
Dim range = sheet("A2:A8")
' This is how we can iterate over our range and read or edit any cell
For Each cell In range
	Console.WriteLine(cell.Value)
Next cell

6.2. Working with Range of Values Using Microsoft.Office.Interop.Excel

This also uses one line of code to get the range of cell values. The cells can then be updated with the required values. The code goes as follows:

Excel.Application xlApp = new Excel.Application();  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// Get range of values and updating their format
var range = xlWorkSheet.get_Range("A1", "D1");
range.NumberFormat = "$0.00";

Excel.Application xlApp = new Excel.Application();  
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// Get range of values and updating their format
var range = xlWorkSheet.get_Range("A1", "D1");
range.NumberFormat = "$0.00";

Dim xlApp As New Excel.Application()
Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(filePath)
Dim xlWorkSheet As Excel.Worksheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)

' Get range of values and updating their format
Dim range = xlWorkSheet.get_Range("A1", "D1")
range.NumberFormat = "$0.00"

7.1. Working with Excel Formulas using IronXL

After loading the workbook and worksheet, the following code sample can be used to either make changes to formulas or apply them to specific cells. The code goes as follows:

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

You can also retrieve formulas and their values.

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

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

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

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

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

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

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

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

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

7.2. Working with Excel Formulas using Microsoft.Office.Interop.Excel

Similarly, after loading the workbook and worksheet, the following code can be used to work on Excel formulas. The code sample goes as follows:

// Get range of values and updating their format
var range1 = xlWorkSheet.get_Range("A1", "D1");
var range2 = xlWorkSheet.get_Range("C2", "C6");

// Set Formula
range1.Formula = "=RAND()*100000";
range2.Formula = "=A2 & \" \" & B2";

// Get range of values and updating their format
var range1 = xlWorkSheet.get_Range("A1", "D1");
var range2 = xlWorkSheet.get_Range("C2", "C6");

// Set Formula
range1.Formula = "=RAND()*100000";
range2.Formula = "=A2 & \" \" & B2";

' Get range of values and updating their format
Dim range1 = xlWorkSheet.get_Range("A1", "D1")
Dim range2 = xlWorkSheet.get_Range("C2", "C6")

' Set Formula
range1.Formula = "=RAND()*100000"
range2.Formula = "=A2 & "" "" & B2"