protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "shifou", "<script>alert(' Please you to choose Excel file ')</script> ");
return;
}
string Name = FileUpload1.PostedFile.FileName; // Get the initial file name
int j = Name.LastIndexOf("."); // Gets the last in the file name 1 a "." The index of the
string newext1 = Name.Substring(j); // Gets the file extension
if (newext1 != ".xls" && newext1 != ".xlsx")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert(' You can only choose Excel file ')</script>");
return;// When the choice is not Excel A file is , return
}
SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
cn.Open();
//savePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);// FileUpload1.PostedFile.FileName.ToString();
if (FileUpload1.HasFile == false)//HasFile Used to check FileUpload Whether there is a specified file
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('Excel Path has The problem ')</script>");
return;// When there is no file , return
}
filename = FileUpload1.FileName; //Path.GetFileNameWithoutExtension (FileUpload1.PostedFile.FileName);
savePath = Server.MapPath(("../../UploadFiles/xls/") + filename);
FileUpload1.SaveAs(savePath); // save XLS file
DataSet ds = ExecleDs(savePath, filename); // Invoke the custom method
DataRow[] dr = ds.Tables[0].Select(); // define 1 a DataRow An array of
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
// when Excel When the table is empty , Prompt the user
Page.ClientScript.RegisterStartupScript(this.GetType(), "kong", "<script>alert('Excel The table is empty table , No data !')</script>");
}
else
{
for (int i = 0; i < dr.Length; i++)
{
// The custom of 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 = " no ";
student.roleid = Convert.ToInt32("22");
try
{
student.stuname = dr[i][" The name "].ToString();
student.stuid = dr[i][" Student id "].ToString();
student.username = dr[i][" The name "].ToString();
student.userpassword = dr[i][" Student id "].ToString();
}
catch(Exception ex) {
Response.Write("<script>alert('"+ex.Message+"')</script>");
return;
}
DataSet ds2 = studentbll.GetList("stuid='" + dr[i][" Student id "].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 Table imported successfully !')</script>");
}
catch (MembershipCreateUserException ex) // The catching
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert(' The import content :" + ex.Message + "')</script> ");
}
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert (' Repeat! Ban on the import ')</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;'";// So this is a string and make sure that you don't make a space error or you'll get an error
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet18$]", conn);// 【 sheet18$ "Is the name of the table
DataSet ds = new DataSet();
odda.Fill(ds, table);
return ds;
}