To import Excel data into SQL Server with code

This is illustrated here directly with a small example.

1. Open the new Web site, vs2010-> file, select the ASP. NET site and set the storage path to create an empty Web site. (My path here is set to D:\EXCELEDUCETOSQL)

2. Create the database test and create the My_test table in the test database (here I create the SQL folder in the Exceleducetosql folder and save the database in it).

(The table has the following fields:)

TID (int)

tname (nvarchar (50))

tt (nvarchar (50))

3. Create Excel table (the file name of my Excel table here is Test.xls). Note that the first line in Excel is defined as the column name, which is the data from line 2nd, and the data that is read from Excel through the SQL statement starts at the second line.

(Excel table is designed as follows:)

Number name remarks

1 a AA

2 b bb

3 C cc

4, on the basis of the 1th step, right-click Add New Item, select Web Form and name (I'm here by default name Default.aspx). In the design page, double-click or pull the button control in the left-hand toolbox and modify the Text property in the right property to import, and double-click the Import button to enter the code for the Default.aspx.cs file.

5, the code is written as follows:

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Web;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Data.SqlClient;

public partial class Test:System.Web.UI.Page
{
protected void Page_Load (object sender, EventArgs e)
{

}
Public SqlConnection con ()
{
return new SqlConnection ("server=.; Uid=sa; Pwd=sa;database=test "); It is important to note that the server, UID, PWD, database are your databases, respectively.
}
protected void Button1_Click (object sender, EventArgs e)
{
SqlConnection mycon = con ();
String sqlstr= "INSERT into my_test select * from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 8.0; Hdr=yes;database=d:\\exceleducetosql\\excel\\test.xls ', sheet1$) "; It is important to note that the path of the Excel file needs to be separated by a double slash, otherwise an error will occur.
SqlCommand cmd = new SqlCommand (sqlstr, mycon);
Mycon. Open ();
Cmd. ExecuteNonQuery ();
Mycon. Close ();
}
}

6. Click New query in SQL Server, enter the following code and click Execute:

exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
Go

7, ctrl+f5 operation can be. It is important to note that the operation is not open Excel file, otherwise it will be an error.

　　If there is an error please point out or contact me directly qq:953276191

To import Excel data into SQL Server with code