protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "shifou", "script>alert('請您選擇 Excel文件')/script> ");
return;
}
string Name = FileUpload1.PostedFile.FileName; //獲取初始文件名
int j = Name.LastIndexOf("."); //取得文件名中最后一個"."的索引
string newext1 = Name.Substring(j); //獲取文件擴展名
if (newext1 != ".xls" newext1 != ".xlsx")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "script>alert('只可以選擇 Excel文件')/script>");
return;//當選擇的不是Excel文件時,返回
}
SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
cn.Open();
//savePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);// FileUpload1.PostedFile.FileName.ToString();
if (FileUpload1.HasFile == false)//HasFile用來檢查FileUpload是否有指定文件
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "script>alert('Excel路徑有 問題')/script>");
return;//當無文件時,返回
}
filename = FileUpload1.FileName; //Path.GetFileNameWithoutExtension (FileUpload1.PostedFile.FileName);
savePath = Server.MapPath(("../../UploadFiles/xls/") + filename);
FileUpload1.SaveAs(savePath); //保存XLS文件
DataSet ds = ExecleDs(savePath, filename); //調(diào)用自定義方法
DataRow[] dr = ds.Tables[0].Select(); //定義一個DataRow數(shù)組
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
//當Excel表為空時,對用戶進行提示
Page.ClientScript.RegisterStartupScript(this.GetType(), "kong", "script>alert('Excel表為空表, 無數(shù)據(jù)!')/script>");
}
else
{
for (int i = 0; i dr.Length; i++)
{
//自定義的model-student
student.belong = "";
student.birthday = Convert.ToDateTime("1990-1-1");
student.classid = 1;
student.id = 1;
student.inschool = Convert.ToDateTime("1990-1-1");
student.phone = "";
student.sex = "";
student.type = "";
student.isaudite = "否";
student.roleid = Convert.ToInt32("22");
try
{
student.stuname = dr[i]["姓名"].ToString();
student.stuid = dr[i]["學號"].ToString();
student.username = dr[i]["姓名"].ToString();
student.userpassword = dr[i]["學號"].ToString();
}
catch(Exception ex) {
Response.Write("script>alert('"+ex.Message+"')/script>");
return;
}
DataSet ds2 = studentbll.GetList("stuid='" + dr[i]["學號"].ToString() + "'");
if (ds2.Tables[0].Rows.Count == 0)
{
try
{
bool addstudent = Convert.ToBoolean(studentbll.Add(student));
if (addstudent)
Page.ClientScript.RegisterStartupScript(this.GetType(), "daochu", "script>alert('Excle表導入成功!')/script>");
}
catch (MembershipCreateUserException ex) //捕捉異常
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "script>alert('導入內(nèi)容:" + ex.Message + "')/script> ");
}
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "script>alert ('內(nèi)容重復！禁止導入')/script> ");
continue;
}
}
}
}
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";//這段字符串注意空格別寫錯否則會報錯
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet18$]", conn);//【sheet18$】是表格名
DataSet ds = new DataSet();
odda.Fill(ds, table);
return ds;
}
