程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> SqlServer2008 >> 如何把Excel數據導入到SQL2008數據庫的實例方法

如何把Excel數據導入到SQL2008數據庫的實例方法

編輯:SqlServer2008

復制代碼 代碼如下:
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("數據導入成功!");

                    }
                }  

           }  

       }

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved