了解如何从Excel读取数据并将其写入SQL Server是一个非常有用的技能。以下是实现此目标的完整攻略:
第一步:引入所需的库
在C#中读取和写入Excel需要使用外部库。我们需要下载并添加以下NuGet包:
- Microsoft.Office.Interop.Excel:允许操作Excel文件。
- Microsoft.ACE.OLEDB.12.0:允许使用OleDb连接器连接Excel文件。
第二步:连接Excel文件
我们需要指定Excel文件的路径,然后打开工作表,以便可以读取其中的数据。下面是连接Excel文件的示例代码:
string excelFilePath = @"C:\Test\Test.xlsx";
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", excelFilePath);
var excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
var excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConnection);
var excelReader = excelCommand.ExecuteReader();
while (excelReader.Read())
{
// 读取每一行数据
}
excelReader.Close();
excelConnection.Close();
第三步:连接SQL Server
连接SQL Server的方法与连接Excel文件类似。我们需要指定服务器地址和要连接的数据库。
以下是连接SQL Server的示例代码:
string serverName = "MyServerName";
string databaseName = "MyDatabaseName";
string connectionString = string.Format("Server={0};Database={1};Integrated Security=SSPI;", serverName, databaseName);
var sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
// 执行SQL语句
第四步:读取并写入数据
现在我们已经连接了Excel文件和SQL Server,我们需要读取Excel文件中的数据并将其写入SQL Server中。
以下是将Excel文件中的数据读取并将其写入SQL Server的示例代码:
while (excelReader.Read())
{
string column1 = excelReader[0].ToString();
string column2 = excelReader[1].ToString();
string column3 = excelReader[2].ToString();
string sql = string.Format("INSERT INTO MyTable(Column1, Column2, Column3) VALUES ('{0}', '{1}', '{2}')", column1, column2, column3);
var sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
示例说明
以下是两个示例,示例数据存储在Excel文件中。我们将读取该数据并将其写入SQL Server中。
示范1
Excel文件中包含以下数据:
Column1 | Column2 | Column3 |
---|---|---|
111 | 222 | 333 |
444 | 555 | 666 |
777 | 888 | 999 |
我们将读取此数据并将其写入名为MyTable
的SQL Server表中。
string excelFilePath = @"C:\Test\Test.xlsx";
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", excelFilePath);
var excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
var excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConnection);
var excelReader = excelCommand.ExecuteReader();
string serverName = "MyServerName";
string databaseName = "MyDatabaseName";
string connectionString = string.Format("Server={0};Database={1};Integrated Security=SSPI;", serverName, databaseName);
var sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
while (excelReader.Read())
{
string column1 = excelReader[0].ToString();
string column2 = excelReader[1].ToString();
string column3 = excelReader[2].ToString();
string sql = string.Format("INSERT INTO MyTable(Column1, Column2, Column3) VALUES ('{0}', '{1}', '{2}')", column1, column2, column3);
var sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
excelReader.Close();
excelConnection.Close();
sqlConnection.Close();
示范2
Excel文件中包含以下数据:
First Name | Last Name | |
---|---|---|
John | Doe | johndoe@example.com |
Jane | Doe | janedoe@example.com |
Bob | Smith | bobsmith@example.com |
我们将读取此数据并将其写入名为Users
的SQL Server表中。
string excelFilePath = @"C:\Test\Test.xlsx";
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", excelFilePath);
var excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
var excelCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConnection);
var excelReader = excelCommand.ExecuteReader();
string serverName = "MyServerName";
string databaseName = "MyDatabaseName";
string connectionString = string.Format("Server={0};Database={1};Integrated Security=SSPI;", serverName, databaseName);
var sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
while (excelReader.Read())
{
string firstName = excelReader[0].ToString();
string lastName = excelReader[1].ToString();
string email = excelReader[2].ToString();
string sql = string.Format("INSERT INTO Users(FirstName, LastName, Email) VALUES ('{0}', '{1}', '{2}')", firstName, lastName, email);
var sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
excelReader.Close();
excelConnection.Close();
sqlConnection.Close();
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:C# 从Excel读取数据向SQL server写入 - Python技术站