C# 中 DataTable 到 Excel（代码示例教程）

IronXL Features

• IronXL runs on a variety of Operating Systems, including Windows, Linux, and macOS.
• Almost all of the .NET Frameworks are supported by IronXL, including the Console, Windows Forms Application, and Web Application.
• IronXL makes reading Excel files straightforward and easy.
• We can load, edit, export Datatables, export datasets, and read Excel file formats such as XLSX files, XLS, CSV, TSV, XLST, XLSM files, and more with IronXL.
• IronXL allows us to save and export files with a variety of extensions, including XLS, CSV, TSV, JSON, XLSX, and others.
• Excel formulas can be created by IronXL.
• Text, Numbers, Formulas, Dates, Currency, Percentages, and other Excel cell data formats are supported by IronXL.
• It also provides numerous sorting options such as Range, Columns, and Rows, among others.
• Cell styling in IronXL includes font, size, background pattern, border, and alignment, among others.
• We can read and export data from an Excel file using IronXL.
• IronXL does not require Excel or Interop to be installed on your server. The IronXL API is faster and easier to use than Microsoft Office, Interop, and Excel.

1 Creating a New Project in Visual Studio

To use the IronXL library, we must create a .NET project in Visual Studio. You can use any version of Visual Studio, but the latest version is recommended. You can create an application like Windows Forms or different project templates, depending on your requirements. I will use the Console Application for this tutorial, for simplicity.

Create a new project UI in Visual Studio

Next, enter the project name and the location of the project.

Configuration step in creating a new project

Next, select the framework below. In this project, we are going to use .NET Core 6.

.Net Framework selection UI

Once the Application creates the solution, it will open the program.cs file where you can enter the code and build/run the application.

Empty program.cs file on the new project

Next, we can add the library to test the code.

2 Install the IronXL Library

The IronXL Library can be downloaded and installed in four ways.

These are:

• Using Visual Studio
• Using the Visual Studio Command Line.
• Direct download from the NuGet website.
• Direct download from the IronXL website.

Install-Package IronXL.Excel

3 Export Datatable to Excel File using IronXL

IronXL allows us to export DataTables to Excel files easily with a limited number of steps.

First, we need to include the namespace of IronXL as in the code screenshot below. Once it is added, we can use the IronXL classes and methods in our code.

Include namespace of IronXL before using the library

IronXL allows us to create Excel files and convert them into workbook objects. After converting them into objects, we can perform various types of operations. In the sample code below, we will convert a DataTable into an Excel worksheet, and then we can create an Excel file.

using System;
using System.Data;
using IronXL;

public class ExcelExporter
{
    /// <summary>
    /// Exports a DataTable to an Excel file using IronXL.
    /// </summary>
    /// <param name="filePath">The file path where the Excel file will be saved.</param>
    public static void ExportToExcel(string filePath)
    {
        // Creating a DataTable with a single column
        DataTable table = new DataTable();
        table.Columns.Add("DataSet_Animal", typeof(string));

        // Adding some rows to the DataTable
        table.Rows.Add("Lion");
        table.Rows.Add("Tiger");
        table.Rows.Add("Cat");
        table.Rows.Add("Goat");
        table.Rows.Add("Panther");
        table.Rows.Add("Fox");
        table.Rows.Add("Cheetah");

        // Creating a new Excel Workbook
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);

        // Accessing the default worksheet
        WorkSheet ws = wb.DefaultWorkSheet;

        // Iterating through the DataTable rows
        int rowCount = 1;
        foreach (DataRow row in table.Rows)
        {
            // Adding each DataRow's first column value to the corresponding cell in the worksheet
            ws["A" + rowCount].Value = row[0].ToString();
            rowCount++;
        }

        // Saving the workbook to a CSV file at specified file path with ";" as delimiter
        wb.SaveAsCsv(filePath, ";");
    }
}

using System;
using System.Data;
using IronXL;

public class ExcelExporter
{
    /// <summary>
    /// Exports a DataTable to an Excel file using IronXL.
    /// </summary>
    /// <param name="filePath">The file path where the Excel file will be saved.</param>
    public static void ExportToExcel(string filePath)
    {
        // Creating a DataTable with a single column
        DataTable table = new DataTable();
        table.Columns.Add("DataSet_Animal", typeof(string));

        // Adding some rows to the DataTable
        table.Rows.Add("Lion");
        table.Rows.Add("Tiger");
        table.Rows.Add("Cat");
        table.Rows.Add("Goat");
        table.Rows.Add("Panther");
        table.Rows.Add("Fox");
        table.Rows.Add("Cheetah");

        // Creating a new Excel Workbook
        WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);

        // Accessing the default worksheet
        WorkSheet ws = wb.DefaultWorkSheet;

        // Iterating through the DataTable rows
        int rowCount = 1;
        foreach (DataRow row in table.Rows)
        {
            // Adding each DataRow's first column value to the corresponding cell in the worksheet
            ws["A" + rowCount].Value = row[0].ToString();
            rowCount++;
        }

        // Saving the workbook to a CSV file at specified file path with ";" as delimiter
        wb.SaveAsCsv(filePath, ";");
    }
}

Imports System
Imports System.Data
Imports IronXL

Public Class ExcelExporter
	''' <summary>
	''' Exports a DataTable to an Excel file using IronXL.
	''' </summary>
	''' <param name="filePath">The file path where the Excel file will be saved.</param>
	Public Shared Sub ExportToExcel(ByVal filePath As String)
		' Creating a DataTable with a single column
		Dim table As New DataTable()
		table.Columns.Add("DataSet_Animal", GetType(String))

		' Adding some rows to the DataTable
		table.Rows.Add("Lion")
		table.Rows.Add("Tiger")
		table.Rows.Add("Cat")
		table.Rows.Add("Goat")
		table.Rows.Add("Panther")
		table.Rows.Add("Fox")
		table.Rows.Add("Cheetah")

		' Creating a new Excel Workbook
		Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)

		' Accessing the default worksheet
		Dim ws As WorkSheet = wb.DefaultWorkSheet

		' Iterating through the DataTable rows
		Dim rowCount As Integer = 1
		For Each row As DataRow In table.Rows
			' Adding each DataRow's first column value to the corresponding cell in the worksheet
			ws("A" & rowCount).Value = row(0).ToString()
			rowCount += 1
		Next row

		' Saving the workbook to a CSV file at specified file path with ";" as delimiter
		wb.SaveAsCsv(filePath, ";")
	End Sub
End Class

In the above code, we are exporting the DataTable to an Excel file. First, we are creating a DataTable, and then we are creating column headers. After creating the columns, we add the rows one by one. Once the rows are added, we create the WorkBook object. By using the object, we can add the data to the Excel sheet, and then we can save the data to the location. We are creating the WorkSheet object which allows for creating a worksheet, and then we can add this to the WorkBook object.

We are using a foreach loop to read the DataTable values one by one and then we add the value to the worksheet. Once all the values are added to the worksheet, we then save them into a CSV file using the method called SaveAsCsv — we need to pass the delimiter and file name with location as a parameter. A delimiter is an optional parameter that we can ignore if it is not required.

The completed code in Visual Studio

The above is the full screenshot of the code which we are using on the .NET Core 6.

Result:

The result when opening the file in Microsoft Excel

Above is the result of the executed code sample. In the screenshot, all the data available in the DataTable has been added to the newly created Excel sheet one by one.

4 Conclusion

IronXL is one of the most commonly used Excel libraries. It does not depend on any other third-party library. It is independent and does not need MS Excel to be installed. It works on multiple platforms. The introductory price for IronXL starts at $799. Also, it provides the option of a one-year fee for product support and updates. IronXL provides royalty-free redistribution coverage at an extra cost. To know more about the price details, visit our licensing page.