. NET import Excel to SQL database

In our development of various types of application systems, often encounter import and export Excel, why use him? Enterprise or unit in from the information-free to the informatization of a transformation process. In the absence of information-based enterprises or units, generally using Excel to record the corresponding data, do the role of statistical calculation, then when the enterprise or unit implementation of information will inevitably import the original data into the system to store and analyze. Then as a programmer will inevitably face a data import database functionality, the following for such a situation as outlined below.

In the B/s architecture of ASP, go to import Excel.

For example:

We need to import the employee information sheet into the database. The Excel format is as follows:

So after we understand this, the following is the occurrence of an import event triggered by a button in ASP.

protected voidBtnchange_click (Objectsender, EventArgs e) {Userinfoclass Tclass= (Userinfoclass) session["UserInfo"]; stringTlanguagetype =Tclass.language;//Get file path            stringFilePath = This. File1.            Postedfile.filename; if(FilePath! ="")            {                if(Filepath.contains ("xls"))//determine if a file exists{inputexcel (FilePath); }                Else{MessageBox.Show ("Please check if the file you selected is an Excel file! Thank you! "); }            }            Else{MessageBox.Show ("Please select the import file before performing the import! Thank you! "); }        }        Private voidInputexcel (stringPpath) {            stringconn ="Provider = microsoft.jet.oledb.4.0; Data Source ="+ Ppath +"; Extended properties= ' Excel 8.0; Hdr=false;imex=1 '"; OleDbConnection Olecon=NewOleDbConnection (conn);            Olecon.open (); stringSQL ="select * FROM [sheet1$]"; OleDbDataAdapter mycommand=NewOleDbDataAdapter (SQL, Olecon); DataSet DS=NewDataSet (); mycommand. Fill (DS,"[sheet1$]");            Olecon.close (); intCount = ds. tables["[sheet1$]"].            Rows.Count;  for(inti =0; I < count; i++)            {                stringTuserid, Tusername, Tdept, Temail, Tleader, tAngent; Tuserid= ds. tables["[sheet1$]"]. rows[i]["Employee's Representative No."]. ToString ().                Trim (); Tusername= ds. tables["[sheet1$]"]. rows[i]["Employee Name"]. ToString ().                Trim (); Tdept= ds. tables["[sheet1$]"]. rows[i]["part of the department"]. ToString ().                Trim (); Temail= ds. tables["[sheet1$]"]. rows[i]["e-Mail Address"]. ToString ().                Trim (); Tleader= ds. tables["[sheet1$]"]. rows[i]["Direct Supervisor"]. ToString ().                Trim (); TAngent= ds. tables["[sheet1$]"]. rows[i]["Agent"]. ToString ().                Trim (); stringExcelsql ="Insert into"+ This. Userinfo.company +".. [Resak] (resak001, resak002, resak015,resak005,resak013,resak009) VALUES ('"+ Tuserid +"', '"+ Tusername +"', '"+ Tdept +"', '"+ Temail +"', '"+ Tleader +"', '"+ TAngent +"')"; DBCommand cmd=Dscdbdata.getdatadbcommand (); Cmd.            Exenonquery (Excelsql); }        }

Of course, this part of the content to be slightly modified, such as the final execution of the INSERT statement part, and so on. The above completes the ability to import Excel into the database under ASP. Next, if you want to import Excel directly into the database in an SQL statement, implement the following:

SELECT * into Purtcfrom   OPENROWSET ('MICROSOFT. JET. oledb.4.0','Excel 5.0; Hdr=yes;database=d:\12.xls', sheet1$)

Note that the fields in Excel correspond to the fields in the table.

. NET import Excel to SQL database