Xls table import database function instance code

Copy codeThe Code is as follows:
Protected void button#click (object sender, EventArgs e)
{

If (FileUpload1.HasFile = false)
{
Page. ClientScript. RegisterStartupScript (this. GetType (), "shifou", "<script> alert ('select an Excel file') </script> ");
Return;
}
String Name = FileUpload1.PostedFile. FileName; // get the initial file Name
Int j = Name. LastIndexOf ("."); // retrieve the index of the last "." In the file Name
String newext1 = Name. Substring (j); // get the file extension
If (newext1! = ". Xls" & newext1! = ". Xlsx ")
{
Page. ClientScript. RegisterStartupScript (this. GetType (), "wenjian", "<script> alert ('select only Excel file') </script> ");
Return; // If the selected file is not an Excel file
}
SqlConnection cn = new SqlConnection (ConfigurationManager. etettings ["ConnectionString"]);
Cn. Open ();
// SavePath = Path. GetFullPath (FileUpload1.PostedFile. FileName); // FileUpload1.PostedFile. FileName. ToString ();
If (FileUpload1.HasFile = false) // HasFile is used to check whether a specified file exists in FileUpload.
{
Page. ClientScript. RegisterStartupScript (this. GetType (), "wenjian", "<script> alert ('excel path problematic ') </script> ");
Return; // if no file exists, return
}

Filename = FileUpload1.FileName; // Path. GetFileNameWithoutExtension (FileUpload1.PostedFile. FileName );
SavePath = Server. MapPath (".../../UploadFiles/xls/") + filename );
FileUpload1.SaveAs (savePath); // Save the XLS file

DataSet ds = ExecleDs (savePath, filename); // call a 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 the Excel table is empty
Page. ClientScript. RegisterStartupScript (this. GetType (), "kong", "<script> alert ('excel table is empty, 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] ["student ID"]. ToString ();
Student. username = dr [I] ["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) // catch an exception
{
Page. clientScript. registerStartupScript (this. getType (), "chongfu", "<script> alert ('imported content:" + ex. message + "') </script> ");
}
}
Else
{
Page. ClientScript. RegisterStartupScript (this. GetType (), "chongfu", "<script> alert ('content already exists! Import prohibited ') </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; '"; // do not write an error when writing this string with spaces. Otherwise, an error is reported.
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;
}