将Excel表导入Sql Server

通过OleDbDataAdapter将指定路径的.xls文件填充到DataSet中。

using  System.Data.OleDb;
using  System.Data;
...
... {
        protected DataSet ExcelToDS(string FilePath)
       ...{ 
               DataSet ds=new DataSet(); 
               new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;").Fill(ds,"Sheet1");
               return ds;
        } 
}

然后将DataSet按照格式写入数据库中：

 

using  System.Data;
using  System.Data.SqlClient;
    
....
... {
      .....
    protected void LoadButton_Click(object sender, EventArgs e) 
    ...{
        if (FileUpload1.PostedFile.FileName.ToString() != null 
            && FileUpload1.PostedFile.FileName.ToString() != "" 
            && FileUpload1.PostedFile.FileName.EndsWith(".xls")) //FileUpload1为页面中控件
        ...{             
            DataSet ds = null;         
            try 
            ...{ 
                string path = FileUpload1.FileName;
                string strFilePath = MapPath("excel") + "/" + path;
                FileUpload1.PostedFile.SaveAs(strFilePath);         
                ds = ExcelToDS(strFilePath); 

                for (int i = 0; i  < ds.Tables[0].Rows.Count-1; i++)  
                ...{ 
                    for (int j = 0; j  < 6; j++) 
                    ...{ 
                        string colvalue = ds.Tables[0].Rows[i].ItemArray[j].ToString();

                        if (j < 2 && colvalue == "")
                        ...{
                            Response.Write("<script > window.alert( '数据导入发生错误!') </script > ");
                        }
                    } 

                    //得到各列的值
                    string user_id = ds.Tables[0].Rows[i].ItemArray[0].ToString();
                    ......
                    string union_name = ds.Tables[0].Rows[i].ItemArray[10].ToString();
                   
                    SqlConnection MyConnection = new SqlConnection();
                    MyConnection.ConnectionString = MyConnectionString;//MyConnectionString 为你的数据库连接字符串
                    
                    SqlCommand MyCommand = new SqlCommand(); 
                    MyCommand.CommandText="INSERT INTO [XXX] ( [USER_ID],......,[STATUS] ) VALUES ( @user_id,...,@status)";
                    MyCommand.Connection = MyConnection;
                    MyCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
                    .......
                    MyCommand.Parameters.Add(new SqlParameter("@status",status));

                    MyConnection.Open();
                    MyCommand.ExecuteNonQuery();
                    MyConnection.Close();
                } 
            } 
            catch (SqlException exc) 
            ...{ 
                Response.Write("<script >window.alert('导入数据发生错误："+ exc.Message+"') </script > "); 
                return; 
            } 

            Response.Write("<script>window.alert('已成功导入数据！')</script>");  
        } 
        else Response.Write("<script>window.alert('请至少选择一个Excel文件导入！')</script>");
    }
     .....
}