XLS table Import Database Function Instance Code _ Practical Tips

Copy Code code as follows:

protected void Button1_Click (object sender, EventArgs e)
{

if (Fileupload1.hasfile = = False)
{
Page.ClientScript.RegisterStartupScript (this. GetType (), "Shifou", "<script>alert (' Please select Excel file ') </script>");
Return
}
string Name = FileUpload1.PostedFile.FileName; Get the initial file name
Int J = Name.lastindexof ("."); Gets the last "." in the filename. The index
String newext1 = Name.substring (j); Get file name extension
if (Newext1!= ". xls" && newext1!= ". xlsx")
{
Page.ClientScript.RegisterStartupScript (this. GetType (), "Wenjian", "<script>alert (' only Excel file selected ') </script>");
return;//when the Excel file is not selected, returns
}
SqlConnection cn = new SqlConnection (configurationmanager.appsettings["ConnectionString"));
cn. Open ();
Savepath = Path.GetFullPath (FileUpload1.PostedFile.FileName);//FileUpload1.PostedFile.FileName.ToString ();
if (Fileupload1.hasfile = False)//hasfile is used to check whether FileUpload has the specified file
{
Page.ClientScript.RegisterStartupScript (this. GetType (), "Wenjian", "<script>alert (' Excel path problematic ') </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); Calling the custom method
datarow[] Dr = ds. Tables[0].            Select (); Defines a DataRow array
int rowsnum = ds. Tables[0]. Rows.Count;
if (rowsnum = 0)
{
Prompt the user when an Excel table is empty
Page.ClientScript.RegisterStartupScript (this. GetType (), "Kong", "<script>alert (' Excel table is empty table, no data! ') </script> ");
}
Else
{
for (int i = 0; i < Dr. Length; i++)
{

Custom 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]["Name". ToString ();
Student.stuid = dr[i]["School Number"]. ToString ();
Student.username = dr[i]["Name". ToString ();
Student.userpassword = dr[i]["School Number"]. ToString ();
}
catch (Exception ex) {
Response.Write ("<script>alert" ("+ex"). Message+ "') </script>");
Return
}
DataSet ds2 = Studentbll. GetList ("stuid= '" + dr[i]["School Number"]. 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 import succeeded! ') </script> ");
}
catch (MembershipCreateUserException ex)//catch exception
{
Page.ClientScript.RegisterStartupScript (this. GetType (), "Chongfu", "<script>alert (' Import content:" + ex.) Message + "') </script>");
}
}
Else
{
Page.ClientScript.RegisterStartupScript (this. GetType (), "Chongfu", "<script>alert" (' Content repeat!) Prohibit 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; '; /This string note the blanks do not write wrong, or you will report an error
OleDbConnection conn = new OleDbConnection (strconn);
OleDbDataAdapter Odda = new OleDbDataAdapter ("SELECT * from [sheet18$]", conn);/"sheet18$" is the table name

DataSet ds = new DataSet ();
Odda. Fill (ds, table);
return DS;
}