使用 C# 读取 Excel 数据并插入数据库

1. IronXL

IronXL is a powerful software library designed for .NET developers, providing them with an easy-to-use API to read, write, and manipulate Excel files in their .NET applications. It offers a comprehensive set of features for creating, editing, and exporting Excel spreadsheets, all without requiring Microsoft Office or Excel to be installed on the target machine. With support for a wide range of file formats, including XLS, XLSX, CSV, TSV, and more, this library makes it easy for developers to create Excel-based applications that can be deployed anywhere. IronXL also offers advanced features like chart creation, data visualization, and data analysis to streamline the development process for small to large-scale projects.

2. Prerequisites

Before you can use the IronXL library to write to a database from an Excel file, you must fulfill certain prerequisites. These include:

• Having Visual Studio installed on your computer to create a C# project.
• Ensuring that ASP.NET is also installed on your system before creating a C# project.
• Installing the IronXL library on your system to export data. You can obtain it by downloading the IronXL NuGet package from the NuGet Package Manager in Visual Studio.
• Having SQL installed in Visual Studio.

3. Creating a New Project in Visual Studio

Before you can utilize the IronXL library to perform Excel-related operations, you need to create a .NET project in Visual Studio. While any version of Visual Studio is compatible, it is recommended to use the latest version available. You can choose from various project templates such as Windows Forms and ASP.NET, based on your project requirements. For this tutorial, the Console Application project template is recommended to demonstrate how to work with IronXL.

Create a new project

Once you have chosen the project type, you need to name the project and select its location. You can also specify the preferred framework, such as .NET Core 6, for the project.

Project configuration

After the new project is created, you can access the program.cs file where you can write code and execute the application.

Project with code open

Now the Visual Studio project is created, let's install IronXL.

4. Install IronXL

The IronXL library can be downloaded and installed in different ways, but for this article, two simplest methods are covered:

• Using NuGet packages in Visual Studio.
• Using the Visual Studio Command-Line.

5. Installing and Configuring the SQL Server Database

To install and integrate the SQL server database table with your C# project, first, go to the NuGet Package Manager, search for System.Data.SqlClient, and install it.

Search and install SqlClient in NuGet Package Manager UI

Once installed, go to the project menu and click on "Add New Item".

Add New Item

A new window will appear. Select data from the side menu and then click on Service-Based Database from the list. Write an appropriate name for the database and click the Add button.

Select Service-based Database

Then, in the Solution Explorer, right-click on the newly created database and select "Open". This will open a new sidebar.

Right-click and select Open

In the new sidebar, click on your database and go to its properties. From there, copy the connection string.

Right-click and select Properties

Handle Connection String

After copying the connection preferences, click on your database instance to open a new list. Right-click on the table folder and select "Add New Table".

Add New Table

To create a new table in the database, follow these steps:

1. Open a new data table design page.
2. Add the following SQL Query, which will create a new table with three columns: Id, Name, and Number.
3. Click on the "Update" button at the top of the page.
4. The newly generated table will be added to the database.

CREATE TABLE [dbo].[Table]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [Name] VARCHAR(100) NOT NULL,
  [Number] INT
)

Now that the SQL environment is set up, let's create some sample data to fill this database from Excel data.

Excel file data

6. Import Excel File Data and Export to a Database using IronXL

With IronXL, developers can automate the process of transferring data between Excel files and databases, which can save a significant amount of time and effort. By utilizing IronXL, developers can streamline their workflow and eliminate the need for manual data entry, ensuring that the data is accurate and up-to-date.

Once the SQL server is set up and the connection string is copied, just paste the connection string into the code below, link your Excel file with the code, and change the SQL query if needed. Then simply run the code and the data will be exported to the database table.

using IronXL; // Import the IronXL library
using System.Data; // Import data handling library
using System.Data.SqlClient; // Import SQL Client library for SQL database operations

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("book.xlsx");

// Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Define the SQL query to select data from the table
string sql = "SELECT * FROM [dbo].[Table]";

// Connection string for the SQL Server
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"; 

// Open a connection to the SQL Server using the connection string
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open(); // Open connection
    // Use SqlDataAdapter to update SQL table with DataSet from Excel file
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    adapter.Update(dataSet);
}

using IronXL; // Import the IronXL library
using System.Data; // Import data handling library
using System.Data.SqlClient; // Import SQL Client library for SQL database operations

// Load the Excel workbook
WorkBook workBook = WorkBook.Load("book.xlsx");

// Convert the workbook into a DataSet
DataSet dataSet = workBook.ToDataSet();

// Define the SQL query to select data from the table
string sql = "SELECT * FROM [dbo].[Table]";

// Connection string for the SQL Server
string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"; 

// Open a connection to the SQL Server using the connection string
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open(); // Open connection
    // Use SqlDataAdapter to update SQL table with DataSet from Excel file
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    adapter.Update(dataSet);
}

Imports IronXL ' Import the IronXL library
Imports System.Data ' Import data handling library
Imports System.Data.SqlClient ' Import SQL Client library for SQL database operations

' Load the Excel workbook
Private workBook As WorkBook = WorkBook.Load("book.xlsx")

' Convert the workbook into a DataSet
Private dataSet As DataSet = workBook.ToDataSet()

' Define the SQL query to select data from the table
Private sql As String = "SELECT * FROM [dbo].[Table]"

' Connection string for the SQL Server
Private connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"

' Open a connection to the SQL Server using the connection string
Using connection As New SqlConnection(connectionString)
	connection.Open() ' Open connection
	' Use SqlDataAdapter to update SQL table with DataSet from Excel file
	Dim adapter As New SqlDataAdapter(sql, connection)
	adapter.Update(dataSet)
End Using

To check if the data has been successfully exported to the database, right-click on the table directory again and click on "New Query." A new page will open, select your database from the top bar and run the appropriate SQL query to retrieve the data.

SELECT * FROM [dbo].[Table]

Press the green button, and the result will be displayed in a second.

Database data

This is how you can write data imported from a Microsoft Excel file to a database.

7. Conclusion

Working with Excel spreadsheets is a common task in many applications, and inserting data from an Excel sheet into a database table can streamline data management processes. One way to achieve this task in C# is by using libraries that allow reading and manipulating Excel files, such as IronXL. By utilizing this library, developers can easily extract data from an Excel sheet and insert it into a database table, simplifying the data management process and reducing the chances of errors. This article covers the steps involved in adding data from an Excel file to a SQL Server table in a SQL Server database using the IronXL library. It also provides a brief introduction to the IronXL library, discusses the prerequisites needed to insert data, and describes how to create a new project in Visual Studio, install IronXL, and configure an SQL Server database. Please visit the following tutorial to learn how to read Excel files in C#.

Additionally, IronXL also offers advanced features including support for cell formatting such as text alignment, font size, color, freeze panel, adding formulas, applying conditional formatting, and encryption with a password.

Users can also benefit from Iron Suite, a collection of software development tools that includes IronPDF, IronOCR, IronXL, IronBarcode, and IronWebscraper.