无法将excel工作表中的所有数据导入C#中的sql
本文关键字:数据 导入 sql 中的 excel 工作 | 更新日期: 2023-09-27 18:27:44
我有一个excel文件,里面有3个工作表。我想把它导入数据库。我只成功地将一个工作表导入到一个表中。这是我的代码:
string sqlConnectionString = @"Data Source=Magangpti1'SQLEXPRESS;Database=RcColdis;Trusted_Connection=true;Persist Security Info=True";
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=True";
OleDbConnection con = null;
DataTable dt = null;
con = new OleDbConnection(excelConnectionString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
con.Close();
string sheetname = dt.Rows[0]["Table_Name"].ToString();
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from [" + sheetname + "]", con);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
sqlBulk.DestinationTableName = "Absolute_IDR";
sqlBulk.WriteToServer(dReader);
con.Close();
string sheetname1 = dt.Rows[1]["Table_Name"].ToString();
con.Open();
OleDbCommand cmd1 = new OleDbCommand("Select * from [" + sheetname1 + "] ", con);
OleDbDataReader dReader1;
dReader1 = cmd1.ExecuteReader();
SqlBulkCopy sqlBulk1 = new SqlBulkCopy(sqlConnectionString);
sqlBulk1.DestinationTableName = "RC_Saham";
sqlBulk1.WriteToServer(dReader1);
con.Close();
string sheetname2 = dt.Rows[2]["Table_Name"].ToString();
con.Open();
OleDbCommand cmd2 = new OleDbCommand("Select * from [" + sheetname2 + "]", con);
OleDbDataReader dReader2;
dReader2 = cmd2.ExecuteReader();
SqlBulkCopy sqlBulk2 = new SqlBulkCopy(sqlConnectionString);
sqlBulk2.DestinationTableName = "RC_Member";
sqlBulk2.ColumnMappings.Add("Kode AK", "Kode AK");
sqlBulk2.ColumnMappings.Add("Risk Charge", "Risk Charge");
sqlBulk2.WriteToServer(dReader2);
con.Close();
return View("success");
}
else
{
return View("Index");
}
}
}
}
}
我无法将其余工作表导入数据库。你能帮我吗?
是否要将第二张表数据输入dReader,请尝试给出硬编码的表名`
OleDbCommand cmd=new OleDbCommand("从[sheetname1]中选择*",con);`