c# excel导入mysql_C# 导入Excel到数据库

///

///导入Excel///

///

public voidImport(HttpPostedFileBase file)

{string msg = "";if (file == null)

{

msg= "导入失败";

}else{//1、先保存上传的excel文件(这一步与上传图片流程一致)

string extName =file.FileName;string path = Server.MapPath("~/Content/Files");string filename =Path.Combine(path, extName);

file.SaveAs(filename);//2.读取excel文件(通过oledb将excel数据填充到datatable)//HDR=Yes，代表第一行是标题，不做为数据使用,IMEX的含义(0：写入，1：读取，2：读取与写入)

string filePath = filename;//必须是物理路径

string conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source=" + filePath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";//3.默认读取的Sheet1

OleDbDataAdapter adp = new OleDbDataAdapter("select * From [Sheet0$]", conStr);

DataTable dt= newDataTable();

adp.Fill(dt);

List list = new List();if (dt.Rows.Count > 0)

{foreach (DataRow item indt.Rows)

{

list.Add(newEmployeesInfo()

{

ID= item["ID"].ToString(),

PersonnelNumber= item["PersonnelNumber"].ToString(),

ChineseName= item["ChineseName"].ToString(),

EMail= item["EMail"].ToString()

});

}

}//4.传值到数据库

EmployeesInfo model = newEmployeesInfo();for (int i = 0; i < list.Count; i++)

{

model.ID=list[i].ID;

model.PersonnelNumber=list[i].PersonnelNumber;

model.ChineseName= list[i].ChineseName;model.EMail = list[i].EMail;//调用添加方法

int result =Create(model);if (result > 0)

{

msg= "导入成功！";

}

}

}

Response.Write("");

}///

///导入Excel添加到数据库///

///

///

public intCreate(EmployeesInfo info)

{string sql = "insert into Employees(ID,PersonnelNumber, ChineseName,EMail) values(@ID, @PersonnelNumber, @ChineseName,@EMail)";

SqlParameter[] part={new SqlParameter("ID",info.ID),new SqlParameter("PersonnelNumber",info.PersonnelNumber),new SqlParameter("ChineseName",info.ChineseName),new SqlParameter("EMail",info.EMail)

};int result =db.ExecuteNonQuery(sql, part);returnresult;

}