如何在 C# 中连接或合并 Excel 文件

How to merge Excel files in C#

1. Create a new project in Visual Studio
2. Install IronXL library into the project
3. Load all the Excel files that need to be merged
4. Extract all the sheets from the loaded Excel sheet
5. Add the Sheets to a new Excel file
6. Save them into a separate file

IronXL Library

IronXL is a substitute for Microsoft Interop when it comes to managing Excel files in .NET applications. While IronXL offers a more straightforward, effective, and powerful method of manipulating Excel files programmatically in .NET settings, Microsoft Interop requires the use of the Interop components to communicate with Excel.

The use of IronXL has the following benefits:

• Performance and Resource Economy: IronXL performs better because it does not depend on the Excel application being installed on the PC.
• Readability and Simplicity: IronXL provides a more straightforward API, making it easier to read, write, and manipulate Excel files without the complexities of Microsoft Interop.
• Compatibility and Dependency: Since IronXL does not require Microsoft Excel to be installed, it eliminates dependencies and compatibility issues with various Office or Excel versions.
• Platform Independence: IronXL offers greater flexibility and ease of deployment across different environments and platforms, whereas Microsoft Interop is more closely tied to specific Microsoft Office versions.

For .NET developers who need to work with Excel files programmatically, IronXL is often a better choice due to its performance, user-friendliness, and reduced reliance on additional applications.

The decision between IronXL and Microsoft Interop may be influenced by factors such as the project's specific requirements, existing infrastructure, and the developer's familiarity with each library. Always consider your application's needs in making this choice. Visit this page to learn more about the IronXL library.

Creating a New Project in Visual Studio

To launch the Visual Studio application, click "File" from the menu, then "New Project," and choose "Console application."

Enter the project name and the file location. Click the "Create" button and choose the required .NET Framework as shown below.

The project's structure will depend on the selected application type. Use the console, Windows, or web application to build or execute the application and add code by entering the Program.cs file.

Then the library can be added, and the code tested.

Installing IronXL Library

To install the IronXL library, open the NuGet Package Manager Console and type the following command:

Install-Package IronXL.Excel

Alternatively, use the NuGet Package Manager to search for "IronXL" and download it from the list of related packages.

Combine Excel files using IronXL

With IronXL, we can combine multiple Excel files or worksheets into a single Excel file or worksheet using the following code:

using IronXL;
using System;

class Program
{
    static void Main(string[] args)
    {
        // Array of file paths for the Excel files to be merged
        string[] filePaths = { "file1.xlsx", "file2.xlsx" };

        // Create a new workbook to add sheets from the existing files
        WorkBook newWorkBook = WorkBook.Create();

        try
        {
            foreach (var filePath in filePaths)
            {
                // Load the existing Excel file into a workbook
                WorkBook existingWorkbook = WorkBook.LoadExcel(filePath);

                // Retrieve the sheets from the loaded workbook
                WorksheetsCollection sheetCollection = existingWorkbook.WorkSheets;

                // Add each sheet from the existing workbook to the new workbook
                foreach (var sheet in sheetCollection)
                {
                    newWorkBook.WorkSheets.Add(sheet);
                }
            }

            // Save the new workbook with a merged sheet collection
            newWorkBook.SaveAs("MergedBook.xls");
        }
        catch (Exception ex)
        {
            // Output any exceptions encountered during the merging process
            Console.WriteLine(ex.ToString());
        }
    }
}

using IronXL;
using System;

class Program
{
    static void Main(string[] args)
    {
        // Array of file paths for the Excel files to be merged
        string[] filePaths = { "file1.xlsx", "file2.xlsx" };

        // Create a new workbook to add sheets from the existing files
        WorkBook newWorkBook = WorkBook.Create();

        try
        {
            foreach (var filePath in filePaths)
            {
                // Load the existing Excel file into a workbook
                WorkBook existingWorkbook = WorkBook.LoadExcel(filePath);

                // Retrieve the sheets from the loaded workbook
                WorksheetsCollection sheetCollection = existingWorkbook.WorkSheets;

                // Add each sheet from the existing workbook to the new workbook
                foreach (var sheet in sheetCollection)
                {
                    newWorkBook.WorkSheets.Add(sheet);
                }
            }

            // Save the new workbook with a merged sheet collection
            newWorkBook.SaveAs("MergedBook.xls");
        }
        catch (Exception ex)
        {
            // Output any exceptions encountered during the merging process
            Console.WriteLine(ex.ToString());
        }
    }
}

Imports IronXL
Imports System

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Array of file paths for the Excel files to be merged
		Dim filePaths() As String = { "file1.xlsx", "file2.xlsx" }

		' Create a new workbook to add sheets from the existing files
		Dim newWorkBook As WorkBook = WorkBook.Create()

		Try
			For Each filePath In filePaths
				' Load the existing Excel file into a workbook
				Dim existingWorkbook As WorkBook = WorkBook.LoadExcel(filePath)

				' Retrieve the sheets from the loaded workbook
				Dim sheetCollection As WorksheetsCollection = existingWorkbook.WorkSheets

				' Add each sheet from the existing workbook to the new workbook
				For Each sheet In sheetCollection
					newWorkBook.WorkSheets.Add(sheet)
				Next sheet
			Next filePath

			' Save the new workbook with a merged sheet collection
			newWorkBook.SaveAs("MergedBook.xls")
		Catch ex As Exception
			' Output any exceptions encountered during the merging process
			Console.WriteLine(ex.ToString())
		End Try
	End Sub
End Class

Conclusion

IronXL is a popular add-on for Excel as it operates independently of any other external libraries. Since Microsoft Excel is self-contained, there is no need to have it installed separately, and tasks can be performed without additional dependencies. This contrasts with the Interop library, which requires other libraries to parse files for Word documents.

IronXL offers new ways to handle Microsoft Excel documents in programming. It allows operations like calculations, sorting, trimming, finding and replacing, and file storing, among others. It enables reading, writing, and managing Excel data efficiently.

The initial cost of IronXL is a $799. Alternatively, customers can opt for a one-year membership fee to receive support and software upgrades. A paid plan also offers protection against unauthorized redistribution. For a free trial of IronXL, click this link. For detailed pricing information, visit the IronXL licensing website. For further details on Iron Software products, visit this page.