excel导入 一对多对多代码实现
时间: 2024-02-11 22:13:44 浏览: 43
以下是使用C#语言实现将Excel中的数据导入到一对多或多对多关系的示例代码:
```csharp
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using ExcelDataReader;
public void ImportExcelData(string excelFilePath)
{
// 读取Excel文件
using (var stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// 获取Excel文件中的所有表格
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
});
// 获取第一个表格中的数据,并插入到数据库中的实体表格中
var entityTable = result.Tables[0];
foreach (DataRow row in entityTable.Rows)
{
var entityValue1 = row["EntityValue1"].ToString();
var entityValue2 = row["EntityValue2"].ToString();
// 在数据库中插入实体数据,并获取实体ID
var entityId = InsertEntityData(entityValue1, entityValue2);
// 获取第二个表格中该实体对应的多实体数据,并插入到数据库中的多实体表格中
var multiEntityTable = result.Tables[1];
var entityMultiRows = multiEntityTable.AsEnumerable()
.Where(r => r.Field<string>("EntityValue1") == entityValue1 && r.Field<string>("EntityValue2") == entityValue2);
foreach (var multiEntityRow in entityMultiRows)
{
var multiEntityValue1 = multiEntityRow["MultiEntityValue1"].ToString();
var multiEntityValue2 = multiEntityRow["MultiEntityValue2"].ToString();
// 在数据库中插入多实体数据,并关联实体ID
InsertMultiEntityData(entityId, multiEntityValue1, multiEntityValue2);
}
}
}
}
}
public int InsertEntityData(string entityValue1, string entityValue2)
{
var connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;";
var query = "INSERT INTO EntityTable (EntityValue1, EntityValue2) VALUES (@EntityValue1, @EntityValue2); SELECT SCOPE_IDENTITY();";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@EntityValue1", entityValue1);
command.Parameters.AddWithValue("@EntityValue2", entityValue2);
// 执行命令,并获取实体ID
var entityId = Convert.ToInt32(command.ExecuteScalar());
return entityId;
}
}
}
public void InsertMultiEntityData(int entityId, string multiEntityValue1, string multiEntityValue2)
{
var connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;";
var query = "INSERT INTO MultiEntityTable (EntityId, MultiEntityValue1, MultiEntityValue2) VALUES (@EntityId, @MultiEntityValue1, @MultiEntityValue2);";
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@EntityId", entityId);
command.Parameters.AddWithValue("@MultiEntityValue1", multiEntityValue1);
command.Parameters.AddWithValue("@MultiEntityValue2", multiEntityValue2);
// 执行命令
command.ExecuteNonQuery();
}
}
}
```
以上代码示例中,`ImportExcelData`方法用于读取Excel文件并将数据插入到数据库中,`InsertEntityData`方法用于插入实体数据并获取实体ID,`InsertMultiEntityData`方法用于插入多实体数据并关联实体ID。你需要根据实际情况修改连接字符串、表格名称、列名称等参数。