Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how to write xls table import database function code". In daily operation, I believe that many people have doubts about how to write xls table import database function code. Xiaobian consulted all kinds of information and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to write xls table import database function code". Next, please follow the editor to study!

The copy code is as follows:

Protected void Button1_Click (object sender, EventArgs e)

{

If (FileUpload1.HasFile = = false)

{

Page.ClientScript.RegisterStartupScript (this.GetType (), "shifou", "alert ('Please select an Excel file')")

Return

}

String Name = FileUpload1.PostedFile.FileName; / / get the initial file name

Int j = Name.LastIndexOf ("."); / / get the last "." in the file name. Index of

String newext1 = Name.Substring (j); / / get the file extension

If (newext1! = ".xls" & & newext1! = ".xlsx")

{

Page.ClientScript.RegisterStartupScript (this.GetType (), "wenjian", "alert ('only Excel files can be selected')")

Return;// returns when an Excel file is not selected

}

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 a specified file

{

Page.ClientScript.RegisterStartupScript (this.GetType (), "wenjian", "alert ('Excel path problem')")

Return;// returns when there is no file

}

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 custom method

DataRow [] dr = ds.Tables [0] .Select (); / / define an 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", "alert ('Excel table is an empty table, numerous data!')

}

Else

{

For (int I = 0; I < dr.Length; iTunes +)

{

/ / 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 number"] .ToString ()

Student.username = dr [I] ["name"] .ToString ()

Student.userpassword = dr [I] ["student number"] .ToString ()

}

Catch (Exception ex) {

Response.Write ("alert ('" + ex.Message+ ")")

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", "alert ('ExcleTable imported successfully!')")

}

Catch (MembershipCreateUserException ex) / / catch exception

{

Page.ClientScript.RegisterStartupScript (this.GetType (), "chongfu", "alert ('import content:" + ex.Message + "')")

}

}

Else

{

Page.ClientScript.RegisterStartupScript (this.GetType (), "chongfu", "alert ('duplicate content! prohibit import')")

Continue

}

}

}

}

Public DataSet ExecleDs (string filenameurl, string table)

{

String strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filenameurl + "; Extended Properties='Excel 8.0; Extended Properties='Excel 8.0;"; / / this string should not be misspelled or it 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

}

At this point, the study on "how to write xls tables to import database function code" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.