Note: the first row in EXCEL cannot be imported.
The following is the source code: IntoExcel. aspx:
The code is as follows: |
Copy code |
<% @ Page AutoEventWireup = "true" CodeFile = "ExCel. aspx. cs" Inherits = "study_IntoExcel" %> <! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <Html xmlns = "http://www.w3.org/1999/xhtml"> <Head id = "Head1" runat = "server"> <Title> No title page </title> <Script language = "javascript" type = "text/javascript"> <! -- // <! CDATA [ Function check (){ Var k = // S +/. [xls]/; If (! K. test (document. getElementById ("fileId"). value )) { Alert ("only files in xls format last time "); Return false; } Return true; } // --> </Script> </Head> <Body> <Form id = "form1" runat = "server"> <Div> <P> <Asp: FileUpload ID = "fileId" runat = "server"/> <Asp: Button ID = "Button1" runat = "server" Text = "upload" OnClientClick = "return check ()" onclick = "button#click"/> </p> </Div> </Form> </Body> </Html> |
Excel. aspx. cs
The code is as follows: |
Copy code |
Using System; Using System. Collections. Generic; Using System. Linq; Using System. Web; Using System. Web. UI; Using System. Collections; Using System. Configuration; Using System. Data; Using System. Web. Security; Using System. Web. UI. HtmlControls; Using System. Web. UI. WebControls. WebParts; Using System. IO; Using System. Data. OleDb; Using System. Data. SqlClient; Using System. Web. UI. WebControls; Public partial class study_IntoExcel: System. Web. UI. Page { Protected void Page_Load (object sender, EventArgs e) { } /// <Summary> /// Upload a file /// </Summary> /// <Param name = "sender"> </param> /// <Param name = "e"> </param> Protected void button#click (object sender, EventArgs e) { String fileName = fileId. FileName; String savePath = Server. MapPath ("~ /File /"); FileOperatpr (fileName, savePath ); FileId. SaveAs (savePath + fileName ); DataOperator (fileName, savePath ); } /// <Summary> /// Data operations /// </Summary> /// <Param name = "fileName"> </param> /// <Param name = "savePath"> </param> Private void DataOperator (string fileName, string savePath) { String myString = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + savePath + fileName + "; Extended Properties = Excel 8.0 "; OleDbConnection oconn = new OleDbConnection (myString ); Oconn. Open (); DataSet ds = new DataSet (); OleDbDataAdapter oda = new OleDbDataAdapter ("select * from [Sheet1 $]", oconn ); Oda. Fill (ds ); Oconn. Close (); DataSetOperator (ds, savePath + fileName ); } /// <Summary> /// Dataset operation /// </Summary> /// <Param name = "ds"> </param> Private void DataSetOperator (DataSet ds, string filePath) { SqlConnection conn = new SqlConnection ("Data Source = SONYSVR; Initial Catalog = IAR_Factory_811; User ID = sa; Password = P @ ssword "); Conn. Open (); SqlTransaction str = conn. BeginTransaction (); // use transaction processing to prevent interruption Int k = 0; If (ds. Tables [0]. Rows. Count <1) { Response. Write ("<script> alert ('no data! ') </Script> "); Return; } Try { For (int I = 0; I <ds. Tables [0]. Rows. Count; I ++) { String <strong> <a href = "http://www.111cn.net" title = "SQL" target = "_ blank"> SQL </a> </strong> Str = "insert into Excel (Tname, tage, Taddress) values "; SqlStr + = "('" + ds. Tables [0]. Rows [I] [0]. ToString () + "',"; SqlStr + = ds. Tables [0]. Rows [I] [1]. ToString () + ","; SqlStr + = "'" + ds. Tables [0]. Rows [I] [2]. ToString () + "')"; SqlCommand cmd = new SqlCommand (sqlStr, conn, str ); Cmd. Transaction = str; K + = cmd. ExecuteNonQuery (); } Str. Commit (); } Catch (Exception ex) { Response. Write ("exception occurred, data has been rolled back/n information/n" + ex. Message ); Str. Rollback (); } Finally { Response. Write ("uploaded successfully" + k + "items "); File. Delete (filePath ); } } /// <Summary> /// File operations /// </Summary> /// <Param name = "fileName"> </param> /// <Param name = "savePath"> </param> Private void FileOperatpr (string fileName, string savePath) { If (! Directory. Exists (savePath )) { Directory. CreateDirectory (savePath ); } If (File. Exists (savePath + fileName )) { File. Delete (savePath + fileName ); } } } Provider = Microsoft. Jet. OLEDB.4.0; Data Source = "+ savePath +"; Extended Properties = 'Excel 8.0; HDR = YES Provider = Microsoft. Jet. OLEDB.4.0; // connection driver Data Source = "+ savePath +"; // database address Extended Properties = 'Excel 8.0; // The connection is Excel8.0
|
HDR = YES; // There are two values: YES/NO. The two values indicate whether you can directly read the column name. NO, you can only read the subscript.
IMEX = 1; // solves the problem of abnormal identification when numbers and characters are mixed.
This is not the best way to read data into the database. The office component should be used.
Select * from [Sheet1 $] // reference the content of the sheet 1 worksheet in the EXCLE file
The OleDB control uses the OleDb driver to access various databases.
Fields in the database:
The code is as follows: |
Copy code |
Create table external Excel ( Tid int identity (1, 1) primary key, Tname varchar (50 ), Tage int, Taddress varchar (200 ), ) |
The SQL control uses a dedicated driver to efficiently access the SQL Server database.
SQLConnection can only access SQL Server, while OleDbConnection can access all databases.
If you only access SQL Server, SQL is faster than OleDb