ASP.NET Excel数据导入SQL Server

ASP.NET Excel数据导入SQL Server的示例:

string FileName = file_Customer.PostedFile.FileName.Trim();  //获取带路径的文件名

 string excelConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;"

+ "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0";  //连接语句

System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection(excelConnectionStr);//创建连接

 oleConn.Open();  //打开连接

System.Data.OleDb.OleDbCommand oleCmd = new System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", oleConn);

System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();

oda.SelectCommand = oleCmd;

DataSet dsExcel = new DataSet();

oda.Fill(dsExcel, "Data");  //填充数据

oleConn.Close();  //关闭连接

int Count = 0;

try

{

     foreach (DataRow dr in dsExcel.Tables[0].Rows)

     {

         Count++;

         ModelCustomer model = new ModelCustomer();

        Customer cs = new Customer();

         model.CustomerName = dr[0].ToString().Trim();

         model.CompanyType = dr[1].ToString().Trim();

         model.LinkMan = dr[2].ToString().Trim();

         model.HomeTelephone = dr[3].ToString().Trim();

         model.OfficeTelephone = dr[4].ToString().Trim();  

         model.MobileTelephone = dr[5].ToString().Trim(); 

         model.WebSite = dr[6].ToString().Trim();

         model.Email = dr[7].ToString().Trim();

         model.Fax = dr[8].ToString().Trim();

         model.Address = dr[9].ToString().Trim();

         cs.Add(model);     //Customer类的对象调用Add(ModelCustomer  model)方法将数据添加到数据库

     }

     Response.Write("<script>alert('添加成功')</script>");

}

catch

{

    Response.Write("<script>alert('第" + Count.ToString() + "条数据出错')</script>");

}

注意：客户端需要拥有Excel的访问权限，在Excel文件的属性-安全选项卡里面进行设置