|
代码如下: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文件里获取数据
string query = @"SELECT 学号,姓名,公益劳动,电子工艺实习,操作系统,计算机组成,数值分析,网络设备与集成,动态网站开发实验周,动态网站开发,均分,排名 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批量插入数据
using (SqlBulkCopy sqlbc = new SqlBulkCopy(connStr))
{
sqlbc.DestinationTableName = "T_StuScore";
// sqlbc.ColumnMappings.Add("学号","StuNum"),第一个参数对应数据库中的列名，
//第二个参数对应数据库中相应表的列名
sqlbc.ColumnMappings.Add("学号","StuNum");
sqlbc.ColumnMappings.Add("姓名","StuName");
sqlbc.ColumnMappings.Add("公益劳动","Activity");
sqlbc.ColumnMappings.Add("电子工艺实习","ElecAct");
sqlbc.ColumnMappings.Add("操作系统","OprationSystem");
sqlbc.ColumnMappings.Add("计算机组成","ComputerMaded");
sqlbc.ColumnMappings.Add("数值分析","DataAnalyze");
sqlbc.ColumnMappings.Add("网络设备与集成","NetWork");
sqlbc.ColumnMappings.Add("动态网站开发实验周","WebWeek");
sqlbc.ColumnMappings.Add("动态网站开发","WebMake");
sqlbc.ColumnMappings.Add("均分","AvScore");
sqlbc.ColumnMappings.Add("排名","StuPaiMing");
sqlbc.WriteToServer(dataSet.Tables[0]);
MessageBox.Show("数据导入成功！"); }
} } }
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
