C# 最快的方法将 DataTable 导出到 Excel

Prerequisites

To use IronXL in exporting data from DataTable to Excel file in C#, we need the following components to be installed on a local computer. Let's have a look at them one by one.

1. Visual Studio - Visual Studio is the IDE for C# programming and must be installed. You can download and install the latest version from Visual Studio website.

   • Once the IDE is set up, a Console application/Windows form needs to be created which will help to export DataTable to Excel. Following screenshots show how to create a project.

     

     Now, choose your project type. In our example, we will be creating a Console App.

     

     Name your project and choose the location it will be saved to.

     

     Finally, choose your .NET Framework, and click "Create".

     

     After clicking Create in the last screenshot, the project with the name "DemoApp" is created.

2. IronXL library - The IronXL library must be downloaded and installed in the Visual Studio project. There are multiple ways to do it.

   • Using Visual Studio - It provides the NuGet Package Manager to install IronXL. You can access it via the Tools menu or Solution Explorer. The following screenshots help to install IronXL. First, navigate to "Tools" in the top bar, or right-click within your solution explorer.

     

     

     Go to Manage NuGet Packages for Solution, and search for IronXL. Then, you just have to press "Install" and the IronXL library will be added to your project.

     

   • Developer Command Prompt - Open the Developer Command Prompt either from the Visual Studio Tools menu or from the Visual Studio folder. Type the following command to download and install IronXL in your project:

     PM > Install-Package IronXL.Excel

     PM > Install-Package IronXL.Excel

     SHELL
   • Directly from NuGet Package Manager - Navigate to this URL https://www.nuget.org/packages/ironxl.excel/ to download IronXL.

3. Add Necessary Namespaces - To create DataTable and use IronXL, both should be referenced on top of the Program.cs file.

   using IronXL; // Add reference to the IronXL library
   using System.Data; // Add reference to System.Data for DataTable

   using IronXL; // Add reference to the IronXL library
   using System.Data; // Add reference to System.Data for DataTable

   Imports IronXL ' Add reference to the IronXL library
   Imports System.Data ' Add reference to System.Data for DataTable

   $vbLabelText   $csharpLabel

Once all the prerequisites are fulfilled, it's time to export data from DataTable to Excel sheet.

Create a DataTable in C#

The following code creates a new data table with two column headers and multiple rows:

// Create a new DataTable object
DataTable dt = new DataTable();
// Add column names to the DataTable
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
// Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars");
dt.Rows.Add("Dog", "Barks");
dt.Rows.Add("Cat", "Meows");
dt.Rows.Add("Goat", "Bleats");
dt.Rows.Add("Wolf", "Howls");
dt.Rows.Add("Cheetah", "Purrs");

// Create a new DataTable object
DataTable dt = new DataTable();
// Add column names to the DataTable
dt.Columns.Add("Animal");
dt.Columns.Add("Sound");
// Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars");
dt.Rows.Add("Dog", "Barks");
dt.Rows.Add("Cat", "Meows");
dt.Rows.Add("Goat", "Bleats");
dt.Rows.Add("Wolf", "Howls");
dt.Rows.Add("Cheetah", "Purrs");

' Create a new DataTable object
Dim dt As New DataTable()
' Add column names to the DataTable
dt.Columns.Add("Animal")
dt.Columns.Add("Sound")
' Add rows representing different animals and their sounds
dt.Rows.Add("Lion", "Roars")
dt.Rows.Add("Dog", "Barks")
dt.Rows.Add("Cat", "Meows")
dt.Rows.Add("Goat", "Bleats")
dt.Rows.Add("Wolf", "Howls")
dt.Rows.Add("Cheetah", "Purrs")

First, we create a new DataTable called "dt". Then, using Columns.Add, we can add a specified number of columns to the data table by name; in our example, we have two columns called "Animal" and "Sound". We then use Rows.Add to add new rows, placing the content for each row within the brackets. The content is separated by commas, separating each string by column.

Create Excel File using IronXL in C#

When creating Excel file types from scratch using IronXL, it is a two-step process and very easy to implement in C#. IronXL first creates an Excel workbook and then helps add a worksheet to it. The following sample code demonstrates how to create a workbook along with the worksheet:

// Create a new workbook in XLSX format
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
// Reference to the default worksheet in the workbook
WorkSheet ws = wb.DefaultWorkSheet;

// Create a new workbook in XLSX format
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
// Reference to the default worksheet in the workbook
WorkSheet ws = wb.DefaultWorkSheet;

' Create a new workbook in XLSX format
Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
' Reference to the default worksheet in the workbook
Dim ws As WorkSheet = wb.DefaultWorkSheet

Exporting Data from DataTable to Excel Worksheet using IronXL

// Assign DataTable column names to the first row of the Excel worksheet
ws["A1"].Value = dt.Columns[0].ToString();
ws["B1"].Value = dt.Columns[1].ToString();
// Start adding data from the second row
int rowCount = 2;

// Assign DataTable column names to the first row of the Excel worksheet
ws["A1"].Value = dt.Columns[0].ToString();
ws["B1"].Value = dt.Columns[1].ToString();
// Start adding data from the second row
int rowCount = 2;

' Assign DataTable column names to the first row of the Excel worksheet
ws("A1").Value = dt.Columns(0).ToString()
ws("B1").Value = dt.Columns(1).ToString()
' Start adding data from the second row
Dim rowCount As Integer = 2

// Iterate through each row in the DataTable and add to Excel worksheet
foreach (DataRow row in dt.Rows)
{
    ws["A" + (rowCount)].Value = row[0].ToString();
    ws["B" + (rowCount)].Value = row[1].ToString();
    rowCount++;
}

// Iterate through each row in the DataTable and add to Excel worksheet
foreach (DataRow row in dt.Rows)
{
    ws["A" + (rowCount)].Value = row[0].ToString();
    ws["B" + (rowCount)].Value = row[1].ToString();
    rowCount++;
}

' Iterate through each row in the DataTable and add to Excel worksheet
For Each row As DataRow In dt.Rows
	ws("A" & (rowCount)).Value = row(0).ToString()
	ws("B" & (rowCount)).Value = row(1).ToString()
	rowCount += 1
Next row

// Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");

// Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx");

' Save the workbook to a file
wb.SaveAs("DataTable_to_Excel_IronXL.xlsx")

// Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");

// Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv");
wb.SaveAsJson("DataTable_to_Excel_IronXL.json");
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml");

' Save the workbook in different file formats
wb.SaveAsCsv("DataTable_to_Excel_IronXL.csv")
wb.SaveAsJson("DataTable_to_Excel_IronXL.json")
wb.SaveAsXml("DataTable_to_Excel_IronXL.xml")