c#winform导入excel数据并存储到SQL Server数据库中
时间: 2024-01-15 20:04:44 浏览: 25
首先,你需要安装 Microsoft.Office.Interop.Excel 和 System.Data.SqlClient 两个 NuGet 包。
然后,你可以使用以下代码将 Excel 数据导入到 SQL Server 数据库中:
```csharp
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
// 导入 Excel 数据到 DataTable
public DataTable ImportExcelToDataTable(string filePath)
{
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(filePath);
Worksheet worksheet = workbook.ActiveSheet;
Range range = worksheet.UsedRange;
DataTable dataTable = new DataTable();
for (int i = 1; i <= range.Columns.Count; i++)
{
dataTable.Columns.Add(range.Cells[1, i].Value2.ToString());
}
for (int i = 2; i <= range.Rows.Count; i++)
{
DataRow dataRow = dataTable.NewRow();
for (int j = 1; j <= range.Columns.Count; j++)
{
dataRow[j - 1] = range.Cells[i, j].Value2.ToString();
}
dataTable.Rows.Add(dataRow);
}
workbook.Close();
excel.Quit();
return dataTable;
}
// 将 DataTable 存储到 SQL Server 数据库
public void SaveDataTableToSqlServer(DataTable dataTable, string connectionString, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
foreach (DataColumn column in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
bulkCopy.WriteToServer(dataTable);
}
}
}
```
使用时,你可以这样调用:
```csharp
string filePath = "path/to/excel/file.xlsx";
string connectionString = "Data Source=your_server_name;Initial Catalog=your_database_name;Integrated Security=True";
string tableName = "your_table_name";
DataTable dataTable = ImportExcelToDataTable(filePath);
SaveDataTableToSqlServer(dataTable, connectionString, tableName);
```
注意:这种方法适用于 Excel 文件中的数据与 SQL Server 数据库表的列名称一致的情况。如果不一致,你需要在代码中进行相应的处理。