In the project, customers often require that you use Excel to import data to the database to improve efficiency.
Note: The first row in Excel cannot be imported.
The following is the source code;
Excel. aspx:
<MCE: Script Type = "text/JavaScript" src = "http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src = "http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"> </MCE: SCRIPT> <MCE: script Type = "text/JavaScript" src = "http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src = "http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"> </MCE: script >>< % @ page Language = "C #" autoeventwireup = "true" codebehind = "intoexcel. aspx. CS "inherits =" demo. excel excel "%> <br/> <! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <br/> <HTML xmlns = "http://www.w3.org/1999/xhtml"> <br/> <pead runat = "server"> <br/> <title> No title page </title> <br/> <MCE: script Language = "JavaScript" type = "text/JavaScript"> <! -- <Br/> // <! CDATA [<br/> Function check () {<br/> var K = // s +/. [XLS]/; <br/> If (! K. test (document. getelementbyid ("fileid "). value) <br/>{< br/> alert ("files in XLS format last time only"); <br/> return false; <br/>}< br/> return true; <br/>}< br/> // --> </MCE: SCRIPT> <br/> </pead> <br/> <body> <br/> <Form ID = "form1" runat = "server"> <br/> <Div> <br/> <p> <br/> <asp: fileupload id = "fileid" runat = "server"/> <asp: button id = "button1" runat = "server" text = "Upload" onclientclick = "Return check () "onclick =" button#click "/> </P> <br/> </div> <br/> </form> <br/> </body> <br/> </ptml> <br/>
Excel. aspx. CS
Using system; <br/> using system. collections; <br/> using system. configuration; <br/> using system. data; <br/> using system. web; <br/> using system. web. security; <br/> using system. web. ui; <br/> using system. web. UI. htmlcontrols; <br/> using system. web. UI. webcontrols; <br/> using system. web. UI. webcontrols. webparts; <br/> using system. io; <br/> using system. data. oledb; <br/> using system. data. sqlclient; <br/> Nam Espace demo <br/>{< br/> Public partial class internal Excel: system. web. UI. page <br/>{< br/> protected void page_load (Object sender, eventargs E) <br/>{< br/>}< br/> /// <summary> <br/> // upload a file <br/> /// </Summary> <br/> // <Param name = "sender"> </param> <br/> // <Param name = "E"> </param> <br /> protected void button#click (Object sender, eventargs e) <br/>{< br/> string filename = fileid. filenam E; <br/> string savepath = server. mappath ("~ /File/"); <br/> fileoperatpr (filename, savepath); <br/> fileid. saveas (savepath + filename); <br/> dataoperator (filename, savepath ); <br/>}< br/> /// <summary> <br/> // Data Operations <br/> /// </Summary> <br/>/ // <Param name = "FILENAME"> </param> <br/> // <Param name = "savepath"> </param> <br/> private void dataoperator (string filename, string savepath) <br/>{< br/> string mystring = "provider = Microsoft. J ET. oledb.4.0; Data Source = "+ savepath + filename +"; extended properties = Excel 8.0 "; <br/> oledbconnection oconn = new oledbconnection (mystring); <br/> oconn. open (); <br/> dataset DS = new dataset (); <br/> oledbdataadapter ODA = new oledbdataadapter ("select * from [sheet1 $]", oconn ); <br/> ODA. fill (DS); <br/> oconn. close (); <br/> datasetoperator (DS, savepath + filename); <br/>}< br/> // <summar Y> <br/> // dataset operation <br/> /// </Summary> <br/> /// <Param name = "ds"> </Param> <br/> private void datasetoperator (Dataset ds, string filepath) <br/>{< br/> sqlconnection conn = new sqlconnection ("Data Source = .; initial catalog = testexcel; Integrated Security = true "); <br/> Conn. open (); <br/> sqltransaction STR = Conn. begintransaction (); // use transaction processing to prevent interruption <br/> int K = 0; <br/> If (Ds. tables [0]. rows. count <1) <Br/> {<br/> response. Write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('no data! ') <Br/> // --> </MCE: SCRIPT> "); <br/> return; <br/>}< br/> try <br/> {<br/> for (INT I = 0; I <Ds. tables [0]. rows. count; I ++) <br/>{< br/> string sqlstr = "insert into Excel (tname, Tage, taddress) values "; <br/> sqlstr + = "('" + Ds. tables [0]. rows [I] [0]. tostring () + "',"; <br/> sqlstr + = Ds. tables [0]. rows [I] [1]. tostring () + ","; <br/> sqlstr + = "'" + Ds. tables [0]. rows [I] [2]. tostring () + "')"; <br/> sqlcomm And cmd = new sqlcommand (sqlstr, Conn, STR); <br/> cmd. transaction = STR; <br/> K + = cmd. executenonquery (); <br/>}< br/> Str. commit (); <br/>}< br/> catch (exception ex) <br/>{< br/> response. write ("exception occurred, data has been rolled back/n information/N" + ex. message); <br/> Str. rollback (); <br/>}< br/> finally <br/> {<br/> response. write ("uploaded" + K + "); <br/> file. delete (filepath); <br/>}< br/> // <summary> <br/> /// File Operations <br/> /// </Summary> <br/> // <Param name = "FILENAME"> </param> <br/> // <param name = "savepath"> </param> <br/> private void fileoperatpr (string filename, string savepath) <br/>{< br/> If (! Directory. exists (savepath) <br/>{< br/> directory. createdirectory (savepath); <br/>}< br/> If (file. exists (savepath + filename) <br/>{< br/> file. delete (savepath + filename); <br/>}< br/>
Fields in the database:
Create Table foreign Excel <br/> (<br/> TID int identity () primary key, <br/> tname varchar (50), <br/> Tage int, <br/> taddress varchar (200), </P> <p>)
Excel Data
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
