Excel数据导入sql数据库(一)

最新推荐文章于 2024-05-26 19:32:09 发布

原创最新推荐文章于 2024-05-26 19:32:09 发布 · 668 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel #数据库 #sql #sql server #string #properties

本文提供了一个使用C#将Excel文件中的数据导入到SQL Server数据库的例子。通过OpenDataSource功能读取Excel文件，并利用SqlCommand对象执行SQL语句完成数据导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有多种方式这里介绍第一种:  这里是完整的例子、很简单就不仔细写了

protected void BuploadTeacher() {       
  //OpenFileDialog openFileDialog = new OpenFileDialog();       
  // openFileDialog.Filter = "Excel files(*.xls)|*.xls";         
   SqlConnection sqlConnection1 = null;        
 string filePath = this.FImportStudent.FileName;        
 sqlConnection1 = new SqlConnection();        
 sqlConnection1.ConnectionString=ConfigurationManager.ConnectionStrings["ThesisConnectionString"].ConnectionString;  
 //export SQL Server 2000 into excel         
/*string exportSQL = @"EXEC master..xp_cmdshell 'bcp Library.dbo.live41 out " + filePath + "-c -q -S" + "/"" + "/"" +             " -U" + "/"" + "/"" + " -P" + "/"" + "/"" + "/'";*/         
 string importSQL = "SELECT * into test1 FROM OpenDataSource" +         "('Microsoft.Jet.OLEDB.4.0','Data Source=" + "/"" + "E://a.xls" + "/"" +         "; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";        
 try {            
   sqlConnection1.Open();                    
    SqlCommand sqlCommand2 = new SqlCommand();            
    sqlCommand2.Connection = sqlConnection1;             
    sqlCommand2.CommandText = importSQL;             
    sqlCommand2.ExecuteNonQuery();            
     
  }        
 catch (Exception ex)        
 {                 
 }               
 if (sqlConnection1 != null)       
  {           
  sqlConnection1.Close();           
  sqlConnection1 = null;      
   }