private void AddManyData_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel文件|*.xls";
if ((bool)openFileDialog.ShowDialog())
{
FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
string filePath = fileInfo.FullName;
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
using (OleDbConnection oleDbConn = new OleDbConnection(connExcel))
{
oleDbConn.Open();
//獲取excel表
DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//獲取Excel表的表名
string tableName = dt.Rows[0][2].ToString().Trim();
//去掉空格
tableName = "[" + tableName.Replace("'", "") + "]";
//利用SQL語句從Excel文件里獲取數(shù)據(jù)
string query = @"SELECT 學(xué)號(hào),姓名,公益勞動(dòng),電子工藝實(shí)習(xí),操作系統(tǒng) ,計(jì)算機(jī)組成,數(shù)值分析,網(wǎng)絡(luò)設(shè)備與集成,動(dòng)態(tài)網(wǎng)站開發(fā)實(shí)驗(yàn)周,動(dòng)態(tài)網(wǎng)站開發(fā),均分,排名 FROM "; + tableName;
DataSet dataSet = new DataSet();
using (OleDbCommand oleDbcomm = oleDbConn.CreateCommand())
{
oleDbcomm.CommandText = query;
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleDbcomm);
oleAdapter.Fill(dataSet);
}
string connStr = "Data Source=HESHUHUA-PC;Initial Catalog=RSMSystem;Integrated Security=True";
//利用SqlBulkCopy批量插入數(shù)據(jù)
using (SqlBulkCopy sqlbc = new SqlBulkCopy(connStr))
{
sqlbc.DestinationTableName = "T_StuScore";
// sqlbc.ColumnMappings.Add("學(xué)號(hào)", "StuNum"),第一個(gè)參數(shù)對應(yīng)數(shù)據(jù)庫中的列名,
//第二個(gè)參數(shù)對應(yīng)數(shù)據(jù)庫中相應(yīng)表的列名
sqlbc.ColumnMappings.Add("學(xué)號(hào)", "StuNum");
sqlbc.ColumnMappings.Add("姓名", "StuName");
sqlbc.ColumnMappings.Add("公益勞動(dòng)", "Activity");
sqlbc.ColumnMappings.Add("電子工藝實(shí)習(xí)", "ElecAct");
sqlbc.ColumnMappings.Add("操作系統(tǒng)", "OprationSystem");
sqlbc.ColumnMappings.Add("計(jì)算機(jī)組成", "ComputerMaded");
sqlbc.ColumnMappings.Add("數(shù)值分析", "DataAnalyze");
sqlbc.ColumnMappings.Add("網(wǎng)絡(luò)設(shè)備與集成", "NetWork");
sqlbc.ColumnMappings.Add("動(dòng)態(tài)網(wǎng)站開發(fā)實(shí)驗(yàn)周", "WebWeek");
sqlbc.ColumnMappings.Add("動(dòng)態(tài)網(wǎng)站開發(fā)", "WebMake");
sqlbc.ColumnMappings.Add("均分", "AvScore");
sqlbc.ColumnMappings.Add("排名", "StuPaiMing");
sqlbc.WriteToServer(dataSet.Tables[0]);
MessageBox.Show("數(shù)據(jù)導(dǎo)入成功!");
}
}
}
}