How to write the function Code of importing xls Table into Database
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!
