如何在 C# 中创建新的 Excel 工作簿

How to Create a new Excel Workbook in C#

In this tutorial, we'll learn the process of creating a new workbook using IronXL in C#. We'll walk through the necessary steps, from setting up your development environment to writing the code that generates a new Excel workbook.

By the end of this tutorial, you'll have a solid understanding of how to leverage IronXL to create Excel workbooks programmatically, empowering you to integrate Excel functionality into your C# applications with ease. Let's get started!

What is IronXL?

IronXL is a versatile C# library that allows you to work with Excel documents without the need for Microsoft Office Excel Interop or any Excel Application. It lets you easily read, create, and modify workbooks, format cells, add formulas, and work with both modern and older Excel file formats.

You can validate data, insert images, apply conditional formatting, and create charts without needing Microsoft Office. Developers can build Excel solutions for tasks like financial reports, data dashboards, and inventory management with ease by using IronXL.

Let's begin creating an Excel file in C#.

Step 1: Create a New C# Project

Open Visual Studio and create a new C# Console Application project. Name it as per your preference. You can also create other types of Projects such as ASP.NET MVC, Blazor, MAUI, WEB Forms, Windows Forms, WEB API, etc. This code will work with all project types. I am creating a console application for simplicity and making it relevant for all project types.

Step 2: Install IronXL NuGet Package

To install the IronXL package in your C# project, you can use any of the following ways:

1. To install IronXL, right-click on your project in Solution Explorer, choose "Manage NuGet Packages," search for IronXL, and then proceed with the installation.

2. Alternatively, you can install IronXL via the Package Manager Console using the following command:

Install-Package IronXL.Excel

This command will download, install, and add an assembly reference to our project. Wait for the package to be downloaded and installed. Once the installation is complete, you can start using IronXL in your project to work with Excel files programmatically.

Step 3: Import Necessary Namespace

At the top of your C# file, add the following namespace:

using IronXL;

using IronXL;

Imports IronXL

Step 4: Create a New Excel File

Now, let's write the code to create an Excel file:

internal class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook in the XLSX format
        WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        workBook.Metadata.Author = "Mr. Author"; // Set the author (optional)

        // Add a blank worksheet named "Sheet1"
        WorkSheet workSheet = workBook.CreateWorkSheet("Sheet1");

        // Add data to the new worksheet
        workSheet["A1"].Value = "Developer Name";
        workSheet["A2"].Value = "John Grahm";
        workSheet["A3"].Value = "David Smith";
        workSheet["A4"].Value = "Rishi Kelkar";

        // Save the Excel file as "Developers.xlsx"
        workBook.SaveAs("Developers.xlsx");
    }
}

internal class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook in the XLSX format
        WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        workBook.Metadata.Author = "Mr. Author"; // Set the author (optional)

        // Add a blank worksheet named "Sheet1"
        WorkSheet workSheet = workBook.CreateWorkSheet("Sheet1");

        // Add data to the new worksheet
        workSheet["A1"].Value = "Developer Name";
        workSheet["A2"].Value = "John Grahm";
        workSheet["A3"].Value = "David Smith";
        workSheet["A4"].Value = "Rishi Kelkar";

        // Save the Excel file as "Developers.xlsx"
        workBook.SaveAs("Developers.xlsx");
    }
}

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Create a new workbook in the XLSX format
		Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
		workBook.Metadata.Author = "Mr. Author" ' Set the author (optional)

		' Add a blank worksheet named "Sheet1"
		Dim workSheet As WorkSheet = workBook.CreateWorkSheet("Sheet1")

		' Add data to the new worksheet
		workSheet("A1").Value = "Developer Name"
		workSheet("A2").Value = "John Grahm"
		workSheet("A3").Value = "David Smith"
		workSheet("A4").Value = "Rishi Kelkar"

		' Save the Excel file as "Developers.xlsx"
		workBook.SaveAs("Developers.xlsx")
	End Sub
End Class

The above code demonstrates how to create an Excel file programmatically using IronXL in C#. It starts by creating a new Excel workbook (a new instance of an Excel file) in the XLSX format, sets author metadata, adds a blank Excel worksheet with the name "Sheet1" using the CreateWorkSheet() method, populates data in cells A1 to A4, and finally saves the workbook as "Developers.xlsx".

IronXL offers a range of functionalities for working with Excel files, including creation, manipulation, and saving in various formats like XLSX, CSV, TSV, JSON, XML, and HTML.

The output is as:

Step 5: Formatting and Styling Excel File

IronXL allows you to format cells, apply font styles, set background colors, and adjust alignment. You can create professional-looking spreadsheets by customizing cell appearance.

// Set style of heading for cell A1
workSheet["A1"].Style.BackgroundColor = "#FFFF66"; // Light yellow background
workSheet["A1"].Style.Font.Bold = true; // Bold font

// Set border style for a specific range (A1:A4)
var range = workSheet["A1:A4"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium bottom border
range.Style.LeftBorder.Type = BorderType.Medium; // Medium left border
range.Style.RightBorder.Type = BorderType.Medium; // Medium right border
range.Style.TopBorder.Type = BorderType.Medium; // Medium top border

// Set style of heading for cell A1
workSheet["A1"].Style.BackgroundColor = "#FFFF66"; // Light yellow background
workSheet["A1"].Style.Font.Bold = true; // Bold font

// Set border style for a specific range (A1:A4)
var range = workSheet["A1:A4"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium bottom border
range.Style.LeftBorder.Type = BorderType.Medium; // Medium left border
range.Style.RightBorder.Type = BorderType.Medium; // Medium right border
range.Style.TopBorder.Type = BorderType.Medium; // Medium top border

' Set style of heading for cell A1
workSheet("A1").Style.BackgroundColor = "#FFFF66" ' Light yellow background
workSheet("A1").Style.Font.Bold = True ' Bold font

' Set border style for a specific range (A1:A4)
Dim range = workSheet("A1:A4")
range.Style.BottomBorder.Type = BorderType.Medium ' Medium bottom border
range.Style.LeftBorder.Type = BorderType.Medium ' Medium left border
range.Style.RightBorder.Type = BorderType.Medium ' Medium right border
range.Style.TopBorder.Type = BorderType.Medium ' Medium top border

The above code demonstrates how to customize the appearance of specific cells in an Excel worksheet using IronXL in C#. It first sets the background color of cell A1 to a light yellow shade and makes the font bold, effectively styling it as a heading.

Next, it defines a range spanning cells A1 to A4 and sets medium-weight borders along the bottom, left, right, and top edges of this range, enhancing its visual distinction within the worksheet. These styling options allow developers to create visually appealing and organized Excel documents tailored to their specific needs.

The output is as:

Step 6: Formula and Calculation

You can add formulas to cells programmatically. IronXL supports a wide range of Excel functions.

// Add a new column to display the length of developer names
workSheet["B1"].Value = "Name Length";
workSheet["B1"].Style.BackgroundColor = "#FFFF66"; // Styled as heading
workSheet["B1"].Style.Font.Bold = true; // Bold font

// Formula to calculate the length of names in column B
workSheet["B2"].Value = "=LEN(A2)";
workSheet["B3"].Value = "=LEN(A3)";
workSheet["B4"].Value = "=LEN(A4)";

// Add a total count of the length of names in cell A5
workSheet["A5"].Value = "Sum of Length";
workSheet["B5"].Formula = "=SUM(B2:B4)";

// Add a new column to display the length of developer names
workSheet["B1"].Value = "Name Length";
workSheet["B1"].Style.BackgroundColor = "#FFFF66"; // Styled as heading
workSheet["B1"].Style.Font.Bold = true; // Bold font

// Formula to calculate the length of names in column B
workSheet["B2"].Value = "=LEN(A2)";
workSheet["B3"].Value = "=LEN(A3)";
workSheet["B4"].Value = "=LEN(A4)";

// Add a total count of the length of names in cell A5
workSheet["A5"].Value = "Sum of Length";
workSheet["B5"].Formula = "=SUM(B2:B4)";

' Add a new column to display the length of developer names
workSheet("B1").Value = "Name Length"
workSheet("B1").Style.BackgroundColor = "#FFFF66" ' Styled as heading
workSheet("B1").Style.Font.Bold = True ' Bold font

' Formula to calculate the length of names in column B
workSheet("B2").Value = "=LEN(A2)"
workSheet("B3").Value = "=LEN(A3)"
workSheet("B4").Value = "=LEN(A4)"

' Add a total count of the length of names in cell A5
workSheet("A5").Value = "Sum of Length"
workSheet("B5").Formula = "=SUM(B2:B4)"

The above code illustrates the utilization of formulas and functions in IronXL for calculating the length of developer names and computing the sum of these lengths within an Excel worksheet. Through this demonstration, developers can understand how to integrate formulas and functions within IronXL to perform dynamic calculations and manipulations within Excel worksheets programmatically, offering flexibility and automation in data processing tasks.

Firstly, a header titled "Name Length" is added to cell B1, with styling to highlight its significance. Subsequently, formulas are applied to cells B2, B3, and B4 to calculate the length of each developer's name using the LEN function, referencing the corresponding cell in column A. This enables automatic calculation of name lengths as the developer names change.

Additionally, a total count of developers' name lengths is computed in cell B5 using the SUM function, which adds up the values from cells B2 to B4.

By incorporating these formulas, the worksheet becomes dynamically updated. This use case might not be practical, but this is just for an example of using an Excel formula in code.

Conclusion

In summary, this tutorial has demonstrated the process of creating a new Excel workbook in C# using IronXL, a robust library facilitating Excel file manipulation within the C# ecosystem. With IronXL, developers can seamlessly integrate Excel functionality into their applications, from setting up the development environment to generating Excel workbooks programmatically. Alongside its ability to perform tasks like formatting, styling, and applying formulas, IronXL offers a comprehensive feature set for efficient data management and analysis.

For references on how to use IronXL, please visit the documentation page. IronXL also offers a collection of code examples that are helpful to get started.

Developers can explore IronXL through its free trial and purchase, ensuring a seamless transition from evaluation to full-scale implementation. For more details on perpetual licenses, please visit the license page link.